CODEWITHSUNDEEP
oracle-databaseplsqloracle11goracle10g
N.G
N.G

Reputation: 134

An INTO clause is expected in this SELECT statement

any help for this please ? I want to return all those who live in france, then compare them with those who live in BELguim if they have the same name or name_done show the result

declare
  cursor c is select namer from poeple where city = 'France';
  c_res c%rowtype;
begin
  open c;
  loop
    fetch c into c_res;
    exit when c%notfound;
    select * from poeple tw where city = 'BELguim' AND (c_res.namer = tw.namer OR c_res.namer = tw.namer || ' _done' );
  end loop;
  close c;
end;

Upvotes: 0

Views: 1347

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

The issue is with :

select * from poeple tw where city='BELguim' AND (c_res.namer =tw.namer OR c_res.namer =tw.namer || ' _done' );

You cannot write the SQL directly in PL/SQL. The PL/SQL engine expects an INTO clause.

Looking at your code, you don't need the cursor c. it could be written in a single SQL. Which brings it back to Gordon's query :

select pb.*
from poeple pf join
     poeple pb
     on pf.city = 'France' and pb.city = 'BELgium' and
        (pf.namer = pb.namer or pf.namer = pb.namer || ' _done');

If you really need PL/SQL for some stuff which you have not explained, for example return the result set as REF CURSOR, then have an OUT parameter as REF CURSOR. But, you need to explain the requirement.

If you can do something in SQL, then don't use PL/SQL.

EDIT If your final goal is to update, then use the above query as USING clause in the MERGE statement.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Why use a cursor? You can write this with a single query:

select pb.*
from poeple pf join
     poeple pb
     on pf.city = 'France' and pb.city = 'BELgium' and
        (pf.namer = pb.namer or pf.namer = pb.namer || ' _done');

The issue with your code is that PL/SQL does not allow queries to return anything from code blocks. You can print out the results, insert values into variables, or put them in another table. But, you cannot just have the results from the query.

Upvotes: 1

Related Questions