PL/SQL - caching two resultsets into collections and joining them together?

Question

I have two very large tables and I need to process a small resultset from these tables. However, processing is done in several functions each and function must do some joining in order to format the data in proper way.

I would definitely need to cache the initial resultset somehow so it can be reused by the functions. What I would like to do is put the first result set in one collection, the second resultset in another collection, and then manipulate these collections through SQL queries as if they were real SQL tables.

Can you suggest how this can be done?

Answer 1

Sounds like a job for temp tables:

CREATE GLOBAL TEMPORARY TABLE table_name (...) ON ...

The ON has two options, with different impacts:

1. ON COMMIT DELETE ROWS specifies temporary table would be transaction specific. Data persist within table up to transaction ending time. If you end the transaction the database truncates the table (delete all rows). Suppose if you issue commit or run ddl then data inside the temporary table will be lost. It is by default option.

2. ON COMMIT PRESERVE ROWS specifies temporary table would be session specific. Data persist within table up to session ending time. If you end the session the database truncates the table (delete all rows). Suppose you type exit in SQL*Plus then data inside the temporary table will be lost.

Reference:

• ASKTOM
• Create Temporary Table in Oracle

...but it is possible that you don't need to use temporary tables. Derived tables/inline views/subqueries (maybe pipelining) can possibly do what you want, but the info is vague so I can't recommend a particular approach.

Answer 2

You need to create an schema-level type(not inside a package) as a nested table. You can populate them, and then you may use them in you queries as normal tables using the "table()" statement.

This link explains what you need. A quick example

create type foo as table of number;-- or a record type, data%rowtype, whatever
...
myfoo1 foo := foo (1,2,3);
myfoo2 foo := foo(3,4,5)

select column_value
into bar
from table(foo1) join table(foo2) using (column_value)

Answer 3

If your collections are declared with a SQL type you can use them in SQL statements with a TABLE() function. In Oracle 10g we can merge collections using the MULTISET UNION operator. The following code shows examples of both techniques...

SQL> declare
  2      v1 sys.dbms_debug_vc2coll;
  3      v2 sys.dbms_debug_vc2coll;
  4      v3 sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll();
  5  begin
  6      select ename
  7      bulk collect into v1
  8      from emp;
  9      select dname
 10      bulk collect into v2
 11      from dept;
 12
 13      -- manipulate connects using SQL
 14
 15      for r in ( select * from table(v1)
 16                 intersect
 17                 select * from table(v2)
 18               )
 19      loop
 20          dbms_output.put_line('Employee '|| r.column_value ||' has same name as a department');
 21      end loop;
 22
 23      -- combine two collections into one
 24
 25      dbms_output.put_line('V3 has '|| v3.count() ||' elements');
 26      v3 := v1 multiset union v2;
 27      dbms_output.put_line('V3 now has '|| v3.count() ||' elements');
 28  end;
 29  /
Employee SALES has same name as a department
V3 has 0 elements
V3 now has 23 elements

PL/SQL procedure successfully completed.

SQL>

There are a number of other approaches which you can employ. As a rule it is better to use SQL rather than PL/SQL, so OMG Ponies suggestion of temporary tables might be appropriate. It really depends on the precise details of your processing needs.