1 2 Previous Next 19 Replies Latest reply: Sep 12, 2012 11:29 AM by Ashu_Neo RSS

    PLSQL Error while using collections dATABASE:10G

    934835
      Hi,

      I am getting below error while compiling below code:

      Error: DML statement without BULK In-BIND cannot be used inside FORALL

      Could you suggest.
      create or replace PROCEDURE V_ACCT_MTH ( P_COMMIT_INTERVAL  NUMBER DEFAULT 10000)
      is
      
      
       CURSOR CUR_D_CR_ACCT_MTH
       IS 
       SELECT * FROM D_ACCT_MTH;
      
       TYPE l_rec_type IS TABLE OF CUR_D_CR_ACCT_MTH%ROWTYPE
       INDEX BY PLS_INTEGER; 
      
       v_var_tab    l_rec_type;
       v_empty_tab  l_rec_type;
      
       v_error_msg  VARCHAR2(80);
       v_err_code   VARCHAR2(30); 
      
       V_ROW_CNT NUMBER :=0;
       --R_DATA    NUMBER :=1;
      
      BEGIN
      
       OPEN CUR_D_CR_ACCT_MTH;
      
       v_var_tab := v_empty_tab; 
       
       LOOP
          FETCH CUR_D_CR_ACCT_MTH BULK COLLECT INTO v_var_tab LIMIT P_COMMIT_INTERVAL;
          EXIT WHEN v_var_tab.COUNT=0;
      
              FORALL R_DATA IN 1..v_var_tab.COUNT
      
                 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
                (
                 DATE_KEY(R_DATA)
                ,ACCT_KEY(R_DATA)
                ,P_ID(R_DATA) 
                ,ORG_KEY(R_DATA) 
                ,FDIC_KEY(R_DATA) 
                ,BAL(R_DATA) 
                ,BAL(R_DATA) 
                ,BAL(R_DATA) 
                ,BAL(R_DATA) 
                ,BAL(R_DATA) 
                ,BAL(R_DATA) 
                ,BAL(R_DATA) 
                ,BAL(R_DATA) 
                ,BAL(R_DATA) 
                ,BAL(R_DATA) 
                ,BAL(R_DATA) 
                ,BAL(R_DATA) 
                ,BAL(R_DATA) 
                ,BAL(R_DATA) 
                ,BAL(R_DATA) 
                ,BAL(R_DATA) 
                );
      
       
              COMMIT;
      
       END LOOP; 
          
       CLOSE CUR_D_CR_ACCT_MTH;  
      
      EXCEPTION
      WHEN OTHERS THEN
       v_error_msg:=substr(sqlerrm,1,50);
       v_err_code :=sqlcode;
       DBMS_OUTPUT.PUT_LINE(v_error_msg,v_err_code);
      END V_ACCT_MTH;
        • 1. Re: PLSQL Error while using collections dATABASE:10G
          sb92075
          INSERT INTO ACCT_F_ACCT_MTH SELECT * FROM D_ACCT_MTH;
          • 2. Re: PLSQL Error while using collections dATABASE:10G
            934835
            Hi,

            Thanks for your suggestions.

            Here i am using above method using forall because of large volume of data.
            Any suggestion ?

            Thanks.
            • 3. Re: PLSQL Error while using collections dATABASE:10G
              sb92075
              931832 wrote:
              Hi,

              Thanks for your suggestions.

              Here i am using above method using forall because of large volume of data.
              Any suggestion ?

              Thanks.
              PL/SQL will NEVER be faster than plain SQL!
              • 4. Re: PLSQL Error while using collections dATABASE:10G
                934835
                yes, i got your point...sql will be faster.
                Appreciate if you can suggest on the above code.
                • 5. Re: PLSQL Error while using collections dATABASE:10G
                  sb92075
                  931832 wrote:
                  yes, i got your point...sql will be faster.
                  Appreciate if you can suggest on the above code.
                  delete, remove, & eliminate all EXCEPTION code
                  For reason why check these links.

                  http://tkyte.blogspot.com/2007/03/dreaded-others-then-null-strikes-again.html

                  http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html

                  http://tkyte.blogspot.com/2007/03/challenge.html
                  • 6. Re: PLSQL Error while using collections dATABASE:10G
                    rp0428
                    >
                    DATE_KEY(R_DATA)
                    >
                    DATE_KEY is not a function or an array so not sure where you got that syntax.

                    The data is in the collection so you have to take it OUT of the collection. The collection variable is v_var_tab so the syntax is
                        v_var_tab(R_DATA).DATE_KEY,
                       . . . 
                    • 7. Re: PLSQL Error while using collections dATABASE:10G
                      William Robertson
                      Here i am using above method using forall because of large volume of data.
                      And yet your 'commit interval' is 10K, and it's only INSERT (less undo than UPDATE or DELETE). Sounds like you need a bigger laptop ;)
                      • 8. Re: PLSQL Error while using collections dATABASE:10G
                        934835
                        thanks...a lot
                        • 9. Re: PLSQL Error while using collections dATABASE:10G
                          Biju Das
                          >
                          Any suggestion ?
                          >


                          In addition, If you are in 11gR2, APPEND_VALUES hint can give better performance by allowing us to use direct-path inserts within
                          the FORALL statement.

                          Source: http://www.oracle-base.com/articles/11g/append-values-hint-11gr2.php

                          - if you can do it in all SQL, do it, never write code unless you are forced to.
                          - if you absolutely cannot do it in sql, write as little plsql as you can
                          - if you cannot do it in plsql write as little java as you can

                          Regards
                          Biju
                          • 10. Re: PLSQL Error while using collections dATABASE:10G
                            Ashu_Neo
                            Hi William,
                            Could you please let me know, Why we should commit outside of loop and what should be limit for this bulk collect program ? ( Must be less then 10K)
                            • 11. Re: PLSQL Error while using collections dATABASE:10G
                              sb92075
                              Ashu_Neo wrote:
                              Hi William,
                              Could you please let me know,
                              Why we should commit outside of loop
                              COMMIT inside LOOP results in longer elapsed time (slower procedure processing) & increases odds for ORA-01555 Snapshot Too Old error
                              • 12. Re: PLSQL Error while using collections dATABASE:10G
                                Biju Das
                                do something like this:
                                 open the cursor;
                                   loop
                                       fetch c bulk collect into l_c1,  ....... LIMIT 1000;
                                       forall i in 1 .. l_c1.count
                                            insert /*+ APPEND_VALUES */ into ..... values ( L_c1(i), .... );
                                       end loop;
                                       exit when c%notfound;
                                       --commit;
                                   end loop;
                                 close cursor;
                                Observe the performance chanfing the limit 100,500,1000. 10k -you are going to blew your process memory.

                                Regards
                                Biju

                                Edited by: biju2012 on Sep 12, 2012 8:58 AM
                                • 13. Re: PLSQL Error while using collections dATABASE:10G
                                  rp0428
                                  No - take the commit out of the loop altogether.
                                  • 14. Re: PLSQL Error while using collections dATABASE:10G
                                    Billy~Verreynne
                                    931832 wrote:

                                    Here i am using above method using forall because of large volume of data.
                                    Which is a FLAWED approach. Always.

                                    FORALL is not suited to "move/copy" large amounts of data from one table to another.
                                    Any suggestion ?
                                    Use only SQL. It is faster. It has less overheads. It can execute in parallel.

                                    So execute it in parallel to move/copy that data. You can roll this manually via the DBMS_PARALLEL_EXECUTE interface. Simplistic example:
                                    declare
                                            taskName        varchar2(30) default 'PQ-task-1';
                                            parallelSql     varchar2(1000);
                                    begin
                                            --// create trask
                                            DBMS_PARALLEL_EXECUTE.create_task( taskName );
                                    
                                            --// chunk the table by rowid ranges
                                            DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(
                                                    task_name => taskName,
                                                    table_owner => user,
                                                    table_name => 'D_ACCT_MNTH',
                                                    by_row => true,
                                                    chunk_size => 100000
                                            );
                                    
                                            --// create insert..select statement to copy a chunk of rows
                                            parallelSql := 'insert into acct_f_acct_mth select * from d_acct_mnth
                                                            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( taskName ) != DBMS_PARALLEL_EXECUTE.Finished loop
                                                    DBMS_LOCK.Sleep(10);
                                            end loop;
                                    
                                            --// remove task
                                            DBMS_PARALLEL_EXECUTE.drop_task( taskName );
                                    end;
                                    /
                                    Details in Oracle® Database PL/SQL Packages and Types Reference guide.

                                    For 10g, the EXACT SAME approach can be used - by determining the rowid chunks/ranges via a SQL and then manually running parallel processes as DBMS_JOB. See {message:id=1108593} for details.
                                    1 2 Previous Next