11 Replies Latest reply on Sep 30, 2009 9:43 AM by 21205

    ORACLE FOR LOOP CURSOR

    725123
      Hi Gurus,

      I'm struggling to understand why the following proc doesn't work. It doesn't seem to populate the target table. I'm looping using a cursor variable bcz my source table is partitioned by student_id and thus it will be faster to do multiple inserts per student_id iterations.

      CREATE OR REPLACE PROCEDURE test_proc IS

      BEGIN
      FOR r_c1 in (SELECT student_id
      FROM student)
      LOOP
      INSERT INTO selection
      ( course_id,
      course_name,
      effective_date,
      lecturer,
      venue,
      time
      )
      SELECT course_id,
      course_name,
      effective_date,
      lecturer,
      venue,
      time
      FROM student_time_table
      where student_id = r_c1.student_id;

      COMMIT;
      end loop;
      end;
        • 1. Re: ORACLE FOR LOOP CURSOR
          21205
          ... because you don't have data in the Students table?
          why not:
                INSERT INTO selection
                ( course_id,
                course_name,
                effective_date,
                lecturer,
                venue,
                time
                )
                SELECT course_id,
                course_name,
                effective_date,
                lecturer,
                venue,
                time
                FROM student_time_table tt
                   , student s
                where tt.student_id = s.student_id;
          • 2. Re: ORACLE FOR LOOP CURSOR
            725123
            Hi Alex,

            Thanks for the reply, I do have data in the students table (1000 test ids). I use the for cursor because I want to do a multi insert per student_id. The sursor variable doesn't seem to be substituted inside the loop, why is that?
            • 3. Re: ORACLE FOR LOOP CURSOR
              jortri
              But you don't use table "Student" for insert.... use "student_time_table"
              • 4. Re: ORACLE FOR LOOP CURSOR
                Boneist
                I'm not sure what you're trying to do - you want to generate lots of rows in the selection table? Why not just run the insert that Alex has provided multiple times, then? I still don't see why you want a loop.

                And even if you did use a loop, you're committing inside the loop. This is a BIG no-no! (Even bigger than doing the loop in the first place!)

                When you say "commit", you're saying the transaction is over, meaning that your cursor information is no longer needed - this means you could easily run into the ORA-01555 snapshot too old error if another query decides it needs to use the memory currently used by your cursor. And by committing after every row, you're introducing a hell of a lot of overhead whilst the PL/SQL engine says to the db "go commit this insert... committed? Ok, commit this next one....committed? Ok... etc"

                Edited by: Boneist on 30-Sep-2009 09:38
                1 person found this helpful
                • 5. Re: ORACLE FOR LOOP CURSOR
                  21205
                  it should...
                  SQL> create table student
                    2  as
                    3  select rownum student_id
                    4    from all_objects
                    5   where rownum <= 10
                    6  /
                  
                  Table created.
                  
                  SQL> 
                  SQL> create or replace
                    2  procedure test_proc
                    3  as
                    4  begin
                    5     for r_cl in (select student_id
                    6                    from student
                    7                 )
                    8     loop
                    9       dbms_output.put_line(r_cl.student_id);
                   10     end loop;
                   11  end;
                   12  /
                  
                  Procedure created.
                  
                  SQL> sho err
                  No errors.
                  SQL> set serveroutput on
                  SQL> begin
                    2     test_proc;
                    3  end;
                    4  /
                  1
                  2
                  3
                  4
                  5
                  6
                  7
                  8
                  9
                  10
                  
                  PL/SQL procedure successfully completed.
                  • 6. Re: ORACLE FOR LOOP CURSOR
                    725123
                    Hi Alex,

                    An insert into a table inside the loop is the one that doesn't work. Any idea why, because my target table selection doesn't get populated at all.
                    • 7. Re: ORACLE FOR LOOP CURSOR
                      21205
                      That doesn't matter... is there data in the student_time_table table?
                      • 8. Re: ORACLE FOR LOOP CURSOR
                        725123
                        Hi Alex

                        Thanks a lot for the pointers, it was the commit that I was executing inside the loop that was invalidating my inserts and thus the target table wasn't being loaded.

                        Thanks gurus for your help
                        • 9. Re: ORACLE FOR LOOP CURSOR
                          21205
                          user8639474 wrote:
                          it was the commit that I was executing inside the loop that was invalidating my inserts and thus the target table wasn't being loaded.
                          Even though a COMMIT inside a loop is not a good idea, as Boneist already pointed out. This statement doesn't make sense... Can you show a copy paste example of this? Something like:
                          SQL> create table student
                            2  as
                            3  select rownum student_id
                            4    from all_objects
                            5   where rownum <= 10
                            6  /
                          
                          Table created.
                          
                          SQL> 
                          SQL> create table testing
                            2  (x int)
                            3  /
                          
                          Table created.
                          
                          SQL> 
                          SQL> create or replace
                            2  procedure test_proc
                            3  as
                            4  begin
                            5     for r_cl in (select student_id
                            6                    from student
                            7                 )
                            8     loop
                            9       dbms_output.put_line(r_cl.student_id);
                           10       insert into testing
                           11       values (r_cl.student_id);
                           12       commit;
                           13     end loop;
                           14  end;
                           15  /
                          
                          Procedure created.
                          
                          SQL> sho err
                          No errors.
                          SQL> set serveroutput on
                          SQL> begin
                            2     test_proc;
                            3  end;
                            4  /
                          1
                          2
                          3
                          4
                          5
                          6
                          7
                          8
                          9
                          10
                          
                          PL/SQL procedure successfully completed.
                          
                          SQL> select *
                            2    from testing
                            3  ;
                          
                                   X
                          ----------
                                   1
                                   2
                                   3
                                   4
                                   5
                                   6
                                   7
                                   8
                                   9
                                  10
                          
                          10 rows selected.
                          • 10. Re: ORACLE FOR LOOP CURSOR
                            725123
                            SHO err
                            set autoprint on
                            SET serveroutput on

                            CREATE TABLE student_time_table
                            AS
                            SELECT ROWNUM student_id,'xxxxx'||ROWNUM student_no,'yyyyyyyy'||ROWNUM venue,'0800' TIME
                            FROM all_objects
                            WHERE ROWNUM <= 5000;
                            CREATE TABLE student
                            AS
                            SELECT ROWNUM student_id
                            FROM all_objects
                            WHERE ROWNUM <= 10;

                            CREATE TABLE selection AS
                            SELECT ROWNUM student_id,' ' student_no,' ' venue,' ' TIME
                            FROM all_objects
                            WHERE 1=2;

                            CREATE OR REPLACE PROCEDURE test_proc AS
                            BEGIN
                            FOR r_cl IN ( SELECT student_id
                            FROM student )
                            LOOP
                            INSERT INTO selection
                            ( student_id
                            , student_no
                            , venue
                            , TIME
                            )
                            SELECT student_id
                            , student_no
                            , venue
                            , TIME
                            FROM student_time_table
                            WHERE student_id = r_cl.student_id;
                            END LOOP;
                            END;



                            BEGIN
                            test_proc;
                            COMMIT;
                            END;

                            select * from selection;

                            STUDENT_ID STUDENT_ VENUE TIME
                            ---------- -------- ---------- ----
                            1 xxxxx 1 yyyyyyyy1 0800
                            2 xxxxx 2 yyyyyyyy2 0800
                            3 xxxxx 3 yyyyyyyy3 0800
                            4 xxxxx 4 yyyyyyyy4 0800
                            5 xxxxx 5 yyyyyyyy5 0800
                            6 xxxxx 6 yyyyyyyy6 0800
                            7 xxxxx 7 yyyyyyyy7 0800
                            8 xxxxx 8 yyyyyyyy8 0800
                            9 xxxxx 9 yyyyyyyy9 0800
                            10 xxxxx 10 yyyyyyyy10 0800


                            10 rows selected.
                            • 11. Re: ORACLE FOR LOOP CURSOR
                              21205
                              even though this is not a copy paste example (some exceptions), this does not prove your claim that the COMMIT was causing the problems...