-2

i have a table that have about 30 million record and i want to loop on the records that exists in the table like this : 

for i in (select * from tbl_items it
             Left join location lo 
              On lo.id=it.location_id) loop 
     // check some condition 
     // insert into log table 
end loop ;

is there any limitation in for loops inside a procedure in PL/SQL in Oracle11g ? i thinks it's have limitation and how can i have skip the limit ?

Mohammad Mirzaeyan
  • 845
  • 3
  • 11
  • 30
  • That's likely to be extremely slow ... – Keith John Hutchison Feb 15 '17 at 21:05
  • 2
    No, there is no limit (it would be pretty stupid if there was) –  Feb 15 '17 at 21:14
  • @a_horse_with_no_name i think there is a limit , i try a insert and i want to insert data in another table it just insert about 1 million !! – Mohammad Mirzaeyan Feb 15 '17 at 21:17
  • 4
    But why do you think that's a PL/SQL loop limitiation? What error did you get? What logic is inside the loop? Is there really a good reason to use PL/SQL instead of a plain SQL `insert ... select` statement, which would usually be much faster and more efficient? – Alex Poole Feb 15 '17 at 21:30
  • 2
    A loop is a bad choice for that. Just do a `insert into target_table (col1, col2, col3) select c1, c2, c3 from source_table` that will be a **lot** faster –  Feb 15 '17 at 21:52

1 Answers1

4

PL/SQL does not have any practical limit on the number of iterations in a loop.

I can't prove it but here are my reasons for saying there is no limit:

  1. No limit is listed in the PL/SQL Program Limits section of the Database PL/SQL Language Reference. One thing Oracle is usually good at is keeping the hard limits to a minimum or always having a workaround.
  2. Creating a limit like this in a programming language doesn't make sense.
  3. One reason I started concentrating on Oracle SQL and PL/SQL many years ago was to avoid a programming language that had an iteration limit. Few programming problems are more depressing than an error like "maximum loop limit exceeded". If this problem existed in PL/SQL a lot of people would know and be angry about it.

At some point every loops dies - you'll run out of a resource. But for a sane language like PL/SQL you can worry more about cosmic rays.

Perhaps by "limit" you mean the LIMIT bulk collect option? If so, Oracle cursor-FOR loops use a limit of 100. Which means it will fetch 100 rows at a time. This is a good trade-off between memory and reducing overhead and in practice is always good enough.

As a_horse_with_no_name suggested, avoiding loops completely is a best practice in Oracle SQL. Set based programs are almost always faster than row-by-row.

Community
  • 1
  • 1
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • I have some logic inside the loop , so i must use the loop , and the select in the loop join from 3,4 table – Mohammad Mirzaeyan Feb 16 '17 at 05:34
  • @MohammadMirzaeyan That's fine, it should still work. Whatever the problem is it's not caused by some PL/SQL loop limit. Can you add some more information about exactly what's going wrong? – Jon Heller Feb 16 '17 at 06:50
  • I edit the question , the problem is when i call the procedure with a item id it will be insert into log table but when i dont put item id where clause it wont insert it . – Mohammad Mirzaeyan Feb 16 '17 at 07:15
  • There's no "item id" or "log table" in your question so I still don't know what problem you are having. Please add a [simple example reproducing the issue](http://stackoverflow.com/help/mcve). – William Robertson Feb 16 '17 at 12:51