Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Vikas.

Asked: June 17, 2002 - 11:36 am UTC

Last updated: December 04, 2009 - 7:59 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi tom,
Q 1. I have a problem in using ref cursor for bulk bind. Following is the procedure.

create or replace procedure px is
type x is table of number(10);
type refcur is ref cursor;
vcur refcur ;
veno xt;

begin
open vcur for select empno from emp ;

fetch vcur bulk collect into veno;

close vcur;
dbms_output.put_line(veno.count);
end;
/
Procedure created.

scott@ORCL.DEL1>exec px;
14

PL/SQL procedure successfully completed.

this is fine. but when i change it to follows:
1 create or replace procedure px is
2 type x is table of number(10);
3 type refcur is ref cursor;
4 vcur refcur ;
5 veno x;
6 vs varchar2(1000) := 'select empno from emp';
7 begin
8 open vcur for vs ;
9
10 fetch vcur bulk collect into veno;
11 close vcur;
12* end;
scott@ORCL.DEL1>/

Procedure created.

scott@ORCL.DEL1>exec px;
BEGIN px; END;

*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at "SCOTT.PX", line 10
ORA-06512: at line 1

What is the cause of this error. I have gone through your book pg 711 onwards but could not get answer.



and Tom said...

It is not documented clearly but you cannot bulk collect from a weakly typed ref cursor (the one you can dynamically open). A strongly typed one:

scott@ORA817DEV.US.ORACLE.COM> create or replace procedure px
2 is
3 cursor c is select empno from emp;
4 type x is table of number(10);
5 type refcur is ref cursor return c%rowtype;
6 vcur refcur ;
7 veno x;
8 begin
9 open vcur for
10 select empno from emp;
11
12 fetch vcur bulk collect into veno;
13 close vcur;
14 end;
15 /

Procedure created.

scott@ORA817DEV.US.ORACLE.COM> show err
No errors.
scott@ORA817DEV.US.ORACLE.COM>
scott@ORA817DEV.US.ORACLE.COM>
scott@ORA817DEV.US.ORACLE.COM> exec px

PL/SQL procedure successfully completed.

will work but not a dynamically opend, weakly typed ref cursor.


This will change for you in version 9i however. In Oracle9i and up:

scott@ORA9I.WORLD> create or replace procedure px is
2 type x is table of number(10);
3 type refcur is ref cursor;
4 vcur refcur ;
5 veno x;
6 vs varchar2(1000) := 'select empno from emp';
7 begin
8 open vcur for vs ;
9
10 fetch vcur bulk collect into veno;
11 close vcur;
12 dbms_output.put_line( veno.count );
13 end;
14 /

Procedure created.

scott@ORA9I.WORLD> show err
No errors.
scott@ORA9I.WORLD>
scott@ORA9I.WORLD> exec px
14

Rating

  (9 ratings)

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

Comments

pls explain Internal Cause

Vikas Sharma, June 18, 2002 - 11:36 am UTC

hi tom,
thanks for your answer. can you please tell what happens internally.why it does not work with weak ref cursors. please elaborate more.


Tom Kyte
June 18, 2002 - 12:10 pm UTC

it just wasn't supported until Oracle9i release 1 and up. It just "didn't work" until then.

do we have any performance gain in strongly typed ref cursor

john, July 11, 2002 - 2:27 pm UTC

tom,

is there performance gain using strongly typed ref cursor over weekly typed or both are one and same.



Tom Kyte
July 12, 2002 - 7:53 am UTC

using the trivial test harness:
http://asktom.oracle.com/~tkyte/runstats.html

anyone could run this (hint hint hint)

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table emp;

Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table emp as select * from scott.emp;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2      type rc1 is ref cursor;
  3      type rc2 is ref cursor return emp%rowtype;
  4  
  5      l_cursor1 rc1;
  6      l_cursor2 rc2;
  7  
  8      l_rec   emp%rowtype;
  9  
 10      l_start number;
 11  begin
 12      insert into run_stats select 'before', stats.* from stats;
 13  
 14      l_start := dbms_utility.get_time;
 15      for i in 1 .. 1000
 16      loop
 17          open l_cursor1 for select * from emp;
 18          loop
 19              fetch l_cursor1 into l_rec;
 20              exit when l_cursor1%notfound;
 21          end loop;
 22          close l_cursor1;
 23      end loop;
 24      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
 25  
 26  
 27      insert into run_stats select 'after 1', stats.* from stats;
 28      l_start := dbms_utility.get_time;
 29      for i in 1 .. 1000
 30      loop
 31          open l_cursor2 for select * from emp;
 32          loop
 33              fetch l_cursor2 into l_rec;
 34              exit when l_cursor2%notfound;
 35          end loop;
 36          close l_cursor2;
 37      end loop;
 38      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
 39  
 40      insert into run_stats select 'after 2', stats.* from stats;
 41  end;
 42  /
193 hsecs
186 hsecs

PL/SQL procedure successfully completed.

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
------------------------------ ---------- ---------- ----------
LATCH.active checkpoint queue           0          1          1
latch

LATCH.done queue latch                  0          1          1
LATCH.ksfv messages                     2          3          1
LATCH.ncodef allocation latch           1          0         -1
LATCH.undo global data               4001       4000         -1
STAT...consistent gets              18011      18012          1
STAT...enqueue releases                 3          2         -1
STAT...cursor authentications           2          1         -1
STAT...parse count (hard)               2          1         -1
STAT...physical reads                   1          0         -1
STAT...messages sent                    0          1          1
STAT...calls to get snapshot s       5006       5005         -1
cn: kcmgss

LATCH.transaction branch alloc          1          0         -1
ation

LATCH.session switching                 1          0         -1
LATCH.job_queue_processes para          1          0         -1
meter latch

STAT...enqueue requests                 4          2         -2
STAT...free buffer requested            4          2         -2
LATCH.cache buffers lru chain           3          6          3
STAT...redo entries                    16         13         -3
LATCH.messages                          4          7          3
LATCH.checkpoint queue latch            6         10          4
STAT...parse time cpu                   4          8          4
STAT...db block changes                27         22         -5
LATCH.row cache objects                76         70         -6
STAT...session logical reads        30035      30029         -6
STAT...db block gets                12024      12017         -7
LATCH.redo allocation                  14         25         11
STAT...recursive cpu usage            140        124        -16
LATCH.shared pool                    2151       2127        -24
LATCH.library cache                 10325      10292        -33
LATCH.cache buffers chains          60095      60132         37
STAT...redo size                    21440      21392        -48

32 rows selected.


I would say the differences are neglible AT BEST. 

strongly typed ref cursor usage

Muhammad Riaz Shahid, January 26, 2008 - 2:52 pm UTC

Tom,
Could you please tell us the use of strongly typed ref cursors? For weakly typed ref cursors, it is clear that whenever we want to build query dynamically we shall use them. But about strongly typed ref cursors I am not so much sure as when to use them.

Thanks in Advance
Tom Kyte
January 29, 2008 - 2:20 am UTC

when you know at compile time what the structure of the query looks like - if you have a function that always expects a ref cursor that selects three columns - a number, a date and a string - in that order - you might use a strongly typed ref cursor in order to enforce that

Manipulating the ref cursor

Warrick, October 02, 2009 - 1:28 am UTC

Is it possible then to manipulate the contents of a ref cursor (v10.2) prior to passing it back to the calling application? i.e. can I call the ref cursor in a package, inspect it's content as with a normal cursor, and then modify one of the cursor's columns prior to returning it to the calling application?

It seems that although the ref cursor is particularly useful for bulk collects, it's structure prevents further manipulation within the calling procedure.
Tom Kyte
October 07, 2009 - 3:01 pm UTC

once fetched - it is fetched. If you fetch from it, and then return it, the person that gets it will NOT fetch what you saw.

Sounds like you want a pipelined function. The invoker would select from your routine. Your routine would open the cursor, fetch the data, do something about it, and then pipe it to the invoker

Any idea?

rama, November 25, 2009 - 5:45 am UTC

Hi Tom,
I have a problem while opening a ref cursor for a sql select statement.

Open refc for dql1||dsql2 where dsql1 and dsql2 are of clob datatypes.

Again dsql1 is dsel||dfrm||dwhere
dsel is of varchar2(3000) data type
dfrom is of varchar2(200)
dwhere is of long data type

The same is the case for dsql2

Based on the input parameters, checks are done and appropriate tables are referred and join
conditions arrived at.

When I do an open ref cursor,
I am getting an exception as PLS-00382 :expression is of wrong type

What could be the reason behind this?

Tom Kyte
November 25, 2009 - 3:12 pm UTC

clob types are not supported with native dynamic sql until version 11g

are you on 11g?

http://docs.oracle.com/docs/cd/B28359_01/appdev.111/b28370/whatsnew.htm#CHDGIGFJ


therefore, anything that exceeds 32k will fail - and you have the ability to exceed 32k with what you show.


You would have to use dbms_sql and the parse interface that takes an array of smaller strings to break the 32k barrier in pre-11g.

but since you give no example, no version, no context - that is all anyone could say.



On another note, It looks like someone has once again invented the "we'll store SQL in a table outside of the application and do everything by dynamic magic stuff" again... That is the only reason I could see for your current approach. sigh.

Any idea?

rama, November 25, 2009 - 5:46 am UTC

Hi Tom,
I have a problem while opening a ref cursor for a sql select statement.

Open refc for dql1||dsql2 where dsql1 and dsql2 are of clob datatypes.

Again dsql1 is dsel||dfrm||dwhere
dsel is of varchar2(3000) data type
dfrom is of varchar2(200)
dwhere is of long data type

The same is the case for dsql2

Based on the input parameters, checks are done and appropriate tables are referred and join
conditions arrived at.

When I do an open ref cursor,
I am getting an exception as PLS-00382 :expression is of wrong type

What could be the reason behind this?

OK

A reader, November 30, 2009 - 9:50 am UTC

Thanks Tom.
we use Oracle 9i.
we decided to use a temp. table for that lengthy select
statement instead of using ** OPEN refc for select **.
But I have a problem here with "insert into select "
when I use execute immediate it still will exceed statement length of 32K.
How can I convert this into a static SQL instead of going for a dynamic sql. I would like to convert the dsql1||dsql2
into a normal one so that I can use

insert into tmp_tab
select * from .. using bv1

Could you please suggest a valid approach on this?
Thanks for your time.
Tom Kyte
December 01, 2009 - 3:13 am UTC

... we decided to use a temp. table for that lengthy select
statement instead of using ** OPEN refc for select **.
...

why?

... when I use execute immediate it still will exceed statement length of 32K.
...

huh? where did that come from

... How can I convert this into a static SQL instead of going for a dynamic sql. I ...

to do static sql, you sort of need to know the sql AT COMPILE TIME - do you?



short of that, re-read my previous response to you, it actually contains everything you need to know in order to parse dynamically a sql statement exceeding 32k in any release of Oracle.

OK

rama, December 01, 2009 - 7:01 am UTC

Hi Tom,
Sorry for that blunder.

Actually the queries lengths are given as below

Query length of ME Benefits query:15494
Query Length of APAC Benefits query:16661
Query length of US Benefits query:27273
Query length of UK Benefits query:14455

I need to execute the below insert using dbms_sql in Oracle 9i (as you pointed to do piece wise execution).

'insert into bft_temp_tab select * from '|| sql_me||sql_apac||sql_us||sql_uk;

Any sample examples you have for doing this?
It would be useful if you can point any links with in this site.

Thanks.


OK

rama, December 02, 2009 - 5:21 am UTC

Hi Tom,
I tried this one but I am getting ORA-6502 - PL/SQL NUMERIC OR VALUE ERROR.


-- sqlstmt := 'insert into bft_temp_tab select * from ('|| SQL_ME||SQL_APAC||SQL_US||SQL_UK||SQL_ORDER_BY ||')';



I am converting the above sqlstmt into a dynamic sql stmt using dbms_sql. Please refer below


SQL_ME,SQL_APAC,SQL_US have a UNION appended in the statement itself.



declare
.....
begin
dsql_piece(0) := 'insert into bft_temp_tab select * from (';
dsql_piece(1) := SQL_ME;
dsql_piece(2) := SQL_APAC;
dsql_piece(3) := SQL_US;
dsql_piece(4) := SQL_UK;
dsql_piece(5) := SQL_ORDER_BY;
dsql_piece(6) := ')';

....



DBMS_SQL.PARSE(dyn_sql,dsql_piece,dsql_piece.first,dsql_piece.last,false,dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(dyn_sql,':p_maxrow',p_maxrow);
ret := DBMS_SQL.EXECUTE(dyn_sql);
DBMS_SQL.CLOSE_CURSOR(dyn_sql);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error:'||sqlerrm);
DBMS_SQL.CLOSE_CURSOR(dyn_sql);
END;

Can you please give some idea on this?


Tom Kyte
December 04, 2009 - 7:59 am UTC

you would sort of need to supply a full test case.

this is a pretty simple error message, look at the line of code that causes it, debug it - it is going to be very simple.

I cannot see how you defined anything.
I cannot see what line raises this error.
I cannot see anything, you can see it all - surely you must be able to debug a numeric or value error? Basically - in this case - it'll mean "you put too large of a string into too small of a string"


I hate your code by the way.

  EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error:'||sqlerrm);
  DBMS_SQL.CLOSE_CURSOR(dyn_sql);
  END;


that MUST be:

  EXCEPTION
  WHEN OTHERS THEN
  if dbms_sql.is_open(dyn_sql) 
  then
     DBMS_SQL.CLOSE_CURSOR(dyn_sql);
  end if;
  <b>RAISE;</b>
  END;


anything else makes you look like a newbie coder.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library