views:

33

answers:

1

Hi everyone,

I have a pretty big sql statement which returns a list of id's. I need this id-list as base for other statements. Like this:

open crs_result1 for ' select * from ... where id in ( select <ids> from <base_statement> ) ';
open crs_result2 for ' select * from ... where id in ( select <ids> from <base_statement> ) ';
open crs_result3 for ' select * from ... where id in ( select <ids> from <base_statement> ) ';
...

Of course I don't want to select the whole id-list each time for the different selects.

So, my idea was to use a table/array:

TYPE gt_result_rec IS RECORD
(
    id NUMBER
);
TYPE gt_result_tab IS TABLE OF gt_result_rec INDEX BY BINARY_INTEGER;

t_results gt_result_tab;

execute immediate 'insert into t_results select <ids> from <base_statement>';

And than use it for all other statements:

open crs_result1 for ' select * from ... where id in ( select id from t_results ) ';
...

But this doesn't really works.

Does anyone know the problem OR has a better solution for this?

Thx 4 answers

+1  A: 

A similar thing can be achieved using TEMPORARY tables, like this:

create global temporary table temp_ids(id number) on commit preserve rows ;

...than insert data:

execute immediate 'insert into temp_ids(id) select id from <big statement>';
execute immediate 'insert into temp_ids(id) select id from <other big statement>';
execute immediate 'insert into temp_ids(id) select id from <other big statement>';

..finally you can use your idea:

open crs_result1 for ' select * from ... where id in ( select id from temp_ids ) ';

Using TEPORARY TABLES reach that EXECUTE IMMEDIATE will take data from the same context in which your other PL/SQL code run.

If you want to use TABLE OF RECORDS (table/array), you will need declare this ARRAY in the PACKAGE (header, not BODY!), so the field will be visible from the context of the EXECUTE IMMEDIATE. ARRAY must be PUBLIC visible.

Martin Mares