How to use for loop for insert in Oracle?

Question

I'm having trouble inserting 1 million records in Oracle using the following for loop. The insert statement works on it's own but not inside the loop. What am I doing wrong here?

BEGIN
    FOR v_LoopCounter IN 385000000..1000000 LOOP
        INSERT INTO PORT (snb, real_exch, act_exch, user_type, status_id, category_id, assignable) 
            VALUES (TO_CHAR(v_LoopCounter),'GSMB','GSMB','GSM',0,90,'0');
        COMMIT;
    END LOOP;
END;

Solution 1

Don't do it that way, especially don't COMMIT within a loop.

Use a row generator; there are many techniques, one of them being this one:

SQL> create table test (snb number, real_exch varchar2(20));

Table created.

SQL> insert into test (snb, real_exch)
  2    select 385000000 + level - 1, 'GSMB'
  3    from dual
  4    connect by level <= 10;        --> you'd put a million here

10 rows created.

SQL> select * from test;

       SNB REAL_EXCH
---------- --------------------
 385000000 GSMB
 385000001 GSMB
 385000002 GSMB
 385000003 GSMB
 385000004 GSMB
 385000005 GSMB
 385000006 GSMB
 385000007 GSMB
 385000008 GSMB
 385000009 GSMB

10 rows selected.

SQL>

Solution 2

This worked for me:

BEGIN
FOR v_LoopCounter IN 1..1000000 LOOP
        INSERT INTO PORT (snb, real_exch, act_exch, user_type, status_id, category_id, assignable) 
            VALUES (TO_CHAR(v_LoopCounter),'GSMB','GSMB','GSM',0,90,'0');

END LOOP;
COMMIT;
END;

Solution 3

You dont need to use a loop to insert data like this. Try using straight SQL which will get you what you want in an elegant manner

 INSERT 
   INTO PORT (snb
              , real_exch
              , act_exch
              , user_type
              , status_id
              , category_id
              , assignable
             ) 
    select * 
      from (
            SELECT row_number() over(order by 1) + 385000000 -1 as loop_val
                   ,'GSMB'
                   ,'GSMB'
                   ,'GSM'
                   ,0
                   ,90
                   ,'0'
               FROM (select level as lvl
                       from dual
                     connect by level<=1000)a
               JOIN (select level as lvl
                       from dual
                     connect by level<=1000)b
                 ON 1=1  
            )x
       where x.loop_val<=385999999;

commit;

Comments

[Littlefoot]

What does it mean, "it doesn't work"? Any error? If so, which one? If not, what then?

[Gunnlaugur]

I need to v_LoopCounter initial value to be 385000000 and increment to 385999999

[krishh]

A brief explanation has been expected regarding the execution of the code. This will help readers to understand the difference between the code in the question and the answer.

[Phaki]

Why is it bad to commit in for loop?

[Littlefoot]

@Paki, if you have to rollback the whole transaction, you can't if error happens in the middle of a loop because partial changes have already been committed. If loop runs not-that-many-times (depending on what it does), commit outside of a loop. If there are vast millions of rows being affected, then consider running the whole thing in batches and commit after each batch is completed. Besides, committing within the loop (often?) ends up with ORA-01555 snapshot too old error.