13 Replies Latest reply: Jan 7, 2009 7:22 AM by Satyaki_De RSS

    bulk insert [forall] in PL/SQL when inserting new data?

    537177
      Hi,

      I need to generate some test data and insert it into a table.
      It works fine with normal for loop but takes time.
      Is there any way to use bulk insert without using a cursor?
      (I cannot define any cursor since the data is not there yet.

      This is what is working, but takes time:

      PROCEDURE normal_insert
      IS
      v_field_company NUMBER;
      v_commit_counter NUMBER;
      BEGIN
      -- truncate test_table
      EXECUTE IMMEDIATE 'TRUNCATE TABLE test_table';

      v_commit_counter := 0;

      FOR i IN 1 .. 10000000
      LOOP
      INSERT INTO test_table
      (tracking_id, start_date,
      end_date,
      company_id
      )
      VALUES (i, TO_DATE ('2000-01-01', 'YYYY-MM-DD'),
      TO_DATE ('2000-12-31', 'YYYY-MM-DD'),
      TO_CHAR (v_field_company)
      );

      v_field_company := v_field_company + 1;

      IF (v_commit_counter = 1000)
      THEN
      COMMIT;
      v_commit_counter := 0;
      END IF;

      v_commit_counter := v_commit_counter + 1;
      END LOOP;

      COMMIT;
      END normal_insert;

      This is what is want, but did not work...:


      PROCEDURE fast_insert
      IS
      v_field_company NUMBER;
      v_commit_counter NUMBER;
      BEGIN
      -- truncate test_table
      EXECUTE IMMEDIATE 'TRUNCATE TABLE test_table';


                FORALL i IN 1 .. 10000000

      INSERT INTO progress_final
      (tracking_id, start_date,
      end_date,
      company_id
      )
      VALUES (i, TO_DATE ('2000-01-01', 'YYYY-MM-DD'),
      TO_DATE ('2000-12-31', 'YYYY-MM-DD'),
      TO_CHAR (v_field_company)
      );
      COMMIT;
      END fast_insert;

      Here I get 2 errors:

      PLS-00430: FORALL iteration variable I is not allowed in this context
      PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

      Thanks in advance!
        • 1. Re: bulk insert [forall] in PL/SQL when inserting new data?
          SatishKandi
          In your normal FOR loop, use bind variables and run the INSERTs using EXECUTE IMMEDIATE. I guess you are facing a problem of parsing here.
          • 2. Re: bulk insert [forall] in PL/SQL when inserting new data?
            341161
            You're using FORALL incorrectly. You can only use it to BULK insert based on the values of a collection. You're trying to use it outside of that context.

            What you're doing could more easily be achieved by doing an

            INSERT INTO table
            SELECT...
            • 3. Re: bulk insert [forall] in PL/SQL when inserting new data?
              396018
              FORALL works with collection. here is an example from documentation.
              if you are fetching from cursor, use BULK COLLECT to fetch the columns into collection and then use FORALL.
              CREATE TYPE PNum AS OBJECT (n NUMBER);
              /
              CREATE TABLE partno OF PNum;
              
              DECLARE
                TYPE       NumTab IS TABLE OF NUMBER;
                nums       NumTab := NumTab(1, 2, 3, 4);
                TYPE       PNumTab IS TABLE OF PNum;
                pnums      PNumTab := PNumTab(PNum(1), PNum(2), PNum(3), PNum(4));
              BEGIN
                FORALL i IN pnums.FIRST..pnums.LAST
                  INSERT INTO partno VALUES(pnums(i));
                FORALL i IN nums.FIRST..nums.LAST
                  DELETE FROM partno WHERE n = 2 * nums(i);
                FORALL i IN nums.FIRST..nums.LAST
                  INSERT INTO partno VALUES(100 + nums(i));
              END;
              /
              • 4. Re: bulk insert [forall] in PL/SQL when inserting new data?
                532657
                Try this....

                DECLARE
                TYPE CL_ITAB IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
                V_CL_ITAB CL_ITAB;
                TYPE CL_VTAB IS TABLE OF VARCHAR2(25) INDEX BY PLS_INTEGER;
                V_CL_VTAB CL_VTAB;
                TYPE CL_DTAB IS TABLE OF DATE INDEX BY PLS_INTEGER;
                V_CL_DTAB CL_DTAB;
                BEGIN
                FOR I IN 1.. 1000 LOOP
                          V_CL_ITAB(I):= DBMS_RANDOM.VALUE(1,1000);
                          V_CL_VTAB (I):=DBMS_RANDOM.STRING('A',20);
                          V_CL_DTAB (I):=TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2453737, 2454101)),'J');          
                     END LOOP;
                     FORALL I IN V_CL_ITAB.FIRST .. V_CL_ITAB.LAST
                          EXECUTE IMMEDIATE 'INSERT INTO TEST_ORDER VALUES( :N, :STR , :DT ) ' USING V_CL_ITAB(I), V_CL_VTAB (I), V_CL_DTAB (I);          
                     COMMIT;
                END;
                /
                • 5. Re: bulk insert [forall] in PL/SQL when inserting new data?
                  Rob van Wijk
                  As srouane pointed out, the following will be much much faster and requires less code:

                  not tested
                  truncate table test_table;

                  insert into test_table
                  select n
                       , to_date('01012000','ddmmyyyy')
                       , to_date('31122000','ddmmyyyy')
                       , null
                    from (select level l from dual connect by level <= 10000000);
                  Regards,
                  Rob.
                  • 6. Re: bulk insert [forall] in PL/SQL when inserting new data?
                    537177
                    Hi guys, first of all thanks for your replay. I am amiased how fast it went :-)

                    Suresh I guess I stick with your solution it looks really cool man.
                    I modified your code a bit , I hope you are not angry or so ;-).

                    Here is the procedure first:

                    PROCEDURE oracle_forum
                    IS
                    TYPE int_tab IS TABLE OF INTEGER
                    INDEX BY PLS_INTEGER;

                    tracking_id int_tab;

                    TYPE var_tab IS TABLE OF VARCHAR2 (100)
                    INDEX BY PLS_INTEGER;

                    company_id var_tab;

                    TYPE date_tab IS TABLE OF DATE
                    INDEX BY PLS_INTEGER;

                    start_date date_tab;
                    end_date date_tab;
                    group_id int_tab;
                    BEGIN
                    EXECUTE IMMEDIATE 'TRUNCATE TABLE progress_final';


                    LOOP
                    FOR i IN 1 .. 10
                    LOOP
                    tracking_id (i) := i;
                    start_date (i) := TO_DATE ('2000-01-01', 'YYYY-MM-DD');
                    end_date (i) := TO_DATE ('2000-12-31', 'YYYY-MM-DD');
                    company_id (i) := TO_CHAR (1000 + i);
                    group_id (i) := 0;
                    END LOOP;

                    FORALL i IN tracking_id.FIRST .. tracking_id.LAST
                    EXECUTE IMMEDIATE 'INSERT INTO progress_final VALUES( :N, :DT, :DT, :STR, :N ) '
                    USING tracking_id (i),
                    start_date (i),
                    end_date (i),
                    company_id (i),
                    group_id (i);
                    COMMIT;
                    END LOOP;
                    END oracle_forum;
                    The error I am getting now is:

                    ERROR at line 1:
                    ORA-04030: out of process memory when trying to allocate 276108 bytes (PLS
                    non-lib hp,DARWIN)
                    ORA-06512: at line 31


                    I guess I have to put LIMIT but the tricky thig is that I do not want to loose my counter i.
                    What I do not want is if I have a LIMIT with 1000,
                    so that the next assignments start with 0.
                    What do you thing guys is it possible?
                    • 7. Re: bulk insert [forall] in PL/SQL when inserting new data?
                      32685
                      Hello

                      Did you not try Rob's suggestion - it will be faster still than the collection based approach and you won't have to worry about running out of memory.

                      HTH

                      David
                      • 8. Re: bulk insert [forall] in PL/SQL when inserting new data?
                        537177
                        Hi Rob,
                        thanks for your solution.
                        In fact this is a fast one, and require less code except that I can not increment one of the colum here. Everything stays static, so I can not use it :-(

                        Thanks anyway
                        • 9. Re: bulk insert [forall] in PL/SQL when inserting new data?
                          32685
                          if you need an incrementor for the company id, you could use ROWNUM...
                          • 10. Re: bulk insert [forall] in PL/SQL when inserting new data?
                            Rob van Wijk
                            You sure are able to increment.
                            I see now I made a syntax error: where I said "n" I meant "l". "l" is incremented from 1 until 10000000.

                            Regards,
                            Rob.
                            • 11. Re: bulk insert [forall] in PL/SQL when inserting new data?
                              537177
                              Rob you are my man!!!!

                              This one is regular for loop insert with 2000000 records

                              SQL> @C:\D\data\how2s\oracle\sqlplus\insert_values_into_table.sql

                              PL/SQL procedure successfully completed.

                              Elapsed: 00:01:43.79

                              This is your insert with the same 2 millions records

                              SQL> @C:\D\data\how2s\oracle\sqlplus\insert_values_into_table.sql

                              PL/SQL procedure successfully completed.

                              Elapsed: 00:00:07.82

                              Well no comment :-)

                              Thanks man
                              • 12. PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL
                                678589
                                CREATE OR REPLACE procedure rt711pq_op
                                               (
                                FromTabName in      varchar2,
                                     ToTabName     in     varchar2,
                                     in_amc_cd in varchar2
                                     )
                                     as
                                TYPE qryCurTyp IS REF CURSOR;
                                qry_cv     qryCurTyp;
                                TYPE fetch_rec IS TABLE OF rt304mb%rowtype INDEX BY BINARY_INTEGER;
                                fetch_rt304mb fetch_rec;
                                errors number;
                                dml_errors EXCEPTION;
                                BEGIN
                                BEGIN
                                OPEN qry_cv for SELECT * FROM RT304MB where ref_no > 0
                                and ((payment_option is null and pay_ref_no IS NOT NULL) or
                                (payment_option not in ('m','i','n') and pay_ref_no is not null)) and amc_cd = rt711pq_op.in_amc_cd;

                                FETCH qry_cv BULK COLLECT INTO fetch_rt304mb;

                                EXCEPTION WHEN OTHERS THEN
                                DBMS_OUTPUT.PUT_LINE(' Error :' || sqlerrm);
                                END;

                                BEGIN

                                FORALL i IN fetch_rt304mb.FIRST..fetch_rt304mb.LAST SAVE EXCEPTIONS
                                INSERT INTO RT304hb VALUES fetch_rt304mb(i);
                                EXCEPTION
                                WHEN dml_errors THEN
                                errors := SQL%BULK_EXCEPTIONS.COUNT;
                                dbms_output.put_line('Number of errors is ' || errors);
                                FOR i IN 1..errors LOOP
                                dbms_output.put_line('Error ' || i || ' occurred during '||
                                'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
                                dbms_output.put_line('Oracle error is ' ||
                                SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
                                END LOOP;
                                END;

                                COMMIT;

                                BEGIN

                                FORALL i IN fetch_rt304mb.FIRST..fetch_rt304mb.LAST SAVE EXCEPTIONS
                                     --delete all rows from RT304MB for fetch_rt304mb(i);
                                -- DELETE FROM RT304MB USING fetch_rt304mb(i).ref_no;
                                     
                                DELETE FROM RT304MB where ref_no>0
                                and ((payment_option is null and pay_ref_no IS NOT NULL) or
                                (payment_option not in ('m','i','n') and pay_ref_no is not null)) and amc_cd = rt711pq_op.in_amc_cd;
                                     EXCEPTION
                                WHEN dml_errors THEN
                                errors := SQL%BULK_EXCEPTIONS.COUNT;
                                dbms_output.put_line('Number of errors is ' || errors);
                                FOR i IN 1..errors LOOP
                                dbms_output.put_line('Error ' || i || ' occurred during '||
                                'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
                                dbms_output.put_line('Oracle error is ' ||
                                SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
                                END LOOP;
                                END;

                                COMMIT;
                                END rt711pq_op;
                                /
                                • 13. Re: PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL
                                  Satyaki_De
                                  Welcome to OTN. :)

                                  Why are you digging the old post?

                                  Did you check the post closely?
                                           Re: bulk insert [forall] in PL/SQL when inserting new data?
                                  Posted: Oct 3, 2006 5:20 PM   in response to: Rob van Wijk in response to: Rob van Wijk 
                                  Regards.

                                  Satyaki De.