Can you please illustrate on why you would use explicit cursor ?
Paru, May 09, 2002 - 5:24 pm UTC
Can you please illustrate on why you would use explicit cursor for bulk collects. BTW, what is bulk collect? If I have millions of records that I need to select and place it into another table using pl/sql, what would you suggest for me to use - explicit cursor or implicit cursor?
TIA,
Paru
May 09, 2002 - 7:53 pm UTC
Because you usually are processing lots of rows and you don't want to fetch 100,000 rows into a set of arrays in one call -- rather you want to get say 100-500 at a time, process them, get the next 100-500 times. So you need an explicit cursor like:
open c1;
loop
fetch c1 bulk collect into array limit 100;
....
exit when c1%notfound;
end loop;
close c1;
so you can fetch N rows at a time.
As for
"If I have millions of records that I need
to select and place it into another table using pl/sql"
I would say your approach is FLAWED. You don't want to involve PLSQL at all -- you want to insert into select PERIOD.
Paru, May 10, 2002 - 11:29 am UTC
Hi Tom,
I am using insert into select, but I am putting it in a loop, cos otherwise it does not catch my dup_val_exception. In the sense once I get a dup_val_on_index, I want all the other records to be inserted and then if I hit a dup_val then I want to write to an error table.
Please tell me if this is the correct way of doing this. I am using dblink to connect 2 schemas as our clients might be doing the conversion from one schema into another on the same instance or on different instance.
Also, they might convert millions of records, so please suggest to me if the below method is correct way?
Here is my code :
begin
for x in (select ... from table_name@dblink)
loop
begin
insert into table_name
(.....) (select ... from table_name@dblink)
exception
when dup_val_on_index then
insert into error_table (table_name, date, 'found dups...');
when invalid_number then
insert into error table(table_name, date,'invalid no...');
end;
end loop;
end;
/
Explicit vs Implicit cursors
Mark, June 17, 2002 - 10:37 am UTC
Tom,
I am now a convert to the use of implicit cursors. There are books available by well-respected authors (no names) that recommend (almost insist) on using explicit cursors. Was there ever a version of Oracle where explicit cursors were better?
Anyway, I have come across a situation where I feel using explicit cursor is better.
I want to return the most recent event, hence
FUNCTION f_get_latest_evn_id
(p_req_id IN BINARY_INTEGER)
RETURN BINARY_INTEGER IS
CURSOR cur_get_events IS
SELECT evn_id
FROM events
WHERE req_id = p_req_id
ORDER BY dtm DESC,
evn_id DESC;
l_return_val BINARY_INTEGER;
BEGIN
OPEN cur_get_events;
FETCH cur_get_events INTO l_return_val;
CLOSE cur_get_events;
RETURN l_return_val;
END;
Now I know I can code it in a cursor for loop, exiting after the first record, but I feel this may be confusing to other developers as a loop implies that you want to retrieve more than one rocord (in my humble opinion).
I would be grateful for your views on this.
Mark
June 17, 2002 - 11:12 am UTC
Like most things, there is a nugget of truth to be found in ancient history.
It was never so in the database itself, but in really old versions of forms in client server -- a select into was "open, fetch fetch close". It was not optimized. This is really really old though (and never was true in a stored procedure in the database).
I would write your code as:
select evn_id into l_evn_id
from (SELECT evn_id
FROM events
WHERE req_id = p_req_id
ORDER BY dtm DESC,
evn_id DESC)
where rownum = 1;
That can be much much more efficient then your method. Consider:
declare
l_object_name varchar2(30);
begin
select object_name into l_object_name
from ( select object_name
from big_table
order by timestamp desc )
where rownum = 1;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 0 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 216
********************************************************************************
SELECT OBJECT_NAME
FROM
(SELECT OBJECT_NAME FROM BIG_TABLE ORDER BY TIMESTAMP DESC ) WHERE ROWNUM
= 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 5.43 5.46 14848 14968 27 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 5.43 5.46 14848 14968 27 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 216 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 COUNT STOPKEY
1 VIEW
1 SORT ORDER BY STOPKEY
1101008 TABLE ACCESS FULL BIG_TABLE
declare
l_object_name varchar2(30);
cursor c is select object_name from big_table order by timestamp desc;
begin
open c;
fetch c into l_object_name;
close c;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.01 0 0 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 216
********************************************************************************
SELECT OBJECT_NAME
FROM
BIG_TABLE ORDER BY TIMESTAMP DESC
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.01 0 0 0 0
Fetch 1 19.68 164.57 25054 14968 129 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 19.68 164.58 25054 14968 129 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 216 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY
1101008 TABLE ACCESS FULL BIG_TABLE
but of course it depends on indexes and such (but the rownum trick will be AT LEAST as good, if not significantly better then). The rownum optimization is very nice. Here the database KNOWS your intention (i want 1 row). It can do the work better. In the second case -- it has to assume you want ALL OF the rows. Hence, it does a ton more work.
Re: Explicit vs Implicit cursors
Mark, June 17, 2002 - 11:23 am UTC
Very, very informative! Yet again I have learned something new.
Many thanks,
Mark
oracle high performance sql tuning
A reader, August 26, 2002 - 7:17 am UTC
Hi
I have this book called Oracle SQL hugh performance tuning by Guy Harrison.
There was this comparison between explicit and implicit cursors. He stated that explicit cursor is faster than implicit because implicit cursors have to make an extra fetch to test for row existence.
He actually suggested that a
select a
into l_a
from table
where condition
is probably slower than
declare cursor c1 select * from a where condition
begin
open c1
fetch ....
close c1
end
I am amazed! Is this true! It seems that he tested it because there was this comparison chart in seconds!
August 26, 2002 - 8:53 am UTC
In every single one of my tests (and it is trivial to setup) the select into is
o faster
o easier
o does not do the theoritical double round trip everyone proclaims.
If he tested it -- one would hope the test would be there for us to replicate?
It is trivial to test this theory (and see that IMPLICIT cursors rock, they rule, they are the right way):
ops$tkyte@ORA817DEV.US.ORACLE.COM> set timing on
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_dummy dual.dummy%type;
3 begin
4 for i in 1 .. 10000
5 loop
6 select dummy into l_dummy from dual this_is_implicit;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
<b>Elapsed: 00:00:01.77</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_dummy dual.dummy%type;
3 cursor c is select dummy from dual this_is_explicit;
4 begin
5 for i in 1 .. 10000
6 loop
7 open c;
8 fetch c into l_dummy;
9 if (c%notfound)
10 then
11 raise no_data_found;
12 end if;
13 close c;
14 end loop;
15 end;
16 /
PL/SQL procedure successfully completed.
<b>Elapsed: 00:00:02.29</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_dummy dual.dummy%type;
3 cursor c is select dummy from dual this_is_explicit_done_right;
4 begin
5 for i in 1 .. 10000
6 loop
7 open c;
8 fetch c into l_dummy;
9 if (c%notfound)
10 then
11 raise no_data_found;
12 end if;
13 fetch c into l_dummy;
14 if (c%found)
15 then
16 raise too_many_rows;
17 end if;
18 close c;
19 end loop;
20 end;
21 /
PL/SQL procedure successfully completed.
<b>Elapsed: 00:00:02.50
I maintain that the above (or below) piece of code is what you must code in order to get the benefits of a select into -- but none the less, the minimal code in example 2 above is still provably slower then select into..</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_dummy dbms_sql.varchar2s;
3 cursor c is select dummy from dual this_is_explicit_done_right2;
4 begin
5 for i in 1 .. 10000
6 loop
7 open c;
8 fetch c bulk collect into l_dummy limit 2;
9 close c;
10 if ( l_dummy.count = 0 )
11 then
12 raise no_data_found;
13 end if;
14 if ( l_dummy.count > 1 )
15 then
16 raise too_many_rows;
17 end if;
18 end loop;
19 end;
20 /
PL/SQL procedure successfully completed.
<b>Elapsed: 00:00:02.48
So, as you can see, select into is "faster"</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set sql_trace=true;
Session altered.
Elapsed: 00:00:00.03
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_dummy dual.dummy%type;
3 begin
4 select dummy into l_dummy from dual this_shows_no_double_trip;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
ops$tkyte@ORA817DEV.US.ORACLE.COM>
<b>
and the TKPROF shows us:
</b>
SELECT DUMMY
FROM
DUAL THIS_SHOWS_NO_DOUBLE_TRIP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 4 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 1 4 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 554 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL
<b>
If there was a double trip, the Fetch count would be 2. We can see that with this:
</b>
declare
l_dummy dual.dummy%type;
cursor c is select dummy from dual this_is_explicit_done_right;
begin
open c;
fetch c into l_dummy;
if (c%notfound)
then
raise no_data_found;
end if;
fetch c into l_dummy;
if (c%found)
then
raise too_many_rows;
end if;
close c;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 0 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 554
********************************************************************************
SELECT DUMMY
FROM
DUAL THIS_IS_EXPLICIT_DONE_RIGHT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 1 4 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 1 4 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 554 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL
<b>
Note how the Fetch = 2, rows = 1. If the select into did TWO fetches, it would have 2 in the Fetch column. It doesn't, it has 1 showing that an array fetch was used like this does:
</b>
declare
l_dummy dbms_sql.varchar2s;
cursor c is select dummy from dual this_is_explicit_done_right2;
begin
open c;
fetch c bulk collect into l_dummy limit 2;
close c;
if ( l_dummy.count = 0 )
then
raise no_data_found;
end if;
if ( l_dummy.count > 1 )
then
raise too_many_rows;
end if;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.01 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.02 0.01 0 0 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 554
********************************************************************************
SELECT DUMMY
FROM
DUAL THIS_IS_EXPLICIT_DONE_RIGHT2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 4 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 0 1 4 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 554 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL
<b>
See the 1 in the fetch column. The select into is (has been for many many many many years, at least since version 7.0 in 1993) optimized to array fetch.
Now, just to beat a dead horse, lets put it thru my simple test harness
http://asktom.oracle.com/~tkyte/runstats.html </b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 cursor c is select * from dual;
3 l_dummy dual.dummy%type;
4 l_start number;
5 l_run1 number;
6 l_run2 number;
7 begin
8 insert into run_stats select 'before', stats.* from stats;
9
10 l_start := dbms_utility.get_time;
11 for i in 1 .. 10000
12 loop
13 select dummy into l_dummy from dual;
14 end loop;
15 l_run1 := (dbms_utility.get_time-l_start);
16 dbms_output.put_line( l_run1 || ' hsecs' );
17
18
19 insert into run_stats select 'after 1', stats.* from stats;
20 l_start := dbms_utility.get_time;
21 for i in 1 .. 10000
22 loop
23 open c;
24 fetch c into l_dummy;
25 close c;
26 end loop;
27 l_run2 := (dbms_utility.get_time-l_start);
28 dbms_output.put_line( l_run2 || ' hsecs' );
29 dbms_output.put_line( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' );
30
31 insert into run_stats select 'after 2', stats.* from stats;
32 end;
33 /
179 hsecs
244 hsecs
run 1 ran in 73.36% of the time
PL/SQL procedure successfully completed.
<b>faster in single user mode..</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2,
2 ( (c.value-b.value)-(b.value-a.value)) diff
3 from run_stats a, run_stats b, run_stats c
4 where a.name = b.name
5 and b.name = c.name
6 and a.runid = 'before'
7 and b.runid = 'after 1'
8 and c.runid = 'after 2'
9 and (c.value-a.value) > 0
10 and (c.value-b.value) <> (b.value-a.value)
11 order by abs( (c.value-b.value)-(b.value-a.value))
12 /
NAME RUN1 RUN2 DIFF
------------------------------ ---------- ---------- ----------
...
STAT...recursive calls 10031 30031 20000
...
26 rows selected.
<b>
and cheaper too -- lots less recursive calls (context switches)...
I'll stick with select into thanks very much!</b>
Reader
A reader, August 26, 2002 - 10:32 pm UTC
Tom,
Could you explain to us why there is 1 row in "execute"
(not fetch )and there were no blocks read( disk, query, current)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 0 0 1
Thsnks
August 27, 2002 - 8:19 am UTC
ops$tkyte@ORA817DEV.US.ORACLE.COM> @test
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set sql_trace=true;
Session altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 cursor c is select * from Dual;
3 begin
4 open c;
5 end;
6 /
PL/SQL procedure successfully completed.
SELECT *
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 554 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL DUAL
You parsed it and opened it but did not actually fetch from it. Or, you did a DML operation that affected nothing:
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set sql_trace=true;
Session altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM> update dual set dummy = 'x' where 1=0;
0 rows updated.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
update dual set dummy = 'x'
where
1=0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.01 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 554
Rows Row Source Operation
------- ---------------------------------------------------
1 UPDATE DUAL
1 FILTER
0 TABLE ACCESS FULL DUAL
Yet to be convinced
Phil Winfield, November 17, 2002 - 5:37 pm UTC
Hi Tom
You may be right but you're not comparing like with like. In your implicit example all that is done is the implicit bit. In the explicit example you have an if clause. I took this out and got the following results on my system.
1 declare
2 l_dummy dual.dummy%type;
3 begin
4 for i in 1 .. 10000
5 loop
6 select dummy into l_dummy from dual this_is_implicit;
7 end loop;
8* end;
SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.22
1 declare l_dummy dual.dummy%type;
2 cursor c is select dummy from dual this_is_explicit;
3 begin
4 for i in 1 .. 10000
5 loop
6 open c;
7 fetch c into l_dummy;
8 close c;
9 end loop;
10* end;
SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.02
SQL>
So the explicit one is 20 miliseconds faster?
November 17, 2002 - 6:00 pm UTC
well, you need the if clause (but it matters not, read on). what if -- just what IF there was no data to fetch? what then? well, you have garbage in l_dummy that is what. whatever was left in l_dummy before the fetch will still be there -- if you are in a loop doing this, that would be deadly as l_dummy wouldn't even be NULL, it would be whatever was left in it from the last iteration.
If you don't have that if check - you have what I would call "a bug".
Even removing the IF, using my test harness:
http://asktom.oracle.com/~tkyte/runstats.html
I cannot reproduce your results. (i attribute your .2 second to timing in plus. You are measuring more then the operations in question - did you run in a single user system? if not other sessions would impact you and you need to run a couple dozen times to "average" that. you timed the network, you timed sqlplus drawing on your screen, etc. -- in any case -- I suggest a single block of code that you time yourself when comparing A vs B in general. I should have myself).
In my testing, every time, in all cases, explicit is slower. Forget about the facts
o that explicit is more code to type
o that explict is easier to introduce bugs (you have one in your example IMHO)
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_start number;
3 l_run1 number;
4 l_run2 number;
5
6 cursor c is select dummy from dual this_is_explicit;
7 l_dummy dual.dummy%type;
8 begin
9 insert into run_stats select 'before', stats.* from stats;
10
11 l_start := dbms_utility.get_time;
12 for i in 1 .. 10000
13 loop
14 select dummy into l_dummy from dual this_is_implicit;
15 end loop;
16 l_run1 := (dbms_utility.get_time-l_start);
17 dbms_output.put_line( l_run1 || ' hsecs' );
18
19 insert into run_stats select 'after 1', stats.* from stats;
20 l_start := dbms_utility.get_time;
21 for i in 1 .. 10000
22 loop
23 open c;
24 fetch c into l_dummy;
25 close c;
26 end loop;
27 l_run2 := (dbms_utility.get_time-l_start);
28 dbms_output.put_line( l_run2 || ' hsecs' );
29 dbms_output.put_line
30 ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' );
31
32 insert into run_stats select 'after 2', stats.* from stats;
33 end;
34 /
185 hsecs
241 hsecs<b>
run 1 ran in 76.76% of the time
</b>
PL/SQL procedure successfully completed.
<b>that was 817 on a 2cpu sparc running solaris (similar results on linux and even windows)</b>
220 hsecs
289 hsecs
run 1 ran in 76.12% of the time
PL/SQL procedure successfully completed.
138 hsecs
179 hsecs
run 1 ran in 77.09% of the time
PL/SQL procedure successfully completed.
198 hsecs
249 hsecs
run 1 ran in 79.52% of the time
PL/SQL procedure successfully completed.
125 hsecs
170 hsecs
run 1 ran in 73.53% of the time
PL/SQL procedure successfully completed.
<b>Different releases -- same box -- from versions 7.3 8.0 9.0 9.1. All pretty consistent -- times are all different (thats the nature of the beast) but the ratios of IMPLICT to EXPLICT is pretty consistent in the 70-80% range
Try that approach to measuring things and see if you can become convinced</b>
Or, just keep on typing in extra code -- but don't forget that IF statement! (and I think its a bug not to look for TOO_MANY_ROWS as well -- select into generally means -- there will be at least one and at most one row. If you just want the first "random row" we happen to return -- use "and rownum = 1" on the select into.
FYI, Feuerstein has finally changed his tune--a little.
Jer Smith, November 17, 2002 - 6:36 pm UTC
In the most recent (3rd) edition of PL/SQL programming, Feuerstein has finally backed down on his statements that explicit cursors give any performance advantage. He still 'prefers' them as a structure (one may assume that he's pretty used to writing them--and saying something is 'easier' or 'more readable' is a matter of opinion to some degree--ask a Perl programmer) but has 'mellowed' considerably in his adamancy.
Excellent thread
Rajiv, November 17, 2002 - 7:07 pm UTC
Tom, You are the Master! The way you clear things beyond any speculation is absolutely amazing.
Hats off to you!
Your Remarks on Closing Cursors...
Robert, November 19, 2002 - 11:17 am UTC
Tom, I have wanted to ask you this for a while....
I have seen several times your remarks about closing cursors...something like...
"don't be so quick to close them...me, I never close them...." etc.
What exactly do you mean ?
Do you mean that if you create Explicit Cursor(s),
you keep them hanging around just because you might need to RE-OPEN them ?
I appreciate if you can demo what you mean and how that's
better.
Thanks
November 19, 2002 - 5:24 pm UTC
well, if you code in PLSQL and use STATIC sql -- you are done. thats the example. PLSQL caches those cursors for you!
If you are programming in java -- i'm saying DO NOT
o prepare
o bind
o execute
o close
all of the time -- instead
o prepare in your CONSTRUCTOR
o bind + execute in your methods over and over
If you ever anticipate that statement being executed more then once or twice (eg: you have a method to insert a row into a table and this method is called 500 times in your app -- DO NOT close the prepared statement between calls)
same for VB, C/C++ etc.
Implicit Cursors in Procedures
Brian, June 19, 2003 - 7:33 pm UTC
why does code like:
begin
for x in (select . . .)
loop
process;
end loop;
end;
work fine
but once you go and add
create or replace procedure myproc
as
begin
for x in (select . . .)
loop
process;
end loop;
end;
You start to receive compile errors.
Thanks.
June 20, 2003 - 4:39 pm UTC
you know what -- my car won't start, can you tell me why?
No error codes.
No example.
No error messages.
No clues other then "compile errors"
I'll guess (bet I'm right too :)
read:
</code>
http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>
Jeez
Brian, June 20, 2003 - 7:23 pm UTC
Thanks.
I read your invoker/definer chapter a bunch after reviewing the code over and over again thinking I had coded something wrong. I didn't know how to ask the question.
The error code didn't make any sense since the PL/SQL worked fine when not a procedure. I didn't know how providing it would help.
I've learned a bunch here.
I've only been working with Oracle for a month. I do not have a programming background (I'm a financial analyst). I have never had an Oracle class, and I have had to teach myself everything to migrate away from using ACCESS to query Oracle tables.
I'm doing the best I can. I've moved more than half of my processes and calculations from ACCESS and EXCEL into Oracle.
And I have to get the job done today because the developers won't have their apps done for another year, and I have deadlines.
I apologize for not knowing how to ask the question.
Thanks Again.
June 21, 2003 - 10:03 am UTC
It is that things like
"my database is going slow, why"
"i got an error from my code, why"
"i get some ora error message when connecting, why"
"i did <lengthly discussion of something> and it didn't work, why"
are impossible questions to answer -- always need the error message, always need the error code. There are literally hundreds of such messages/codes -- without them, I can only I guess -- in your case I had to guess it was either pls-201, ora-942 or ora-1031 (still don't know which one), but having that information would make me say "ah hah, I can tell you exactly what went wrong".
Imagine if you will that you issue:
SQL> create or replace procedure p as begin null; end;
2> /
ORA-XXXXX something went wrong, fix it and try again
and that was what you got, and even better:
SQL> show errors procedure P
Error on some line, you did something wrong, fix it and it will work ;)
scott@ORA920> create or replace procedure p
2 as
3 begin
4 for x in ( select * from ops$tkyte.t )
5 loop
6 null;
7 end loop;
8 end;
9 /
Warning: Procedure created with compilation errors.
scott@ORA920>
scott@ORA920> show errors procedure p
Errors for PROCEDURE P:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/11 PL/SQL: SQL Statement ignored
4/35 PL/SQL: ORA-00942: table or view does not exist
<b>perhaps, I should have mentioned the "show errors command", I forgot to remember that you might not be aware of it</b>
It is very useful when trying to diagnose what is going wrong where in the code -- and would be useful to supply to anyone that would be looking at the problem. Sorry about that.
execellent!!!!
A reader, July 26, 2005 - 4:44 pm UTC
Tom,
I agreed with you that implicit cursor are much faster
than explicit but what will you suggest if we need to manipulate more than one row? should we use explicit??
July 26, 2005 - 5:36 pm UTC
begin
for x in ( select * from t )
loop
is what I like, in 9iR2 and before if I were to process HUNDREDS of rows that way, I might use an explicit cursor and BULK collect (HUNDREDS of rows or more).
in 10gr1 and up, just:
for x in (select * from t) loop
Newbie
A reader, August 29, 2005 - 11:46 am UTC
Tom,
HOw can I turn the proc below into an implicit cursor. Thank you very much.
CREATE OR REPLACE procedure TEST
is
CURSOR testcur
IS
SELECT Y.STUDENT_NAME
FROM student_hist Y
WHERE Y.CLASS = 'Y'
ORDER BY Y.STUDENT_NAME;
xteacher SIZE%type;
LOOP_EXCEPTION EXCEPTION;
xCount number;
ALERT_FILE CONSTANT File_Pkg.filename_TYPE := 'TEST_PROC';
begin
OPEN testCUR;
LOOP
FETCH testCUR INTO xteacher;
SELECT COUNT(1) INTO xCount
FROM STUDENT_HIST I
WHERE I.L1 = xteacher;
INSERT INTO test_tbl VALUES(Sysdate,xteacher,xCount);
END LOOP;
commit;
close testCUR;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
file_pkg.log_alert(ALERT_FILE, TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss') ||' ERROR '||SQLCODE||'-'||SQLERRM);
end TEST;
August 29, 2005 - 1:56 pm UTC
there are quite a few, as is many, things wrong with this procedure.
you do understand that is an infinite LOOP?
I don't get the logic at all, you put into test_tbl a count of all of the teacher records for a teacher over and over and over again?????????
please look at this logic a bit before I consider how to make it tighter.
for x in ( select ... )
loop
....
end loop
is how to make it "implicit"
Pl make it work ..
Reader, October 12, 2006 - 8:01 am UTC
declare
v_deptno varchar2(10);
begin
for x in (select deptno from dept)
loop
v_deptno:=x;
dbms_output.put_line(v_deptno);
end loop;
end ;
I wish to display deptno in the loop.
Thanks
October 12, 2006 - 8:30 am UTC
homework?
for x in ( select deptno from dept )
loop
dbms_output.put_line( x.deptno );
end loop;