Thanks for the question.

Asked: July 07, 2016 - 3:33 pm UTC

Last updated: July 12, 2016 - 1:29 am UTC

Version: oracle 11g R2

Viewed 10K+ times! This question is

You Asked

Hi,

I have a cursor for loop which does some calculation in the cursor query. With in the loop I have insert with commit for every iteration. There are not more statement apart from INSERT & COMMIT inside the loop.

Example:
DECLARE
cursor cur IS
SELECT col1,
col2,
func_call_using table1(some parameters) col3
FROM table2;
BEGIN
FOR rec IN cur LOOP
INSERT INTO table1 VALUES rec;
COMMIT;
END LOOP;
END;
/

Is there any possibility that the cursor query gets executed while the loop is running already?

Thanks for your help in advance

and Connor said...

Yes it can...and it can easily fool you

Lets look at this example


SQL> drop table t1 purge;

Table dropped.

SQL> drop table t2 purge;

Table dropped.

SQL> create table t1 ( x int, y int );

Table created.

SQL> create table t2 as select rownum a from dual connect by level <= 5;

Table created.

SQL> select * from t2;

         A
----------
         1
         2
         3
         4
         5

5 rows selected.

SQL>
SQL> create or replace
  2  function count_of_t1 return int is
  3    x int;
  4  begin
  5    select count(*) into x from t1;
  6    return x;
  7  end;
  8  /

Function created.

SQL>
SQL>
SQL> set serverout on
SQL> declare
  2    cursor cur is select a, count_of_t1 cnt from t2;
  3  begin
  4    for rec in cur loop
  5       dbms_output.put_line(rec.a||','||rec.cnt);
  6       insert into t2 values (0);
  7       insert into t1 values (1,1);
  8    end loop;
  9  end;
 10  /
1,0
2,0
3,0
4,0
5,0

PL/SQL procedure successfully completed.

SQL>
SQL>

All looks fine...as if everything was consistent all the way through. But that was only because we were *lucky*.

SQL guarantees read consistency, which is why, even though we are inserting rows into T2 as we go, we still only loop through 5 rows.

But this isnt *just* SQL, its also PLSQL. That SQL *inside* your PLSQL function is *also* consistent, but consistent based on the point in time that the *function* is executed, *not* when the outer SQL is being run.

So each time we call it, we are starting from a *new* point in time.

Now you might be thinking - if thats the case, why didnt the return value from the function go up in this example ? That's because, by default, we are fetching 100 rows at a time, so all 5 came back with a single fetch. Let's bump up the count, and see what happens.


SQL> drop table t1 purge;

Table dropped.

SQL> drop table t2 purge;

Table dropped.

SQL> create table t1 ( x int, y int );

Table created.

SQL> create table t2 as select rownum a from dual connect by level <= 250;

Table created.

SQL> select * from t2;

         A
----------
         1
         2
         3
         4
         5
...
       244
       245
       246
       247
       248
       249
       250

250 rows selected.

SQL>
SQL> create or replace
  2  function count_of_t1 return int is
  3    x int;
  4  begin
  5    select count(*) into x from t1;
  6    return x;
  7  end;
  8  /

Function created.

SQL>
SQL>
SQL> set serverout on
SQL> declare
  2    cursor cur is select a, count_of_t1 cnt from t2;
  3  begin
  4    for rec in cur loop
  5       dbms_output.put_line(rec.a||','||rec.cnt);
  6       insert into t2 values (0);
  7       insert into t1 values (1,1);
  8    end loop;
  9  end;
 10  /
1,0
2,0
3,0
4,0
5,0
...
95,0
96,0
97,0
98,0
99,0
100,0
101,100
102,100
103,100
104,100
105,100
106,100
107,100
...
193,100
194,100
195,100
196,100
197,100
198,100
199,100
200,100
201,200
202,200
203,200
204,200
205,200
206,200
207,200
208,200
...
247,200
248,200
249,200
250,200

PL/SQL procedure successfully completed.

SQL>
SQL>

You can see that the function can *see* the rows being inserted, because the "select count(*)" is consistent to when the *function* runs.

It is for this reason, you *always* need to be super careful when calling plsql functions from SQL.

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

awesome explanation about issues with cursor for loop

A reader, July 08, 2016 - 11:06 am UTC

thats a heack of an explanation nd i really appreciatr it. thanks a lot for detailing me about the inherent issues with cursor for loops. looks like i got a code ro fix now :( thanks again for your help connor

July 08, 2016 - 12:12 pm UTC

glad we could help out.

A reader, July 08, 2016 - 1:44 pm UTC

Great explanation Connor. Can adding an ORDER BY to the cursor query force the function to be evaluated for every row in the resultset at the start of the loop, before the first INSERT occurs?

July 09, 2016 - 1:55 am UTC

Yes...but that's a really really bad idea - see the next followup

With ORDER BY !

Rajeshwaran Jeyabal, July 08, 2016 - 4:35 pm UTC

drop table t1 purge;
drop table t2 purge;
create table t1 ( x int, y int );
create table t2 as select rownum a from dual connect by level <= 250;
create or replace
function count_of_t1 return int is
 x int;
begin
  select count(*) into x from t1;
  return x;
end;
/
demo@ORA11G> declare
  2   cursor cur is select a, count_of_t1 cnt from t2 order by a ;
  3   l_rows int := 0;
  4  begin
  5   for rec in cur loop
  6     dbms_output.put_line(rec.a||','||rec.cnt);
  7     insert into t2 values (0);
  8     insert into t1 values (1,1);
  9     l_rows := l_rows + 1;
 10   end loop;
 11   dbms_output.put_line('Total rows ='||l_rows);
 12  end;
 13  /
1,0
2,0
3,0
4,0
5,0
......
......
245,0
246,0
247,0
248,0
249,0
250,0
Total rows =250

PL/SQL procedure successfully completed.

demo@ORA11G>

With ORDER BY in place, the entire result set required for the cursor FOR LOOP has to be available and parked in Temp segment, before the iterations happens.

so when you loop through, the count returned by the function is not *really* from the plsql function execution, but as part of the results available in the Temp segment.

July 09, 2016 - 1:58 am UTC

This is one of those really dangerous things... because what happens when we decide to make the simplest of changes to the database:

SQL> create table t1 ( x int, y int );

Table created.

SQL> create table t2 as select rownum a from dual connect by level <= 250;

Table created.

SQL> create or replace
  2  function count_of_t1 return int is
  3   x int;
  4  begin
  5    select count(*) into x from t1;
  6    return x;
  7  end;
  8  /

Function created.

SQL>
SQL> create index T2_IX on T2 ( a ) ;

Index created.

SQL> alter table t2 modify a not null;

Table altered.

SQL>
SQL>
SQL> declare
  2   cursor cur is select a, count_of_t1 cnt from t2 order by a ;
  3   l_rows int := 0;
  4  begin
  5   for rec in cur loop
  6     dbms_output.put_line(rec.a||','||rec.cnt);
  7     insert into t2 values (0);
  8     insert into t1 values (1,1);
  9     l_rows := l_rows + 1;
 10  end loop;
 11  dbms_output.put_line('Total rows ='||l_rows);
 12  end;
 13  /
1,0
2,0
3,0
4,0
5,0
6,0
7,0
8,0
9,0
10,0
11,0
12,0
13,0
14,0
15,0
16,0
17,0
18,0
19,0
20,0
21,0
22,0
23,0
24,0
25,0
26,0
27,0
28,0
29,0
...
96,0
97,0
98,0
99,0
100,0
101,100
102,100
103,100
104,100
105,100
106,100
107,100
108,100
109,100
...
190,100
191,100
192,100
193,100
194,100
195,100
196,100
197,100
198,100
199,100
200,100
201,200
202,200
203,200
204,200
205,200
206,200
207,200
208,200
209,200
210,200
211,200
212,200
213,200
214,200
215,200
216,200
217,200
218,200
219,200
220,200
221,200
222,200
...
249,200
250,200
Total rows =250

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL>

It's like the days of when GROUP BY always did a sort, so people started omitting the ORDER BY. When GROUP BY *stopped* sorting, their results were garbage.

You never want the presence of an index (or anything else that might impact the optimizer) to be able to *change* the results you get.

TRANSACTION READ ONLY

Georg, July 10, 2016 - 1:15 pm UTC

Would
SET TRANSACTION READ ONLY
help to get a consistent view of the data?

July 11, 2016 - 12:44 am UTC

I'm not entirely sure what you mean there ? (given that the operation *does* include transaction activty)

SQL> set transaction read only;

Transaction set.

SQL> set serverout on
SQL> declare
  2    cursor cur is select a, count_of_t1 cnt from t2;
  3  begin
  4    for rec in cur loop
  5       dbms_output.put_line(rec.a||','||rec.cnt);
  6       insert into t2 values (0);
  7       insert into t1 values (1,1);
  8    end loop;
  9  end;
 10  /
1,0
declare
*
ERROR at line 1:
ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction
ORA-06512: at line 6

Taking a step back...

John Keymer, July 11, 2016 - 10:07 am UTC

...I'm surprised nobody has commented on the actual solution that is being delivered here? A cursor for loop - inserting one row from the cursor into another table each iteration within a commit after each insert? Smells like trouble to me.

July 12, 2016 - 1:29 am UTC

Very good point.

TRANSACTION READ ONLY

Georg, July 13, 2016 - 8:21 pm UTC

For

SET TRANSACTION READ ONLY

to work i think you have to put the inserts and the commit into a separate procedure with pragma AUTONOMOUS_TRANSACTION.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

awesome explanation about issues with cursor for loop

With ORDER BY !

TRANSACTION READ ONLY

Taking a step back...

TRANSACTION READ ONLY

More to Explore

PL/SQL demos

PL/SQL docs