Database Reference
In-Depth Information
from our PL/SQL process. The PL/SQL routine that used to procedurally process the data becomes a
table
, in effect,
and the rows we fetch and process are the outputs. We've seen this many times throughout this topic every time we've
issued the following:
Select * from table(dbms_xplan.display);
That is a PL/SQL routine that reads the
PLAN_TABLE
; restructures the output, even to the extent of adding rows;
and then outputs this data using
PIPE ROW
to send it back to the client. We're going to do the same thing here, in effect,
but we'll allow for it to be processed in parallel.
We're going to use two tables in this example:
T1
and
T2
.
T1
is the table we were reading previously in the
select * from some_table
line of the
PROCESS_DATA
procedure;
T2
is the table we need to move this information
into. Assume this is some sort of ETL process we run to take the transactional data from the day and convert it into
reporting information for tomorrow. The two tables we'll use are as follows:
EODA@ORA12CR1> create table t1
2 as
3 select object_id id, object_name text
4 from all_objects;
Table created.
EODA@ORA12CR1> begin
2 dbms_stats.set_table_stats
3 ( user, 'T1', numrows=>10000000,numblks=>100000 );
4 end;
5 /
PL/SQL procedure successfully completed.
EODA@ORA12CR1> create table t2
2 as
3 select t1.*, 0 session_id
4 from t1
5 where 1=0;
Table created.
We used
DBMS_STATS
to trick the optimizer into thinking that there are 10,000,000 rows in that input table and that
it consumes 100,000 database blocks. We want to simulate a big table here. The second table,
T2
, is a copy of the first
table's structure with the addition of a
SESSION_ID
column. That column will be useful to actually see the parallelism
that takes place.
Next, we need to set up object types for our pipelined function to return. The object type is a structural definition
of the output of the procedure we are converting. In this case, it looks just like
T2
:
EODA@ORA12CR1> CREATE OR REPLACE TYPE t2_type
2 AS OBJECT (
3 id number,
4 text varchar2(30),
5 session_id number
6 )
7 /
Type created.
