In my PL/SQL block, I have these qu 2

[ganeshmb]

In my PL/SQL block, I have these queries.

Query I

select emp_id from employee where emp_type = 'MANAGER';


Query II

select * from products where prod_owner in (list selected in previous query)


Query I retrieves a list of values,which will be used in the IN clause of Query II.

My exact problem is this. I am not able to find a place holder for the list retrieved in Query I, which I would like to use in Query II. Please note that I have grossly simplified the queries in order to explain it here. So, please don't suggest that I can achieve this by having query I as a sub query inside query II. That is simply ruled out in the real case scenario.

I tried using Varray. But, I faced these problems.
a) The first query needs to be declared as a cursor and I have to loop through the cursor to populate the array.
b) There is no way I can execute the second query using the varray. Please note that I don't want to build the queries in form of strings.

Please let me know if you have a solution for this.

 

[lewisp]

Is there a reason you cant use

SELECT *
from products
where prod_owner in (
SELECT emp_id
from employee
where emp_type = 'MANAGER');

 

[Guest_imported]

Hi,
what about creating a temporary table containing your list
from query I, e.g. :
1) create table my_temp_table as select emp_id ...
2) ... in (select emp_id from my_temp_table);
3) drop table my_temp_table;
regards

 

[jee]

If you are going to use a temporary table, why not use a truly temporary table?

Create global temporary table ...


I am not up on the details, but check the Oracle 8i Concepts manual and the 8i Sql Reference Manual. I think using a true temporary table would provide better performance.

 

[ganeshmb]

lewisp, josef_b, jee
Thank you very much.

josef_b,
I was looking for a solution along these lines. But, I am not very comfortable with temporary tables. I agree that this will do the job for me, but not in a neat way. Temporary it may seem, But, why would I create a table in the place of a simple place holder in PL/SQL?

There doesn't seem to be a place holder object, which will act as an collection and can be integrated with the query seamlessly as the normal VARCHAR2 variables in a query do.

Look at this PL/SQL block.
temp_emp_id number(6);

temp_emp_id := 6
select * from employee where emp_id = temp_emp_id

Look how the PL/SQL variable fits into the query easily. Now, compare it with this query.

select * from employee where emp_id in (list of values)

Don't you think there should be a collection variable(emp_list), which I can fit into this query just like the previous one. Varray is a collection object, but it doesn't s
select * from employee where emp_id in emp_list
Varray seems to a candidate here, but not really. It simply cannot be used this way.

If this is not possible, it should provide at least a collection object, on which I should be able to perform a select query.
select * from employee where emp_id in (select * from emp_list)
TABLE object immediately comes to our mind here. But, Oracle documentation clearly says that you cannot perform a SELECT on this object. Why do they call it a "TABLE" then?

I am still left wondering as to how can I achieve this. Anyway, thank you very much guys. I appreciate your help.

 

[JamesGordon]

Try something like:

declare
cursor c1 is
select col1, col2 from products where prod_owner in (
select emp_id from employee where emp_type = 'MANAGER');

begin
for cd in c1 loop
if(cd.col1 > cd.col2) then
dbms_output.put_line('something.');
end if;
end loop;
end;
/

 

[zephan]

Thank you for answering yourself (and mine at the same time). Your right, I was expecting from pl/sql tables to be as analog to tables as *selectable* tables; now I know that it's impossible.

In facts the question could be "how to use a collection in a cursor ?".

Sorry for not helping so much.

 

[sem]

I suppose you do not want to use subquery because of an unapropriate execution plan, so maybe the problem is in tuning your query? Gather statistics ot write hints. Though if you need "intermediate" result, you may store it in a collection variable and base your inner subquery on it (using TABLE(CAST)).

 

[zephan]

Sem, I think that's precisely what do need ganeshmb (and I).
Can you give a sample using TABLE(CAST) or at least its syntax. But keep in mind that we want its usage in a cursor query.

Thanks

 

[sem]

create type emp_type as object (empno integer, ename varchar2(30));

create type emps_type as table of emp_type;

create table emps_table (empno integer, ename varchar2(30));

insert into emps_table values(1, 'John');

insert into emps_table values(2, 'Paul');

insert into emps_table values(3, 'George');

commit;

declare

a emps_type := emps_type(emp_type(1, 'John'));
cursor c is
select * from emps_table where empno in
(select empno from table(cast(a as emps_type)));

begin

for f in c loop
dbms_output.put_line(f.ename);
end loop;

end;

 

[srishan]

>> I am not able to find a place holder

Your placeholder could be another CURSOR itself.
Solution would have nested CURSORS. Something you can look at as an alternative to what others have suggested.

CURSOR c1 is 
select emp_id from employee where emp_type = 'MANAGER';

CURSOR c2(p_emp_id NUMBER) is -- Parameter cursor
select * from products where prod_owner = p_emp_id;

Begin
  FOR rec1 in c1 LOOP
      FOR rec2 in c2(rec1.emp_id) LOOP
         -- your logic
      END LOOP;
  END LOOP;
End;

The resultset in the INNER loop would only get the matching records for each of the emp_id from the OUTER loop. In other words the OUTER loop serves as the placeholder for emp_id values for your INNER loop/resultset, which is exactly what you want.

Thx,
SriDHAR