Data loading script

 

Cursor for loading data on W_ASSESS_F_PART table 

******************************************************

alter session force parallel DDL parallel 16 ;

alter session force parallel DML parallel 16 ;

alter session set db_file_multiblock_read_count=256;

CREATE OR REPLACE PROCEDURE Load_W_ASSESS_F_PART (p_array_size IN PLS_INTEGER DEFAULT 10000)

IS

TYPE ARRAY IS TABLE OF OLAP.W_ASSESS_F%ROWTYPE;

l_data ARRAY;

CURSOR c IS select * from olap.W_ASSESS_F where X_ASSESS_TMPL_NAME<>'PDI Template';

BEGIN

    OPEN c;

    LOOP

    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

    FORALL i IN 1..l_data.COUNT

    INSERT /*+ appand */ INTO OLAP.W_ASSESS_F_PART VALUES l_data(i);

    commit;

    EXIT WHEN c%NOTFOUND;

    END LOOP;

    CLOSE c;

END Load_W_ASSESS_F_PART;

/

BULK Update Cursour 

**************************************************************

Source Table : OLAP.W_ASSESS_F

Target Table : OLAP.W_ASSESS_F_PART

**************************************************************

13:50:09 SQL> exec Load_W_ASSESS_F_PART;

PL/SQL procedure successfully completed.

Elapsed: 02:03:04.82

15:53:22 SQL> 

15:53:22 SQL>