1 Reply Latest reply: Sep 3, 2012 12:28 PM by jcgeorge RSS

    ISSUE ON USING DBMS_PARALLEL_EXECUTE.RUN_TASK

    jcgeorge
      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
          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