Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question.

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

Answered by: Connor McDonald - Last updated: July 12, 2016 - 1:29 am UTC

Category: Database - Version: oracle 11g R2

Viewed 10K+ times! This question is

Whilst you are here, check out some content from the AskTom team: Will Oracle ruin the PL/SQL Challenge?

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 we 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.

and you rated our response

  (6 ratings)

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

Reviews

awesome explanation about issues with cursor for loop

July 08, 2016 - 11:06 am UTC

Reviewer: A reader from Cleveland, OH USA

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
Connor McDonald

Followup  

July 08, 2016 - 12:12 pm UTC

glad we could help out.

July 08, 2016 - 1:44 pm UTC

Reviewer: A reader

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?
Connor McDonald

Followup  

July 09, 2016 - 1:55 am UTC

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

With ORDER BY !

July 08, 2016 - 4:35 pm UTC

Reviewer: Rajeshwaran Jeyabal

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.
Connor McDonald

Followup  

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

July 10, 2016 - 1:15 pm UTC

Reviewer: Georg from Wolnzach, Germany

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

Followup  

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...

July 11, 2016 - 10:07 am UTC

Reviewer: John Keymer

...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.
Connor McDonald

Followup  

July 12, 2016 - 1:29 am UTC

Very good point.

TRANSACTION READ ONLY

July 13, 2016 - 8:21 pm UTC

Reviewer: Georg from Wolnzach, Germany

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.

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here