This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Sep 12, 2012 9:29 AM by Ashu_Neo RSS

PLSQL Error while using collections dATABASE:10G

934835 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    INSERT INTO ACCT_F_ACCT_MTH SELECT * FROM D_ACCT_MTH;
  • 2. Re: PLSQL Error while using collections dATABASE:10G
    934835 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    thanks...a lot
  • 9. Re: PLSQL Error while using collections dATABASE:10G
    Biju Das Journeyer
    Currently Being Moderated
    >
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    No - take the commit out of the loop altogether.
  • 14. Re: PLSQL Error while using collections dATABASE:10G
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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