My query takes a parameter and needs to be executed for all values coming from a table. How do I call my query? Please help.
Query to execute:
select &&pnbr, pname from PERSON where pno=&&pnbr;
Query to give all values for parameter:
select distinct personnbr from CUSTOMER
Code you posted suggests that you use SQL*Plus (because of the substitution variable).
However, do you really need that parameter? Because, the 1st query has to return some rows for values returned by the 2nd query. So - why don't take a different approach? Here are a few of them.
select distinct p.pnbr, p.pname
from person p join customer c on c.personnbr = p.pnbr
or
select p.pnbr, p.pname
from person p
where p.pnbr in (select c.personnbr
from customer c)
or anonymous PL/SQL block
declare
l_pname person.pname%type;
begin
for cur_r in (select distinct c.personnbr from customer c) loop
select p.pname
into l_pname
from person p
where p.pnbr = cur_r.personnbr;
dbms_output.put_line(cur_r.personnbr ||' - '|| l_pname);
end loop;
end;
/
or function that returns refcursor
create or replace function f_person return sys_refcursor is
l_rc sys_refcursor;
begin
open l_rc for select p.pnbr, p.pname
from person p
where p.pnbr in (select c.personnbr
from customer c);
return l_rc;
end;
/
select f_person from dual;
I don't think that you can do what you asked for in pure SQL (meaning: let the 2nd query return some values, and - somehow - use them automatically as parameters in the 1st query).
