This discussion is archived
1 Reply Latest reply: Sep 3, 2012 10:28 AM by jcgeorge RSS

ISSUE ON USING DBMS_PARALLEL_EXECUTE.RUN_TASK

jcgeorge Newbie
Currently Being Moderated
I have an insert which I am doing using

DBMS_PARALLEL_EXECUTE.CREATE_TASK(TASK_NAME => L_TASK_NAME);
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID(TASK_NAME => L_TASK_NAME
,TABLE_OWNER => 'FUSION'
,TABLE_NAME => P_BK_TABLE_NAME
,BY_ROW => TRUE
,CHUNK_SIZE => 1000);
DBMS_PARALLEL_EXECUTE.RUN_TASK(TASK_NAME => L_TASK_NAME
,SQL_STMT => L_DYNAMIC_SQL_SEL_PT
,LANGUAGE_FLAG => DBMS_SQL.NATIVE
,PARALLEL_LEVEL => NULL);
L_STATUS := DBMS_PARALLEL_EXECUTE.TASK_STATUS(TASK_NAME => L_TASK_NAME);
DBMS_OUTPUT.PUT_LINE('L_STATUS:'||L_STATUS);

L_DYNAMIC_SQL_SEL_PT I am passing is

INSERT INTO DEBUG_BK( MSG,LINE,CUST_SET_ID ) ( SELECT MSG,LINE ,CAST(1 AS NUMBER ) AS CUST_SET_ID FROM DEBUG WHERE ROWID BETWEEN :START_ID AND :END_ID)

The above statements run successfully and I get status value '6' , which means 'Finished without errors' . But I do not see the data getting inserted into table.

Any clue on this as , why the insert is not happening?.

Thanks in advance.

Joseph

Edited by: jcgeorge on Aug 31, 2012 12:07 PM
  • 1. Re: ISSUE ON USING DBMS_PARALLEL_EXECUTE.RUN_TASK
    jcgeorge Newbie
    Currently Being Moderated
    Issue :

    I am using "DBMS_PARALLEL_EXECUTE" routines for inserting data (parallel) into a backup table from a base table. The procedures ran successfully and return status is "Finished without Errors", but the rows are not inserted into the table.

    (Please see the below scripts, which one can try out in thier test Database to reproduce the issue.)

    script1
    =============================================================
    CREATE TABLE DEBUG ( MSG VARCHAR2(2000) , LINE NUMBER) ;
    CREATE TABLE DEBUG_BK AS (SELECT * FROM DEBUG WHERE 1 = 2 );
    ALTER TABLE DEBUG_BK MODIFY ( CUST_SET_ID NUMBER );

    DECLARE
    BEGIN
    FOR CTR IN 1..2000
    LOOP
    INSERT INTO DEBUG VALUES ('Msg'||TO_CHAR(CTR) , CTR);
    END LOOP;
    COMMIT;
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('EXCEPTION :'||SQLERRM);
    END;

    script2
    =============================================================
    DECLARE
    L_TASK_NAME VARCHAR2(100) := 'CBT:'||TO_CHAR(SYSDATE, 'DD-MM-YY-HH:MI:SS');
    L_DYNAMIC_SQL_SEL_PT VARCHAR2(3000) := 'INSERT INTO DEBUG_BK( MSG,LINE,CUST_SET_ID ) ( SELECT MSG,LINE ,CAST(1 AS NUMBER ) AS CUST_SET_ID FROM DEBUG WHERE ROWID BETWEEN :START_ID AND :END_ID)' ;
    L_STATUS NUMBER ;
    L_OP VARCHAR2(100);
    BEGIN
    DBMS_PARALLEL_EXECUTE.CREATE_TASK(TASK_NAME => L_TASK_NAME);
    SELECT TASK_NAME||''||STATUS
    INTO L_OP
    FROM USER_PARALLEL_EXECUTE_TASKS
    WHERE TASK_NAME = L_TASK_NAME ;
    DBMS_OUTPUT.PUT_LINE('AFTER CREATE TASK :L_OP:'||L_OP);
    DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID( TASK_NAME => L_TASK_NAME
    ,TABLE_OWNER => 'FUSION'
    ,TABLE_NAME => 'DEBUG_BK'
    ,BY_ROW => TRUE
    ,CHUNK_SIZE => 1000);
    SELECT TASK_NAME||''||STATUS
    INTO L_OP
    FROM USER_PARALLEL_EXECUTE_TASKS
    WHERE TASK_NAME = L_TASK_NAME ;
    DBMS_OUTPUT.PUT_LINE('AFTER CREATE TASK :L_OP:'||L_OP);

    DBMS_PARALLEL_EXECUTE.RUN_TASK( TASK_NAME => L_TASK_NAME
    ,SQL_STMT => L_DYNAMIC_SQL_SEL_PT
    ,LANGUAGE_FLAG => DBMS_SQL.NATIVE
    ,PARALLEL_LEVEL => NULL);
    L_STATUS := DBMS_PARALLEL_EXECUTE.TASK_STATUS(TASK_NAME => L_TASK_NAME);
    DBMS_OUTPUT.PUT_LINE('L_STATUS:'||L_STATUS);
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('CREATE_BACKUP EXCEPTION :'||SQLERRM);
    END;
    =============================================================

    Run these files from SQLPlus on a test instance.

    First Script creates debug (DEBUG) and backup (DEBUG_BK) table . The second script reads the row from debug table and insert them in parallel to Backup Table

    What is observed that , the when the row chunks are inserted in parallel , the status of the chunks coming as Processed. But eventually the DEBUG_BK is not having the rows from DEBUG table.

    What could be the issue here ?. Any pointers are highly helpful.

    Thanks

    Joseph

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points