1 2 Previous Next 19 Replies Latest reply: Sep 12, 2012 11:29 AM by Ashu_Neo Go to original post RSS
      • 15. Re: PLSQL Error while using collections dATABASE:10G
        934835
        Hi Billy,

        I developed below code and compiled with below errors.
        any suggestions please.

        errors:
        PLS-00201: identifier 'DBMS_PARALLEL_EXECUTE.CREATE_TASK' must be declared
        PLS-00201: identifier 'DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID' must be declared
        PLS-00201: identifier 'DBMS_PARALLEL_EXECUTE.RUN_TASK' must be declared
        PLS-00201: identifier 'DBMS_PARALLEL_EXECUTE.TASK_STATUS' must be declared
        PLS-00201: identifier 'DBMS_PARALLEL_EXECUTE.DROP_TASK' must be declared
        declare
                taskName        varchar2(30) default 'PQ-task-1'; --default 'mytask';
                parallelSql     varchar2(1000);
        begin
                --// create trask
                DBMS_PARALLEL_EXECUTE.create_task( 'mytask' );
         
                --// chunk the table by rowid ranges
                DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(
                        task_name => 'mytask',
                        table_owner => 'dw',
                        table_name => 'F_ACCT_MTH',
                        by_row => true,
                        chunk_size => 100000
                );
         
                --// create insert..select statement to copy a chunk of rows
                parallelSql := 'insert into ACCT_F_ACCT_MTH 
                                (
                    DATE_KEY
                   ,ACCT_KEY
                   ,P_ID
                   ,ORG_KEY
                   ,FDIC_KEY
                   ,BAL
                   ,BAL1
                   ,BAL2
                   ,BAL3
                   ,BAL4
                   ,BAL5
                   ,BAL6
                   ,BAL7
                   ,BAL8
                   ,BAL9
                   ,BAL10
                   ,BAL11
                   ,BAL12
                   ,BAL13
                   ,BAL14
                   ,BAL15
                  )
                   VALUES
                   select 
                   (
                    DATE_KEY
                   ,ACCT_KEY
                   ,P_ID
                   ,ORG_KEY
                   ,FDIC_KEY
                   ,BAL
                   ,BAL
                   ,BAL
                   ,BAL
                   ,BAL
                   ,BAL
                   ,BAL
                   ,BAL
                   ,BAL
                   ,BAL
                   ,BAL
                   ,BAL
                   ,BAL
                   ,BAL
                   ,BAL
                   ,BAL
                   )
                   from F_ACCT_MTH
                   where rowid between :start_id and :end_id';
         
                --// run the task using 5 parallel processes
                DBMS_PARALLEL_EXECUTE.Run_Task(
                        task_name => taskName,
                        sql_stmt => parallelSql,
                        language_flag => DBMS_SQL.NATIVE,
                        parallel_level => 5
                );
         
                --// wait for it to complete
                while DBMS_PARALLEL_EXECUTE.task_status( 'mytask' ) != DBMS_PARALLEL_EXECUTE.Finished loop
                        DBMS_LOCK.Sleep(10);
                end loop;
         
                --// remove task
                DBMS_PARALLEL_EXECUTE.drop_task( 'mytask' );
        end;
        / 
        • 16. Re: PLSQL Error while using collections dATABASE:10G
          BrendanP
          Upgrade to 11GR2?
          • 17. Re: PLSQL Error while using collections dATABASE:10G
            934835
            we are using Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
            • 18. Re: PLSQL Error while using collections dATABASE:10G
              BrendanP
              Hence why you can't use 11.2 new functionality...Look at the earlier posts suggesting 10g options.
              • 19. Re: PLSQL Error while using collections dATABASE:10G
                Ashu_Neo
                Please Check SQL string creation is wrongly noted here and may in your code too!

                Existing
                parallelSql := 'insert into ACCT_F_ACCT_MTH 
                                        (
                            DATE_KEY
                ............................
                It should be like this
                parallelSql := 'insert into ACCT_F_ACCT_MTH' || 
                                       ' ('||
                            'DATE_KEY'||.....
                ......
                If you are not using concatenation symbol, then it wont be considered as a string. Else write it in one line. Then compile.
                1 2 Previous Next