I've two large tables and I have to process a little resultset from all of these tables. However, processing is completed in several functions each and performance should do some joining to be able to format the information in most convenient way.

I'd certainly have to cache the first resultset in some way so it may be used again through the functions. What I must do is defined the very first result occur one collection, the 2nd resultset in another collection, after which manipulate these collections through SQL queries as though these were real SQL tables.

Are you able to suggest how you can do this?

Seems like employment for temp tables:


The ON has two options, with various impacts:

  1. ON COMMIT DELETE ROWS identifies temporary table could be transaction specific. Data persist within table as much as transaction ending time. Should you finish the transaction the database truncates the table (remove all rows). Suppose should you problem commit or run ddl then data within the temporary table is going to be lost. It's automatically option.

  2. ON COMMIT PRESERVE ROWS identifies temporary table could be session specific. Data persist within table as much as session ending time. Should you finish the session the database truncates the table (remove all rows). Suppose you type exit in SQL*Plus then data within the temporary table is going to be lost.


...but it's entirely possible that you don't have to use temporary tables. Derived tables/inline sights/subqueries (maybe pipelining) may possibly do what you would like, however the info is vague and so i can't recommend a specific approach.

In case your collections are declared having a SQL type they are utilized in SQL claims having a TABLE() function. In Oracle 10g we are able to merge collections while using MULTISET UNION operator. The next code shows good 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;
 13      -- manipulate connects using SQL
 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;
 23      -- combine two collections into one
 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.


You will find many other approaches which you'll employ. Usually it is best to make use of SQL instead of PL/SQL, so OMG Ponies suggestion of temporary tables may be appropriate. It truly is dependent around the precise particulars of the processing needs.

You have to create an schema-level type(not in the package) like a nested table. You are able to populate them, and you could use them in your soul queries normally tables while using "table()" statement.

This link describes the thing you need. A fast 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)