Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: July 01, 2002 - 2:38 pm UTC

Last updated: November 15, 2011 - 8:57 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Tom,

I 've written a stored procedure which uses ref cursor. Following is how I 've written it :-

create or replace package pkg_dept
AS
type rc_dept is ref cursor;
end;
/

create or replace
procedure sp_dept( t_deptno IN NUMBER,
t_designation IN NUMBER,
dept_cur in out pkg_dept.rc_dept )
is
begin

OPEN dept_cur for
select ename,salary,join_date
from emp
where deptno=t_deptno
and designation=t_designation;

end;
/

From Sql*plus I can call the stored procedure and get results using :-var c refcursor
execute sp_dept(100,'CLERK',:c);
print c

1. But how do I do it using a small pl/sql block ie. how do I put
var c refcursor (Step 1)
execute sp_dept(100,'CLERK',:c); (Step 2)
print c (Step 3)
the above 3 steps into a pl/sql block and get the output, I tried a couple of ways but it didn't work.

2. Is the above method of opening a ref cursor and executing the results a good method ? In my application I will be calling the stored procedure from a jsp page, I ran the jsp page and it works fine in retrieving the results, But I am wondering if the method I 've adopted in opening the cursor a good one,because I dont want to end up into any problems in the future. Also could you mention some EXCEPTIONS that I could use in the stored procedure.

3. I would like to know if I am using bind variables in the stored procedure. I am rewriting a sql query in to stored procedure for it to use bind variables to improve performance. So I wanted to confirm that this procedure uses bind variables.


Thanks in Advance,
TS




and Tom said...

1) you would have to explicitly fetch and print the results:

declare
c pkg_dept.rc_dept;
l_ename emp.ename%type;
l_sal emp.salary%type;
l_join_date emp.join_date%type;
begin
sp_dept( 100, 'CLERK', c );
loop
fetch c into l_ename, l_sal, l_join_date;
exit when c%notfound;
....
end loop;
close c;


2) I firmly believe the best java programs are those that have ZERO "selects/inserts/updates/deletes" in them. Hence, using ref cursors is the way to go. Lets you tune without bothering those java programmers.

You use the exception you need to -- there is no "list" of ones you should use. You use what you need?

3) yes you are. T_DEPTNO and T_DESIGNATION are bind variables. Now, just make sure the CALLER (java programmer) uses bind variables when calling your procedure!!!!



Rating

  (137 ratings)

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

Comments

One More Clarification on Ref Cursor

Saikumar, July 15, 2002 - 4:21 pm UTC

Tom,

Thanks for your help on giving examples to clarify the usage of Ref Cursor. I 've one more doubt on if I could use EXCEPTION in the pl/sql block I had mentioned. Say for eg. I want to use "EXCEPTION NO DATA FOUND" how do i use it in the procedure that I had mentioned in the question.

Thanks,
TS

Tom Kyte
July 15, 2002 - 8:43 pm UTC

You cannot with a ref cursor as NO_DATA_FOUND *only* applies to

select ... INTO ....

queries and not

open
fetch
close

type of queries.

Any other way of writing the stored procedure with ref cursor

A reader, August 07, 2002 - 4:16 pm UTC

Tom,

Is there any other way of writing the stored procedure below :-

create or replace package pkg_dept
AS
type rc_dept is ref cursor;
end;
/

create or replace
procedure sp_dept( t_deptno IN NUMBER,
t_designation IN NUMBER,
dept_cur in out pkg_dept.rc_dept )
is
begin

OPEN dept_cur for
select ename,salary,join_date
from emp
where deptno=t_deptno
and designation=t_designation;

end;

I would like to use some relevant exceptions and also like to open and close the ref cursor. This procedure will be called from a java program. I am more confused with using the exceptions as I feel nothing could fit in to the above procedure. Please help.

Thanks,
TS

Tom Kyte
August 07, 2002 - 6:34 pm UTC

Well first -- use packages for everything in real code -- it breaks the dependency chain and reduces the amount of recompilation that'll have to take place when you update code.

With the ref cursor -- that is it. That is the way it is done. the CALLER (java in this case) is responsible for the "close" call -- you don't do that in plsql when the client fetches from the cursor.

I don't see any need for any exception handling here -- there is nothing you could "catch" and handle -- the caller would want to handle any and all errors that occur (by reporting them to the end user most likely)



Adding a Status to the procedure that has ref cursor

TS, August 08, 2002 - 5:47 pm UTC

Tom,

Sorry for going over this again and again. I 've included a status variable , where if there is no rows retrieved by the ref cursor it should return the status 0 and if there are rows retrieved the procedure should return a status 1.
I 've modified the procedure as follows :-

create or replace
procedure sp_dept( t_deptno IN NUMBER,
t_designation IN NUMBER,
status out NUMBER,
dept_cur in out pkg_dept.rc_dept )
is
begin

status:=1;

OPEN dept_cur for
select ename,salary,join_date
from emp
where deptno=t_deptno
and designation=t_designation;

IF dept_cur%NOTFOUND
THEN
status:=0;
ELSE
status:=1;
END IF;

end;

when I execute the above procedure, it doesn't seem to pickup the correct status, irrespective of whether rows exist or not , it shows status=1. Could you pls help.

Thanks,
TS



Tom Kyte
August 09, 2002 - 8:12 am UTC

An OPEN fetches NO DATA WHATSOEVER.

A FETCH fetches data.

There is absolutely no way upon OPEN to decide that "hey there is no data". You must actually FETCH a row. Only the client that recieves this ref cursor and FETCHES from it can discover "hey, there is no data.


dept_cur%notfound will not be set until AFTER YOU FETCH.

You cannot do what you are trying to do. the guy who gets the cursor will have to fetch and then say "there is data" or "there is no data".

Point made very clearly.

Robert, August 09, 2002 - 8:46 am UTC


process ref cursor

A reader, December 16, 2002 - 3:26 pm UTC

Hi

I have a function which retrieves a small table and store the results into a ref cursor and that ref cursor is returned

something like

CREATE OR REPLACE PACKAGE TYPES
AS
TYPE RC IS REF CURSOR;
END;
/

create or replace function x(y varchar2)
return types.rc
...
...
open c1 for select * fdrom emp
return c1;
.....

If I have a pl/sql anonymous block similar to

declare
c1 types.rc;
begin
c1:=x('EMP');
....
....
end;
/

How can I process c1 row by row in the procedure?

Tom Kyte
December 16, 2002 - 3:33 pm UTC

Look at the answer above.

FETCH

sorry just noticed after I post

A reader, December 16, 2002 - 3:37 pm UTC

Hi

I saw the answer after I posted really sorry... but I have a serious problem, I have to declare the record type. The problem is I pass the table_name dynamiclly so in the run time I dont know what's the record type. For example if I passed EMP as table_name record type should be

emp%rowtype

if I pass DEPT then it should be

dept%rowtype

I think it's impossible to assign record type in the run time, is there a workaround?

Tom Kyte
December 17, 2002 - 7:25 am UTC

You will not be using REF CURSORS then.

With a ref cursor you by definition have a fixed number of inputs and outputs - everything is setup at COMPILE time.

You can use DBMS_SQL. search this site for

dump_csv

to see a very generic example.

forogot to say recorde type is going to be declared in anonymous pl/sql block

A reader, December 16, 2002 - 3:39 pm UTC

Hi

I forgot to mention that record type has to be in PL/SQL block so I can do

c1:=x('EMP');
loop
fetch c1 into record_type

etc.....

Can you put a ref cursor in another cursor or in "if ... end if"?

John, January 17, 2003 - 4:50 pm UTC

I have learnt how to return a result set using ref cursor from this forum, which worked great for me! But can you put a ref cursor in another cursor or in "if ... end if"?

Something like this:

Declare
v_user_setting cursor type
begin
if ....... then
open v_user_setting for select ...
end if;
return v_user_setting;
end if;


If I put OPEN cursor statement in a "if ... end if" statement, it worked fine when it returns some rows. But I got error if it return 0 row. Here is the error message when I tested from SQL*Plus:

ERROR:
ORA-24338: statement handle not executed

Is this expected or did I do anything wrong? Any work around?


Tom Kyte
January 17, 2003 - 6:30 pm UTC

umm, the cursor wasn't opened so yes there will be an error.

what would you "like" it to do?? It is like accessing "NULL" -- there is nothing there.

You could always

if ... then
open v_user_setting for select ...
else
open v_user_setting for select * from dual where 1=0;
end if;



Now, just make sure the java programmers use bind variables

Rob, January 24, 2003 - 1:31 pm UTC

Tom,

You mentioned that the Java programmers need to use bind variables. Would you please elaborate and give an example.

Tom Kyte
January 24, 2003 - 2:27 pm UTC

static void doPreparedStatement (Connection con, int count) throws Exception
{
long start = new Date().getTime();
// p ("doing prepared statement start time : " + start);
PreparedStatement ps = con.prepareStatement("insert into testxxxperf " +
"(id, code, descr, insert_user, insert_date)"
+ " values (?,?,?, user, sysdate)");

for (int i = 0; i < count; i++)
{
ps.setInt(1,i);
ps.setString(2,"PS - code" + i);
ps.setString(3,"PS - desc" + i);
ps.executeUpdate();
}


is an example.

ref curser in "if" statement

John, February 04, 2003 - 12:06 pm UTC

Regarding the ref cursor in "if" statement, what I was trying to do is to open ref cursor only under certain conditions. And even under that condition, there could be no record found. In most cases I can put the condition in where clause of the "open curser for select ..." statement, but there are times that it's just easier to use "if" statement.

What you are saying is even I do "open cursor for select ..." and if there is no data, the cursor is not open? But if the "open cursor for select ..." is not in a "if" statement, there was no error. This is what puzzled me.

Your suggestion is a good work around! Thanks.


Tom Kyte
February 04, 2003 - 12:59 pm UTC

it matters no if the ref cursor finds data or not. Look at my example quick:

You could always

if ... then
open v_user_setting for select ...
else
open v_user_setting for select * from dual where 1=0;
end if;


there will never be any data for the second open -- it is just that there is an OPEN cursor for the client to try and fetch from (and will immediately get the "no data found")





Thanks, Tom.

John, February 04, 2003 - 2:44 pm UTC

But what's the difference between
open v_user_setting for select ...
and
open v_user_setting for select * from dual where 1=0;

There's no data found in either case. But the first one gives error whereas the second one doesn't.


Tom Kyte
February 04, 2003 - 3:50 pm UTC

If you code this:

Declare
v_user_setting cursor type
begin
if ....... then
open v_user_setting for select ...
end if;
return v_user_setting;
end if;


you will get an error in the client if they attempt to access the cursor (it was never open)

If you code this:

Declare
v_user_setting cursor type
begin
open v_user_setting for select ...
return v_user_setting;
end if;


You should not -- i would be interested in an example of such an error if you have one.

what is the use of ref cursor ?

A reader, October 28, 2003 - 3:21 pm UTC



HI, By keeping What need in mind oracle introduced the REF cursor except passing the result set.

I just want to know what are best places to use refcursor.
1.) to pass the result set
2.) support JDBC/ result set
3.) ?
4.) ?

places in pl/sql code, where one should use it to improve code..

Thanks,

Tom Kyte
October 28, 2003 - 9:06 pm UTC

only #1 since #2 is a repeat of #1

use it ONLY when forced to -- to return a result set to a client. be that client a plsql subroutine or a java program or vb or whatever.

it returns a result set -- period.

REF cursor

A reader, October 29, 2003 - 10:21 am UTC


Thanks, Tom.

In Java, I heard that String manipulation is easy.
we still have stored package that returns the refcursor
after building the dynamic query. There are 20 to 25 ref cursors returned in various procedures in pkg.

do you think they should just build the query in java and query it directly from htere ?

please explain

Thanks.

Tom Kyte
October 29, 2003 - 11:20 am UTC



pssst -- string manipulation in plsql is easy.

there, we are even. just use plsql.

Would you use java with a sql or a store procedure to return a ref cursor

marc, October 29, 2003 - 12:29 pm UTC

Which would be your prefrence for standard practice,java with a sql statmment or a store procedure to return a ref cursor to java.

Tom Kyte
October 29, 2003 - 2:09 pm UTC



i believe the best java programs do not have the words "select", "insert", "update" or "delete" them -- only "begin .... end;"

so, ref cursor from a stored procedure.

Function returns Ref Cursor question

Justin, December 05, 2003 - 11:40 am UTC

owner@DEV> CREATE TABLE loser AS SELECT rownum id FROM user_tables WHERE rownum < 11;

Table created.

owner@DEV>
owner@DEV>
owner@DEV> CREATE OR REPLACE PACKAGE TYPES
2 AS
3 TYPE RC IS REF CURSOR;
4 END;
5 /

Package created.

owner@DEV>
owner@DEV> create or replace function demofunc
2 RETURN types.rc
3 AS
4 l_rc types.rc;
5 BEGIN
6 open l_rc FOR SELECT id FROM loser;
7 return l_rc;
8 end;
9 /

Function created.

owner@DEV> select demofunc from dual;

DEMOFUNC
====================
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

ID
=================
1
2
3
4
5
6
7
8
9
10

10 rows selected.



What is the story with:

DEMOFUNC
====================
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

What generates that?
How do you get rid of it?
Do you need to get rid of it if you are returning these values to a calling program?

Thanks much

Tom Kyte
December 05, 2003 - 12:29 pm UTC

variable x refcursor
exec :x := demofunc
print x


the calling program (sqlplus in this case) is the thing printing it out -- if your client program doesn't print it out, it won't be printed out.


if you don't call the function from sql, just call the function and ask plus to print the cursor -- it'll look "normal"

ok

Mini, March 18, 2004 - 10:04 am UTC

Dear Sir,
I have a doubt on REF constraint and not on REF CURSOR.
Sorry for disturbing you.Is REF actually an integrity constraint?*Could you please show a simple example how does
it act as a constraint?*

Tom Kyte
March 18, 2004 - 10:09 am UTC

do you mean referential integrity constraint, as in a foreign key perhaps.

Is this use of ref cursor right?

Arun Gupta, April 14, 2004 - 11:39 am UTC

Tom,
A developer has coded like:
PROCEDURE ErrorDescription (i_err_num IN NUMBER, ref_cursor OUT ref_cursor_type)
IS
BEGIN
OPEN ref_cursor FOR
SELECT b.cde_key_table_re APPLN_ERROR_NUMBER,
b.txt_value_code_key APPLN_ERROR_DESCRIPTION,
a.txt_value_attrib APPLN_ERROR_SOURCE
FROM T_ADDNL_RE_TABLE_VALUE a, T_RE_TABLE_VALUE b
WHERE a.nam_table_re = b.nam_table_re
AND a.cde_key_table_re = b.cde_key_table_re
AND b.cde_key_table_re = i_err_num
AND b.nam_table_re = 'ERROR';
EXCEPTION
WHEN OTHERS
THEN
IF ref_cursor%ISOPEN
THEN
CLOSE ref_cursor;
END IF;

OPEN ref_cursor FOR
SELECT b.cde_key_table_re APPLN_ERROR_NUMBER,
b.txt_value_code_key APPLN_ERROR_DESCRIPTION,
a.txt_value_attrib APPLN_ERROR_SOURCE
FROM T_ADDNL_RE_TABLE_VALUE a, T_RE_TABLE_VALUE b
WHERE a.nam_table_re = b.nam_table_re
AND a.cde_key_table_re = b.cde_key_table_re
AND b.cde_key_table_re = '40204'
AND b.nam_table_re = 'ERROR';
END ErrorDescription;

I see two problems with this code:
a) He is closing the ref cursor in exception handler without doing any fetch operation. In our application, the ref cursor is passed to VB code which does a fetch so closing ref cursor doesn't make sense.
b) The exception handler itself seems redundant. The query associated with ref cursor will not be executed till the VB application does a fetch. By that time we are already out of stored procedure.

Please let me know if these observations are correct.
Thanks.

Tom Kyte
April 14, 2004 - 2:04 pm UTC

arg, a when others without a raise -- 99.9999% of the time this is a bug.

Here, this is a bug. But fortunately, self correcting.

It is curious what they might have been thinking. If the first query with i_err_num didn't work -- why would the second with '40204' (silly to store a number in a string no?) work?

The close is 'safe' as they open it again. it does hide from the application the fact an error occurred perhaps.

The code should reasonably end at the line above the exception block.

The exception block would only be entered in the strangest of situations (all but one of which that I can think of would be the same for the second open as well -- meaning, lots of code for naught) and probably not at all for that query as it is written..

Arun Gupta, April 14, 2004 - 3:20 pm UTC

The second ref cursor is within the exception handler. They want to return the second ref cursor if an exception occurs. My questions are:
a) Can just opening a ref cursor for a select generate an exception?
b) Supposing that an exception can occur, then the ref cursor will not open, so closing it within the exception handler is not required.

Please let me know if this is correct.

I agree that when others then... is a bug but only those who have spent hours trying to debug code with this kind exception handling can appreciate this.

Thanks...


Tom Kyte
April 14, 2004 - 3:50 pm UTC

my question is -- what exception could POSSIBLY happen in the outer query that would not happen in the query in the exception block given that the queries are pretty much "identical".

If one were to fail - the other would fail as well.

a) yes, pretty serious -- non recoverable errors pretty much. and only for queries that actually execute at least partially during the open which are "rare" (but do happen). this does not look like it would.

b) but they only close if open, so no big deal.




Arun Gupta, April 14, 2004 - 5:38 pm UTC

Tom,
Thanks very much for your help. The logic here is that they want to return a description for an error code from joining two lookup tables. If they hit an exception, they want to return a fixed error description, corresponding to error code '40204'.

Looking at the code, I could not figure out a scenario when they would hit an exception in the outer block so I decided to ask. As to your question, I do not have an answer. Your logic is impeccable !!

Cursor processing is not something that I understand in depth. I wasn't sure if opening a ref cursor in general can generate an exception. Now I know that it can. Can you please give an example since I have never come across this situation. The only time I encountered an error with ref cursor is if there is a syntax error in the query, which was caught at compile time or if there is a logical error which was caught at run time.

Thanks for being so patient...

Tom Kyte
April 15, 2004 - 7:43 am UTC

You need to find a cursor that does work during the EXECUTE (open) phase.  Easiest example would be a for update one.  Consider the behaviour of these two blocks when USERS is online and offline.

In general OPEN for a ref cursor will only return such heinous errors that cannot be dealt with at all -- that users is offline would prevent your developers logic from working as well (they would get the same error if they hit it once - -they will hit it AGAIN)

ops$tkyte@ORA9IR2> create table t ( x int ) tablespace users;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_cursor sys_refcursor;
  3  begin
  4          open l_cursor for select * from t FOR UPDATE;
  5          close l_cursor;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> declare
  2          l_cursor sys_refcursor;
  3  begin
  4          open l_cursor for select * from t ;
  5          close l_cursor;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter tablespace users offline;
 
Tablespace altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_cursor sys_refcursor;
  3  begin
  4          open l_cursor for select * from t FOR UPDATE;
  5          close l_cursor;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '/home/ora9ir2/oradata/ora9ir2/users01.dbf'
ORA-06512: at line 4
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_cursor sys_refcursor;
  3  begin
  4          open l_cursor for select * from t ;
  5          close l_cursor;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter tablespace users online;
 
Tablespace altered.
 
 

Arun Gupta, April 15, 2004 - 9:01 am UTC


Tracing REF CURSORs

Arun Mathur, July 01, 2004 - 10:51 am UTC

Tom,

When doing a level 12 trace on a function returning a ref cursor, I typically run it via SQL*Plus where I declare a refcursor variable and set autoprint to on. Based on the tkprof output, I notice the rows column in the Fetch process is always 1, regardless of how many rows SQL*Plus displays to the screen. I believe I'm misunderstanding what autoprint is doing, but I'd love to hear your opinion on this.

Regards,
Arun


Tom Kyte
July 01, 2004 - 11:35 am UTC

ops$tkyte@ORA9IR2> variable x refcursor;
ops$tkyte@ORA9IR2> set autoprint on
ops$tkyte@ORA9IR2> @trace
ops$tkyte@ORA9IR2> alter session set events '10046 trace name context forever, level 12';
 
Session altered.
 
ops$tkyte@ORA9IR2> begin
  2          open :x for select * from all_users;
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
 
USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYS                                     0 12-MAY-02

.....


SELECT *
from
 all_users
                                                                                          
                                                                                          
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        4      0.00       0.00          0        173          0          50
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0        173          0          50


I cannot reproduce.  is this what you meant?
 

Interesting

Arun Mathur, July 01, 2004 - 2:09 pm UTC

I got the same tkprof output as you demonstrated. Looking into it further, I made a PL/SQL script that'll give me the number of rows fetched. Then, I did a level 12 on the function with autoprint on:

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jul 1 12:45:34 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: amathur@ftdcdb
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jul 1 12:45:34 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: amathur@ftdcdb
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> create or replace procedure test_proc is
  2     v_ref_cursor types.rc;
  3     v_count integer;
  4     v_meas_name temp_meas_name_mkdext.meas_name%type;
  5     v_meas_type varchar2(180);
  6     v_stream_name varchar2(180);
  7     v_bus_type varchar2(180);
  8     v_bus_ident varchar2(180);
  9     v_label varchar2(180);
 10     v_source_rt varchar2(180);
 11     v_destination_rt varchar2(180);
 12     v_xfer_type_id varchar2(180);
 13     v_source_sa varchar2(180);
 14     v_destination_sa varchar2(180);
 15     v_word_count varchar2(180);
 16     v_word_type varchar2(180);
 17     v_word_len varchar2(180);
 18     v_word varchar2(180);
 19     v_word_interval varchar2(180);
 20     v_frame varchar2(180);
 21     v_frame_interval varchar2(180);
 22     v_message_id varchar2(180);
 23     v_offset varchar2(180);
 24     v_length varchar2(180);
 25  begin
 26     insert into temp_meas_name_mkdext values ('1553L_Pilot.16.T
 27     v_ref_cursor := return_inputs_algs('C5M','0090','0103');
 28     v_count := 0;
 29
 30     loop
 31
 32             fetch v_ref_cursor
 33             into v_meas_name,
 34             v_meas_type,
 35             v_meas_name,
 36             v_bus_type,
 37             v_bus_ident,
 38             v_label,
 39             v_source_rt,
 40             v_destination_rt,
 41             v_xfer_type_id,
 42             v_source_sa,
 43             v_destination_sa,
 44             v_word_count,
 45             v_word_type,
 46             v_word_len,
 47             v_word,
 48             v_word_interval,
 49             v_frame,
 50             v_frame_interval,
 51             v_message_id,
 52             v_offset,
 53             v_length;
 54
 55             v_count := v_count + 1;
 56
 57             exit when v_ref_cursor%notfound;
 58     end loop;
 59     dbms_output.put_line(v_count||' rows fetched.');
 60     close v_ref_cursor;
 61
 62  end;
 63  /

Procedure created.
**** as a side note, the insert statement is there so that **** the function will return rows
SQL> set serveroutput on size 1000000
SQL> exec test_proc;
2 rows fetched.

PL/SQL procedure successfully completed.

** now, I'll do a level 12 trace
SQL> select count(*) from temp_meas_name_mkdext;

  COUNT(*)
----------
         1

SQL> set autoprint on
SQL> variable x refcursor;
SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> alter session set timed_statistics=true;

Session altered.

SQL> exec :x := return_inputs_algs('C5M','0090','0103');

PL/SQL procedure successfully completed.
*** records print on the screen 

and, here's the tkprof:


TKPROF: Release 9.2.0.1.0 - Production on Thu Jul 1 13:04:57 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Trace file: ftdcdb_ora_27997.trc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.04          0          0          0           0
Execute      1      2.36       2.46          0          5          0           0
Fetch        1     17.68      35.20      32545      32823          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     20.08      37.71      32545      32828          0           1

Thanks for your help, Tom.

Arun

 

Tom Kyte
July 01, 2004 - 7:52 pm UTC

can you give me a short, concise, yet 100% complete test case I can just run (like I give you?)

get rid of the tons of columns -- just make it do the same thing you are doing, but very very simply.

Something on my end

Arun Mathur, July 02, 2004 - 10:57 am UTC

Tom,

I took

ops$tkyte@ORA9IR2> begin
2 open :x for select * from all_users;
3 end;
4 /



and converted it to a function that'll return a ref cursor. When executing via SQL*Plus and doing a level 12 trace, the rows column is correct. I even tried a few other queries using the same approach, and the traces remain consistent with the rows returned. So, I think it's safe to say that it's not tkprof, but my brain. Sorry Tom, but thanks for bringing me back to keeping things simple.

Regards,
Arun

Efficient way of closing a REF cursor from client.

Praveen, Vijayan, July 14, 2004 - 6:40 am UTC

Hi Tom,

As you have said, it is the job of the client (java) to close the opened ref cursor, after use.
I assume, by this, you mean, use the java code to do this. Suppose I write another function in
the same package, named "pkg_my_package.proc_close_cursor(p_in_ref_cur)", which closes the cursor,
how does the two method differ in terms of performance (apart from the cost involved in hiting the database again by calling the close_cursor procedure by the client)?

Thanks and regards

Praveen

Tom Kyte
July 14, 2004 - 11:35 am UTC

you cannot send a ref cursor back -- java must just call "rset.close()" on it.

Client closing refcursor

A reader, November 26, 2004 - 8:53 am UTC

"With the ref cursor -- that is it. That is the way it is done. the CALLER (java in this case) is responsible for the "close" call -- you don't do that in plsql when the client fetches from the cursor"

The refcursor my sp is returning is being consumed by a ASP/ASPX/.NET type application.

If I understand you correctly above, they are responsible for closing the cursor after they are done processing the data.

What if they dont? What are the ramifications of not closing the refcursor? Would it affect the next execution of the sp? Over time, would this cause a memory leak or something serious like that?

Thanks

Tom Kyte
November 26, 2004 - 10:22 am UTC

if an application constantly opens but does not close cursors - they will eventually hit ora-1000, max open cursors exceeded.

Just as they would get a file open error from the OS if they just opened but never closed files over and over.

a weird problem.

reader, April 06, 2005 - 12:34 pm UTC

Hi Tom,
good day to you, I am facing a very weird problem and I need your help to understand what's going wrong.
I have a funcion (for e.g. func1) which call a procedure (for e.g. proc1) with two in out parameters of type ref cursor, one of the parameter is used in the called procedure
(proc1) for some processing and second parameter returns the resultset to calling funcion (func1), when I use this funcion in anonymous pl/sql block the refcursor returns me only one record, where as if I use this funcion in select statement it returns me one record but that is the last result of the resultset.

I got to understand this only when after the select statement i executed command exec null; and all the dbms_output where printed in sql*plus.

I am not able to get what might be going wrong, so if you can please guide me on this that will be a great help.

Thanks in advance.
your fan.

Tom Kyte
April 06, 2005 - 2:16 pm UTC

need example

Ref Cursor only prints one row

Robert Ware, May 16, 2005 - 6:17 pm UTC

Tom,

Is it possible to use a refcursor to return multiple rows as I am trying to do in the following example?

variable x refcursor;
set feed on autoprint on longc 90000

begin
FOR obj IN (select owner,
object_name,
object_type
from dba_objects
where owner = 'RDWARE'
and object_type IN ('TABLE','VIEW','INDEX'))
LOOP
OPEN :x FOR select replace(DBMS_METADATA.GET_DDL (obj.object_type,obj.object_name),obj.owner,'TESTUSER') from dual;
END LOOP;
end;
/

Even though I have 13 objects in my schema that satisfy the above cursor, I only get the last one back.


sposd> /

PL/SQL procedure successfully completed.

Press <RETURN> To Continue...

REPLACE(DBMS_METADATA.GET_DDL(:B3,:B2),:B1,'TESTUSER')
------------------------------------------------------------------------------------------------------------------------------------

CREATE INDEX "TESTUSER"."T_IDX" ON "TESTUSER"."T" ("OBJECT_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL


1 row selected.


Am I doing something wrong or is there a limitation on what I am trying to do?




Tom Kyte
May 16, 2005 - 8:50 pm UTC

instead of opening 13 cursors, just open one!

open :x for
select replace( ... )
from dba_objects OBJ

where .....


;)

How can my Java programmer call this ref cursor

steve, May 18, 2005 - 10:57 am UTC

create or replace package pkg_dept
AS
type rc_dept is ref cursor;
end;
/

create or replace
procedure sp_dept( t_deptno IN NUMBER,
t_designation IN NUMBER,
dept_cur in out pkg_dept.rc_dept )
is
begin

OPEN dept_cur for
select ename,salary,join_date
from emp
where deptno=t_deptno
and designation=t_designation;

end;

Tom Kyte
May 18, 2005 - 11:37 am UTC

Cursor for returning multiple results

Aaron Rouse, May 18, 2005 - 2:52 pm UTC

I am trying to make an SP that will return a result set via populating a cursor. Currently the way I have it code though will only return the last record since I am opening the cursor and populating it with every loop instance. I was curious what I would need to do to this in order to grab just all records or perhaps if I am trying to approach my goal in the wrong manner. What I want to return is table name, column name and record counts for anything I am trying to delete that has FKs. My code example being for one table that has a varchar PK in it.

CREATE OR REPLACE PROCEDURE SP_DEL_USERS
(
var_inputtablename in USER_CONSTRAINTS.TABLE_NAME%TYPE,
var_userid in USERS.USERID%TYPE,
p_retcur out SYS_REFCURSOR

)
AS
p_rec SYS_REFCURSOR;

CURSOR FKS IS
SELECT C.TABLE_NAME, C.COLUMN_NAME
FROM USER_CONS_COLUMNS C
JOIN (SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE R_CONSTRAINT_NAME = (SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P'
AND TABLE_NAME = var_inputtablename)) F ON C.CONSTRAINT_NAME = F.CONSTRAINT_NAME;
BEGIN
DECLARE
var_tablename USER_CONSTRAINTS.TABLE_NAME%TYPE;
var_columnname USER_CONS_COLUMNS.COLUMN_NAME%TYPE;
num_int NUMBER;
BEGIN
FOR i IN FKS LOOP
OPEN p_retcur FOR 'SELECT COUNT(' || i.COLUMN_NAME || ') AS CNT, :2 AS TBL, :3 AS CLM FROM ' || i.TABLE_NAME || ' WHERE ' || i.COLUMN_NAME || ' = :1 AND ROWNUM > 0 GROUP BY ' || i.COLUMN_NAME
USING var_userid, i.TABLE_NAME, i.COLUMN_NAME;
END LOOP;
END;
END;
/

Tom Kyte
May 18, 2005 - 4:43 pm UTC

assuming primary keys are single columns, this'll do it



ops$tkyte@ORA9IR2> create table p ( x int primary key );
 
Table created.
 
ops$tkyte@ORA9IR2> create table c1 ( x references p );
 
Table created.
 
ops$tkyte@ORA9IR2> create table c2 ( x references p );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into p values (1);
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into c1 select 1 from all_users where rownum <= 5;
 
5 rows created.
 
ops$tkyte@ORA9IR2> insert into c2 select 1 from all_users where rownum <= 15;
 
15 rows created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace
  2  function get_count( p_tname in varchar2,
  3                      p_cname in varchar2,
  4                      p_val in int ) return number
  5  as
  6      l_cnt number;
  7  begin
  8      execute immediate '
  9       select count(*)
 10         from ' || p_tname || '
 11        where ' || p_cname || ' = :x'
 12         into l_cnt
 13        using p_val;
 14
 15      return l_cnt;
 16  end;
 17  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE OR REPLACE PROCEDURE SP_DEL_USERS
  2    (
  3     var_inputtablename in   USER_CONSTRAINTS.TABLE_NAME%TYPE,
  4     var_userid         in   int,
  5     p_retcur           out  SYS_REFCURSOR
  6
  7    )
  8  AS
  9  BEGIN
 10      open p_retcur
 11        for SELECT C.TABLE_NAME,
 12                   get_count( c.table_name, C.COLUMN_NAME, var_userid ) cnt
 13              FROM  USER_CONS_COLUMNS C
 14             where c.constraint_name in
 15                   (SELECT CONSTRAINT_NAME
 16                      FROM USER_CONSTRAINTS
 17                     WHERE R_CONSTRAINT_NAME =
 18                          (SELECT CONSTRAINT_NAME
 19                             FROM USER_CONSTRAINTS
 20                            WHERE CONSTRAINT_TYPE = 'P'
 21                              AND TABLE_NAME = var_inputtablename));
 22  END;
 23  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable x refcursor
ops$tkyte@ORA9IR2> exec sp_del_users( 'P', 1, :x )
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> print x
 
TABLE_NAME                            CNT
------------------------------ ----------
C1                                      5
C2                                     15
 
 

Thanks

Aaron Rouse, May 18, 2005 - 7:29 pm UTC

Yes, in this particular database there are only single PKs in it and the norm for the group is to just have single PKs. I had considered trying to expand out to handle if more than one but did not seem worth the extra effort since it went against "group standards". As usual, thanks for the help.

A reader, May 23, 2005 - 11:55 am UTC

hi tom,

in your first answer at the very top you wrote:

<quote>
I firmly believe the best java programs are those that have ZERO "selects/inserts/updates/deletes" in them. Hence, using ref cursors is the way to go. Lets you tune without bothering those java programmers.
</quote>

are there any advantages of doing so apart from having interfaces and access controlled -- something really convincing "those java programmers"?

Tom Kyte
May 23, 2005 - 3:46 pm UTC

tell them "you won't have to write sql" :)

sorry, but the reason is one of encapsulation, dependency tracking (who uses what), tuning....

i totally agree ...

A reader, May 24, 2005 - 1:44 am UTC

... but they will most likely reply "it 's much easier for us to *NOT* have queries encapsulated within stored procedures we are not allowed to modify/extend at will" ...

and even if they could - i believe it 's the other way around: they *want* to write SQL :o(

so please let me ask it that way: there 's no difference in terms of performance and/or resource consumption between those two approaches (access through ref cursors and straight SQL), right?

Tom Kyte
May 24, 2005 - 7:50 am UTC

You can (but they never will, they will not cache the cursors in the client) achieve fewer parses without ref cursors. Otherwise, a cursor is a cursor is a cursor.


This is a "religious war". I truly believe it is not about application development, apps come, they go. The apps we use today did not exist for the most part 5 years ago and won't exist in 5 years from now.

But the data........ the data will be there.

returning ref cursor result in a mail

a reader, June 23, 2005 - 3:23 am UTC

Hi,

I have a procedure which returns a ref cursor and java developers are able to call it without any issues. We have a requirement to create a job(dbms_job) that will send out a mail (using utl_smtp) with the result of that ref cursor.

is it possible and if so, please give the brief steps .

Thanks,

Tom Kyte
June 23, 2005 - 6:26 pm UTC

create procedure that_processes_a_ref_cursor
as
l_cursor sys_refcursor /* or whatever the type is */;
... other declares ....
begin
call_procedure_that_builds_ref_cursor( ...., l_cursor );
loop
fetch l_cursor into <variables>;
exit when l_cursor%notfound;
process them using utl_smtp
end loop;
close l_cursor;
end;
/


submit that as a job.


Question with declaring a variable for using refcursors --

Hawk, July 13, 2005 - 5:26 pm UTC

Hi Tom,

create or replace procedure sp_refcur_test
( var_userid in int, p_retcur out sys_refcursor)
as
v_sql varchar2(3000);
begin
v_sql:= 'select A1.*, B1.* from A1,B1 ';
open p_retcur for v_sql;
end;

declare
crec sys_refcursor;
crec1 --?????? How should I declare this ??????
begin
sp_refcur_test(1, crec);
for i in 1..5
loop
fetch crec into crec1;
dbms_output.put_line(crec1.C1);
dbms_output.put_line(crec1.C2);
end loop;
end;

How should I declare the variable where its ?????? above.
Also I know this would work if I had defined
crec1 as crec table%type when cursor only had one table A1, i.e ???? answer would be crec A1%rowtype.
But how should I define it when my cursor has multiple tables (A1 and B1)

Please advise??


Tom Kyte
July 14, 2005 - 9:40 am UTC

declare it any way you like.

I've used template cursors in the past.

I'll make the assumption sp_refcur_test really does something, it adds a where clause, but the select list stays constant.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 <code>

shows the concept of a template query.

Cursor For Loops

Yuan, July 22, 2005 - 3:19 pm UTC

I'm having trouble in 9i using a cursor for loop with a ref cursor variable. Here is my code snippet:

PROCEDURE Load IS

/***************************************************************************************************
PURPOSE: Polls for files then loads them.

REVISIONS:
Ver Date Author Description
-------- ---------- -------------------- -----------------------------------------------------------
1.00 03/11/2005 Yuan Tschang 1. Created this procedure.
***************************************************************************************************/

lcrsrFile Common.Pkg_File_Util.gcrsrDirList;
lvDir All_Directories.Directory_Path%TYPE;

BEGIN

-- UPS
Common.Pkg_File_Util.Open_Dir_List(lcrsrFile);
SELECT Directory_Path INTO lvDir FROM All_Directories
WHERE Directory_Name = Pkg_UPS.gv_DIR_NM;
Common.Pkg_File_Util.Make_Dir_List(lvDir);
FOR rFile IN lcrsrFile LOOP
Load_File(rFile.File_Nm, gv_UPS_SOURCE_CD);
END LOOP;

END Load;

Here's the error I get:
PLS-00221: 'LCRSRFILE' is not a procedure or is undefined

I take it you can't use cursor for loops with ref cursors?

Tom Kyte
July 22, 2005 - 6:42 pm UTC

no, you have to

loop
fetch LCRSRFILE into ......;
exit when LCRSRFILE%NOTFOUND;
....
end loop;
close LCRSRFILE;


SUGGEST strongly that you use p_ and l_ as prefixes for Parameters and Local variables -- ALL plsql variables (to avoid confusion with column and table names)

Prefixes

Yuan, July 26, 2005 - 9:14 am UTC

Thanks for the response. I do use p and l as you suggest, but I skip the underscore. The 2nd letter after p/l indicates the datatype (v for varchar, r for record, etc). I realize in naming "rFile," I left out the scope indicator. I suppose I should have made it lrFile.

weak vs strong ref cursors

EB, August 03, 2005 - 6:02 am UTC

Tom,

I've just been told that I can never, ever use SYS_REFCURSOR and must always define my record types and cursors in package headers.

I wasn't too convinved of the arguments given. Can you give any guidelines for when strong and weak refcursors should be used?

Thanks in advance.
EB

Tom Kyte
August 03, 2005 - 11:39 am UTC

I use weak ref cursors unless and until I am forced by the compiler to use a strongly typed one.

Or when the tool invoking the stored procedure cannot deal with a weakly typed cursor.


What were the arguments given?

weak vs strong - arguments for strong

EB, August 04, 2005 - 8:19 am UTC

Hi Tom,

remember these aren't my arguments! I sent out an email suggesting use of sys_refcursor, this was the response from the lead dba (my boss).

'Not sure I like this.

Although it means cursors can be changed or created quickly, using package headers prevents ad-hoc cursor changes which could have adverse affects on the site / java.

In addition, when using a package header your a defining the record type in advance which forces data types and consistency.

Please therefore continue to use package headers.'

sounds like a classic case of FUD to me.

Thanks
EB

Tom Kyte
August 04, 2005 - 9:52 am UTC

it does not prevent ad-hoc cursor changes (I mean, if I can change code, I can change CODE can't I -- well, yes, yes I can. The code might be in a spec, or not, so what)

As to the data types and consistency -- Java result sets don't give a whit about that -- ask them how the java code "respects" this? Answer: They do not, they just get a result set, assume there are N columns and that the first column is a number and so on. The java stuff doesn't "see" the spec.



never heard a truer word

EB, August 04, 2005 - 10:47 am UTC

Tom,

thanks for that, it pretty much confirmed my thoughts. Unfortunately the person in question refuses to change his mind so I'm stuck with working the way he wants. (believe me this is the tip of the iceberg. he also asks for people not to use package bodies but to keep everything in standalone procs and functions ... but don't get me started).

Thanks anyway.
EB

Very Good

sheebs, September 19, 2005 - 9:08 am UTC


Excellent

Gerry, September 22, 2005 - 1:19 pm UTC

Tom,

As always, very good example and practical. Thanks so much for this site, can't imagine life without it!

constructing dynamical sql for ref cursor

A reader, October 04, 2005 - 11:51 am UTC

Tom,
i have one requirement to prepare a statement and return ref cursor from stored procedure. If i can reuse the first example given in this thread my example will be like this
----------------------------------------------------------
create or replace
procedure sp_dept( t_deptno IN NUMBER,
t_designation IN NUMBER,
use_desig_flag IN CHAR(1),
dept_cur in out pkg_dept.rc_dept )
is
sqlQuery VARCHAR2(2000);
begin

-- Prepare the sqlQuery depending on use_desig_flag.If flag is set use that flag.
----------------------------------------------------------


Like this example, I have many arguments to this procedure. Depending on those arguments, i need to prepare the sql and open a ref cursor which will be returned.

while trying this approach of concatenating the sql statments, i get error as the variable used in pl/sql is treated as column in table and i get invalid column error.
I tried to concatenate the pl/sql variable, still i get error. Please help me in solving this.

Thanks Tom.




Thanks Tom

A reader, October 05, 2005 - 9:37 am UTC

Thanks a lot Tom. I am able to implement my stored procedure using the information provided by you.

Thanks again.

Move pointer to previous record

Eduardo, October 13, 2005 - 4:17 pm UTC

Hi Tom, I hope you can help me. Can I move the pointer to the previous record using FETCH ( siimilar to VB methods movenext or moveprevious )

However, thank you.

Tom Kyte
October 13, 2005 - 8:55 pm UTC

depends on the language the client fetching the data is written in...

Move pointer to previous record

Eduardo, October 14, 2005 - 3:51 am UTC

I´m using PL/SQL. I´m migrating an application from VB to PL/SQL.

Tom Kyte
October 14, 2005 - 8:02 am UTC

pl/sql does not have backwards scolling cursors.  show me the logic you are trying to perform and I'll show you the best practice for doing that logic in plsql.


if the result set is "small enough" (say 100/200 rows max) you could:

ops$tkyte@ORA10G> declare
  2          cursor c is select * from emp;
  3          type array is table of c%rowtype index by binary_integer;
  4          l_data array;
  5  begin
  6          open c;
  7          fetch c bulk collect into l_data;
  8          close c;
  9
 10          for i in 1 .. l_data.count
 11          loop
 12                  dbms_output.put_line( l_data(i).ename );
 13          end loop;
 14  end;
 15  /
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
KING
TURNER
ADAMS
JAMES
FORD
MILLER
SCOTT
 
PL/SQL procedure successfully completed.


since the data is in an array, you can address any "row" in the result set using a subscript.

keep it small however, "in memory" remember. 

asktom is about Oracle, not VB

Billy, October 14, 2005 - 4:45 am UTC

Eduardo from Spain wrote:
>
> I´m using PL/SQL. I´m migrating an application from VB to
> PL/SQL

As Tom said Eduardo, this depends entirely on what the client language (VB in your case) allows you to do with the VB structure/object that encapsulates a database REF CURSOR pointer.

For example, in Delphi, one will use the NEXT method of the TDataSet class.

Consult your VB documentation, Google, or Usenet VB groups


Tom Kyte
October 14, 2005 - 8:02 am UTC

(but he is moving FROM vb - to PLSQL :)

Move pointer to previous record

Eduardo, October 14, 2005 - 2:42 pm UTC

Tom, thanks for your help, that I want is the next:

open cursor
loop
fetch cursor into record
exit when cursor%notfound

.
.
. some checks
.
.

if record.field = value then
.
. move next
. another checks
. move previous
.
end if

end loop
close cursor

I hope you can help me again, thanks a lot



Tom Kyte
October 14, 2005 - 5:40 pm UTC

isn't that just:

next_record := null;
open cursor;
loop
if (next_record is null )
then
fetch cursor into record;
exit when cursor%notfound;
else
record := next_record;
next_record := null;
end if;

...... some checks ......
if record.field = value
then
fetch cursor into next_record;
if ( cursor%notfound ) then you_figure_out_what_happens_here; end if;
..... another checks ....
..... no need to move previous, you have previous in records
..... and next in next_record
end if;
end loop;
close cursor;





Select distinct columns from cursor

Anne, October 17, 2005 - 4:15 pm UTC

Hi Tom,

I will try describe my problem as best as I can. I have the following requirement :
hrs_cur : cursor for project-emp-hours in the current period.
distinct project_array : for distinct project_id in hrs_cur
distinct emp_array : for distinct emp_id in hrs_cur
distinct proj_emp_array : for distinct project_id, emp_id in hrs_cur.
FOr every row in the hrs_cur do processing and update the proj_array, emp_array.....

I have coded this as follows :
declare
cursor hrs_cur is
SELECT agency_nr, customer_id,
project_id .......
from ....;

TYPE proj_rec_type IS RECORD (
project_id bps_project.project_id%TYPE
, proj_max bps_project.max_bill_amt%TYPE
, proj_bal bps_project.max_bill_amt%TYPE
);

TYPE proj_type IS TABLE OF .....;
TYPE emp_type IS TABLE OF .....;
TYPE proj_emp_type is TABLE OF .... ;

proj_array proj_type;
emp_array emp_type;
proj_emp_array proj_emp_type;
begin

---Build proj_array
for x in
(SELECT project_id, proj_bal
from proj_table
where project_id in
( select distinct project_id
<from the query for hrs_cur>
)
loop
proj_array(x.project_id) := x.proj_bal;
end loop;

---Build emp_qrray
for x in
(SELECT emp_id, cost_to_date
from emp_table
where emp_id in
( select distinct emp_id
<from the query for hrs_cur>
)
loop
emp_array(x.emp_id) := x.cost_to_date;
end loop;


--- Build proj_emp_array
for x in
(SELECT project_id, emp_id, proj_emp_id
from proj_emp table
where (project_id, emp_id) in
(select distinct project_id, emp_id
<from the query for hrs_cur>
)
loop
proj_emp_array(x.project_id) := x.proj_emp_id;
end loop;

Open hrs_cur;
fetch hrs_cur bulk collect into hrs_tab;
Close hrs_cur;

for i in 1 .. hrs_tab.count
loop
<do some processing and set the 'balances' in the emp_array, proj_array and emp_proj_array.....>
end loop;

exception
when OTHERS then
dbms_output.put_line (DBMS_UTILITY.FORMAT_ERROR_STACK);
end;


Question : Is there a better way for building the emp, proj, and emp_proj arrays so that I do not have to duplicate the query for hrs_cur ? Thanks so much!

Open Mouth. Extract Foot.

Billy, October 18, 2005 - 10:09 am UTC

Tom said:
> (but he is moving FROM vb - to PLSQL :)

ARRGHH!! So he did. Sheez.. Seems like I must up my caffeine intake.. Not understanding a plain English statement. Cripes.

So I guess it is now the leather straps and cuffs as I have been a bad boy? ;-)

But seeing that is the case (not the bad boy bit, but porting VB code to PL/SQL). Why use REF CURSORs in PL/SQL simply because REF CURSORs have been used in VB?

And if dynamic SQL does not feature in the answer that I will raise serious question marks as to why REF CURSORS usage from VB is not ported to implicit or explicit PL/SQL cursors.

REF CURSORs in PL/SQL should be the exception and not the norm when the application itself is PL/SQL.

Ditto for porting Pro*C code. Using a EXEC SQL in Pro*C does not mean that the code now needs to be ported to PL/SQL using the EXECUTE IMMEDIATE statement.. I mean it is great to see the developer fully grok how to use DBMS_SQL, but it is painful to see that being used when a single plain SELECT statement in the PL/SQL code would have more than sufficed.


Tom Kyte
October 18, 2005 - 10:17 am UTC

but where did ref cursors come from? (they were looking for a scroll cursor - either an array or simple logic in plsql)


:)

Dynamic creation of an array which is type of a table passed as a parameter to the procedure

VK, November 23, 2005 - 5:43 am UTC

Hi Tom,

You are great as usual :-) I used the code given here for one of the requirements but I am looking for one more feature in the code. The code given by you -

ops$tkyte@ORA10G> declare
2 cursor c is select * from emp;
3 type array is table of c%rowtype index by binary_integer;
4 l_data array;
5 begin
6 open c;
7 fetch c bulk collect into l_data;
8 close c;
9
10 for i in 1 .. l_data.count
11 loop
12 dbms_output.put_line( l_data(i).ename );
13 end loop;
14 end;
15 /

Say suppose i use this to create a procedue and the parameter to this procedure is a table name. The array declared inside the procedure should be of type passed as the parameter to this procedure. In the above code table "c" would be passed as the parameter.

Regards
VK

Tom Kyte
November 23, 2005 - 9:55 am UTC

you cannot - the number and types of columns are not know at compile time, therefore plsql cannot process the ref cursor.

you will be using DBMS_SQL which gives you a procedural api to process any result set with. search for dump_csv on this site for an example.

Delete millions of rows and table name is passed as parameter

VK, November 24, 2005 - 11:42 pm UTC

Dear Tom,

If i modify the below code and use this as procedure to delete millions of rows based upon the output of the query then it will take a lot of time, so please suggest me how can i do bulk collect into some object and then delete it which i suppose is efficient

create or replace function get_rows( p_tname in varchar2 ) return number
as
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue number default NULL;
l_status integer;
begin
dbms_sql.parse( l_theCursor,
'select count(*) from ' || p_tname,
dbms_sql.native );

dbms_sql.define_column( l_theCursor, 1, l_columnValue );
l_status := dbms_sql.execute(l_theCursor);
if ( dbms_sql.fetch_rows(l_theCursor) > 0 )
then
dbms_sql.column_value( l_theCursor, 1, l_columnValue );
end if;
dbms_sql.close_cursor( l_theCursor );
return l_columnValue;
end ;
/


If I use dump_csv procedure then the output of query will be written into a file and deleting data from a table based upon the contents of a file is inefficient.

Tom Kyte
November 25, 2005 - 9:57 am UTC

how can you use a procedure that counts rows to delete ?

not following you here at all.

Delete millions of rows and table name is passed as parameter

VK, November 26, 2005 - 12:27 am UTC

I am sorry the query can be -
select empno from emp where rownum < 100000;

Then these empno should be deleted from some other table. If i store these empno in a file and then delete it may not be efficient and if i delete one row at a time then also it may not be efficient. So please suggest something like bulk collect which can be efficient.

Regards
VK

Tom Kyte
November 26, 2005 - 12:11 pm UTC

delete from some_other_table
where empno in ( select empno from emp where rownum < 100000 );

???

but that would be a scary thing to do as you wouldn't know what random 100,000 empno's were choosen.

What are you actually trying to accomplish?

Delete millions of rows and table name is passed as parameter

VK, November 27, 2005 - 10:56 pm UTC

I am really sorry for not making things clear to you.

select empno from emp where sal < 5000;

Say suppose this would fetch me some 100000 records. These empno should be deleted from some other table. If i store these empno in a file as suggested in dump_csv function and then delete it may not be efficient and if i delete one row at a time then also it may not be efficient. So please suggest something like bulk collect which can be efficient.




Tom Kyte
November 28, 2005 - 7:09 am UTC

delete from some_table where empno in ( select empno from emp where sal < 5000 );


still don't get the "file" or the need for one.

OK

Kumar, November 30, 2005 - 11:54 am UTC

Hi Tom,
Do ref cursors stay in shared pool of SGA??

Tom Kyte
November 30, 2005 - 3:10 pm UTC

no more than non-ref cursors stay there.


there is the query and the plan - that is in the shared pool.

then there is the cursor area, your specific invocation of that sql statement, that is in your session, with your bind variables, your current point in the execution of that sql statement and so on.

ref cursor performance

Gerald Koerkenmeier, November 30, 2005 - 4:44 pm UTC

Tom,

On 9iR2, why would the exact same SQL statement (with no variables at all, just a select from a whole table) within a PL/SQL procedure (running nothing but the SQL) returning a ref cursor take several orders of magnitude longer than a straight SQL statement, when they both have the EXACT same execution plan?

I used sql_trace to dermine the following:
The query running in the PL/SQL statement took 4.71 seconds (4.64 seconds spent waiting - 4.39 on exec and .25 on fetch).

The query running directly from SQL*Plus took .21 seconds (.18 seconds spent waiting - .02 on exec and .16 on fetch).

Any ideas? Are ref cursors inherently slower than straight SQL? Let me know if you need more specific information about the query, they tables, etc.


Tom Kyte
November 30, 2005 - 9:01 pm UTC

no, you need to show us the code. or at least the tkprof.

My bad.

Gerald Koerkenmeier, December 03, 2005 - 10:19 pm UTC

I made the mistake of not comparing apples to apples. The difference in time was due to a hard parse.

ref cursor within a ref cursor

A reader, December 21, 2005 - 9:59 am UTC

Tom, Can I have multiple ref cursors within a ref cursor? If so, can you show me a simple example?
I tried
open p_cursor for
select cursor(select * from emp),cursor(select * from
dept) from dual
But looks like they are just embedded cursors and not ref cursors
Thanks,

Tom Kyte
December 21, 2005 - 7:33 pm UTC

what is the difference to you?

a ref cursor isn't really much different from a "regular cursor" is it?



It's for ODP.NET

A reader, December 22, 2005 - 9:38 am UTC

Tom, It doesn't make any difference[atleast that's what we thought] but we were trying to test Oracle's ODP.NET [looks like latest Oracle client for .NET can handle multiple ref cursors in a ref cursor], so we tried using embedded cursors, but it did not recognize any cursor from the output and threw some errors. So we thought why not actually pass some ref cursors inside a ref cursor and see how it goes.

That was the idea..
I created that using two functions returning ref cursors and sending them out using another [ref cursor] in a procedure. in pl/sql it was working but ODP.NET still not able to identify them. So i was wondering is there any special way of doing things for ODP.NET.

Any suggestions?

Tom Kyte
December 22, 2005 - 11:01 am UTC

I don't know much (like nothing) about odp.net - sorry, I'll refer to you to otn.oracle.com -> discussion forums.

Is it possible to create a table from a REF CURSOR?

Sonny C., February 14, 2006 - 9:43 am UTC

Hi Tom,

Is it possible to create a table from a ref cursor variable in SQL Plus?

For example, say I want to create a table from the cursor returned from your "my_pkg.pivot" routine in Expert Oracle One on One - is this possible?

I was thinking of something like this (I know this won't work - but thought you could suggest something that would)...

DECLARE rc REFCURSOR;

EXEC routine(:rc);

CREATE TABLE rc_table
AS
SELECT * FROM CURSOR(:rc);

Thanks.

Tom Kyte
February 14, 2006 - 11:04 am UTC

no, you cannot send a cursor BACK to the server once you retrieve it.

You would have to describe it, create a table based on it, fetch and insert the rows.

Wouldn't it be cool though?

Sonny C., February 14, 2006 - 11:20 am UTC

Thanks Tom,

I appreciate your help. Do you think that this be a cool new feature (since a cursor just points to a memory location on the server)?

How does one go about asking for new features like this in the database? Could you point me in the right direction?

Thank you sir.

Tom Kyte
February 14, 2006 - 1:01 pm UTC

no, not really - you open result sets to return to clients.

Since the create of tables to me is something you don't normally do - that is the job of an installation program or an upgrade program - I don't see the purpose.

If you are creating tables on the fly at runtime - there is a better than very very good chance you are doing something wrong.


to add an enhancement request, you use metalink.oracle.com - an iTar is the way to get them in.

I have to select from a refcursor with some more filter condition..Is it possible

Shouvik Basu, February 16, 2006 - 9:20 am UTC

I was just curoius if I can apply some more filter on the REFCURSOR. How can I do it. Is the syntax given below correct ?

DECLARE rc REFCURSOR;

EXEC :rc:=complex_function(simple_inputs);

SELECT * FROM CURSOR(:rc)
WHERE some_more_conditions


Tom Kyte
February 16, 2006 - 12:11 pm UTC

no, it is not.

Performance of Ref cursor

Girish, February 24, 2006 - 7:06 am UTC

From performance point of view which is best normal cursor or ref cursor.Assume this is with respect to an application where a procedure is called multiple times to fetch data

Tom Kyte
February 24, 2006 - 8:52 am UTC

once open, they perform the same - since they are basically "the same"

"normal" is in the eye of the beholder, there isn't anything really like a "normal" cursor.


In plsql, if you can use plsql's static SQL with implicit cursors - do so.


Merge Ref Cursors

Marshall Thompson, March 02, 2006 - 10:45 am UTC

I wonder if my pl/sql can open two ref cursors, then merge the results from those two cursors into a third ref cursor which I could return to java?

Tom Kyte
March 02, 2006 - 1:09 pm UTC

no, you cannot.

Didn't think so

Marshall Thompson, March 02, 2006 - 1:47 pm UTC

Must be another way. I am probably approaching the sql that makes up my ref cursor illogically.

Given:
create table Orgs
(
id number not null,
name varchar2(30) not null
);

create table MetricOrgAssoc
(
id number not null,
metricId number not null,
orgId number not null
);

create table MetricValue
(
id number not null,
MetricOrgAssocId number not null,
forDate date not null,
value number(10,4) not null
);


insert into Orgs values(1, 'Division 1');
insert into Orgs values(2, 'Division 2');
insert into Orgs values(3, 'Division 3');
insert into Orgs values(4, 'Division 4');
insert into MetricOrgAssoc values(1, 1, 1);
insert into MetricOrgAssoc values(2, 1, 2);
insert into MetricOrgAssoc values(3, 1, 3);
insert into MetricOrgAssoc values(4, 1, 4);

insert into MetricValue values(1, 1, to_date('20060101','YYYYMMDD'), 1000);
insert into MetricValue values(1, 3, to_date('20060101','YYYYMMDD'), 3000);
insert into MetricValue values(1, 4, to_date('20060101','YYYYMMDD'), 4000);

insert into MetricValue values(1, 1, to_date('20050101','YYYYMMDD'), 100);
insert into MetricValue values(1, 2, to_date('20050101','YYYYMMDD'), 200);
insert into MetricValue values(1, 3, to_date('20050101','YYYYMMDD'), 300);

How do I get output in the following format:
MetricValue.forDate Orgs.name MetricValue.value
20060101 Division 1 1000
20060101 Division 2 null
20060101 Division 3 3000
20060101 Division 4 4000
20050101 Division 1 100
20050101 Division 2 200
20050101 Division 3 300
20050101 Division 4 mull

In essence, I can't figure out how to get the entries with the null values into my resultset. The production query will need to work with any number of divisions and dates, but I want them in order by date descending with a slot for each Division, even when there is no record in MetricValue for the Division on that date.



Tom Kyte
March 02, 2006 - 2:23 pm UTC

In essence, I don't have any idea what your query is? don't really know what joins to what or how.

Sorry about that - more info

Marshall Thompson, March 02, 2006 - 3:15 pm UTC

Here's the basic query:

select mv.forDate, o.name, mv.value
from metricorgassoc moa,
orgs o,
metricvalue mv
where moa.orgid = o.id
and moa.id = mv.metricorgassocid
order by mv.forDate desc, o.name asc;

which produces:
FORDATE NAME VALUE
--------- ------------------------------ ----------
01-JAN-06 Division 1 1000
01-JAN-06 Division 3 3000
01-JAN-06 Division 4 4000
01-JAN-05 Division 1 100
01-JAN-05 Division 2 200
01-JAN-05 Division 3 300

I'd like to get:
FORDATE NAME VALUE
--------- ------------------------------ ----------
01-JAN-06 Division 1 1000
01-JAN-06 Division 2 null
01-JAN-06 Division 3 3000
01-JAN-06 Division 4 4000
01-JAN-05 Division 1 100
01-JAN-05 Division 2 200
01-JAN-05 Division 3 300
01-JAN-05 Division 4 null


Tom Kyte
March 03, 2006 - 7:41 am UTC

so, now you need to describe what these "null entries" you are talking about are.

i don't see any nulls in your example (pretend you are explaining this problem to your Mother - use that sort of detail when explaining a problem... :)

More info

Marshall Thompson, March 03, 2006 - 8:04 am UTC

Ok, looking back at this, I guess my personal naming scheme is not obvious to the world.

I have a table of organizations. A table of metrics (not included above.) A table metricorgassoc which associates metrics to organizations. MetricValue is a table of metrics (numbers) entered for a metric/org/date. So, the data I setup above shows metricId=1 having data entered for two different dates, 2006-01-01, and 2005-01-01. On 2006-01-01, I only entered information for three of the four organizations associated with the metric (orgs 1,3,4). On 2005-01-01, I only entered data for orgs 1, 2, 3.

I am building a web page based off the resultset. For each date, I would like to include also the orgs for which I did not enter data. Those would be the orgs with the null values (2006-01-01 is org 2, 2005-01-01 is org 4). This will help me to build a page where you can either edit the existing value or add a new value where one did not previously exist.

(It just so happens from my sample data that the MetricOrgAssoc.id matches the corresponding Org.id, but that is not normally the case, a fluke of my sample data.)



Tom Kyte
March 03, 2006 - 8:30 am UTC

and finally, we need a version.

In 9i and before, we will generate another result set - the cartesian product of all org ids and dates. We will OUTER JOIN to that to make up the missing data.

In 10g and later, we will use a partitioned outer join to break the data up by date and outer join each date partition to the set of org ids to make up the missing data.

(that might even be enough information for you to figure it out :)

9i is the version

Marshall Thompson, March 03, 2006 - 8:32 am UTC


Tom Kyte
March 03, 2006 - 8:34 am UTC

(did you try the outer join yourself? hint hint hint?)

Got it

Marshall Thompson, March 03, 2006 - 10:07 am UTC

It had not occurred to me to do the cartesian product first. Believe me, I had tried many things before asking.

select sub.forDate, sub.id, mvv.value
from
(select distinct id, forDate
from
(select moa.id, mv.forDate
from metricOrgAssoc moa,
metricValue mv)) sub
left outer join metricValue mvv
on mvv.metricOrgAssocid = sub.id
and mvv.forDate = sub.forDate
order by sub.forDate desc, sub.id asc;

FORDATE ID VALUE
--------- ---------- ----------
01-JAN-06 1 1000
01-JAN-06 2
01-JAN-06 3 3000
01-JAN-06 4 4000
01-JAN-05 1 100
01-JAN-05 2 200
01-JAN-05 3 300
01-JAN-05 4

Thanks for the hints.

Tom Kyte
March 03, 2006 - 11:20 am UTC

:)

Function Returning Multiple Values

Suren, May 25, 2006 - 3:12 am UTC

Dear Tom,

Thanks for the help.

Consider folllowing situation where I want to return multiple values from a function.

CREATE OR REPLACE TYPE CALCType AS OBJECT
(
VAL1 BINARY_FLOAT
, VAL2 BINARY_FLOAT
, VAL3 BINARY_FLOAT
, VAL4 BINARY_FLOAT
, VAL5 BINARY_FLOAT
)
/

CREATE OR REPLACE FUNCTION CALC (
ID VARCHAR2
) RETURN CALCType
IS
L_VAL1 BINARY_FLOAT;
L_VAL2 BINARY_FLOAT;
L_VAL3 BINARY_FLOAT;
L_VAL4 BINARY_FLOAT;
L_VAL5 BINARY_FLOAT;
BEGIN
L_VAL1 := 111.56;
L_VAL2 := 222.56;
L_VAL3 := 333.56;
L_VAL4 := 444.56;
L_VAL5 := 555.56;

RETURN CALCType(L_VAL1, L_VAL2, L_VAL3, L_VAL4, L_VAL5);

END CALC;
/

I can receive individual values as
SELECT CALC('1').VAL1, CALC('1').VAL2,CALC('1').VAL3, CALC('1').VAL4, CALC('1').VAL5 FROM DUAL;

However it may execute the function those many times.
Is it any way where I execute the function only once and still get individual values?

The function is required to be called in a Query along with other columns.

OR any other way to achive this?

Regards,


how to pivote a refcursor result set ?

A reader, August 03, 2006 - 4:31 pm UTC

Tom , is it possible to pivote a result set ?

we are going to use other groups database via stored procedure interface, in one of the stored proc. we get a result set now I want to pivote that result set in my stored proc. how can I achive it ?

1.) one by one in the loop ?
2.) any smarter way to do it all at once with the query ?

Tom Kyte
August 03, 2006 - 5:05 pm UTC

you would have to do it procedurally - you pivot in SQL or you pivot in your code as you fetch from SQL. If you are getting an already opened cursor - you are pivoting yourself in code.

hopefully the data is sorted correctly :) Otherwise you are in for quite a job.

Thanks for your reply..

A reader, August 03, 2006 - 5:20 pm UTC

Yes, what you put in that's what you get out. (about the data)

Requirement for pivoting the result set came because mgmt whans reports and the data model and all stored proc. api are very very OLTP and when the application was build no body even consider reporting !!



So which is better for a single row?

Jean Williams, August 25, 2006 - 11:39 am UTC

We are calling Oracle through ADO from vb and ASP. For a single returned row is it better/more efficient to use SELECT INTO and return each variable - or is it better to pass a refcursor that only returns one row?

Tom Kyte
August 27, 2006 - 8:44 pm UTC

if I was to call a procedure, instead of a ref cursor, these sound like OUT variables don't they???? why return a ref cursor if you know the result is simple scalars - have plsql select into the scalars and return them as OUT parameters.

the less SQL you put into VB code the better as far as I am concerned.

Any way to pass ref cursor over db link?

Albert Nelson A, December 15, 2006 - 7:19 am UTC

Hi Tom,

Passing ref cursor over db link is not working (not supported?):

SQL> create procedure pro_test (p_io_ref in out sys_refcursor) is
  2  begin
  3    open p_io_ref for select 1 from dual;
  4  end;
  5  /

SQL> declare
  2    v_ref sys_refcursor;
  3    n number;
  4  begin
  5    pro_test(v_ref);
  6    fetch v_ref into n;
  7    dbms_output.put_line(n);
  8    close v_ref;
  9  end;
 10  /

1

PL/SQL procedure successfully completed

Connect to another database.

SQL> select * from dual@cn2dev.com;

DUMMY
-----
X

SQL> declare
  2    v_ref sys_refcursor;
  3    n number;
  4  begin
  5    pro_test@cn2dev.com(v_ref);
  6    fetch v_ref into n;
  7    dbms_output.put_line(n);
  8    close v_ref;
  9  end;
 10  /

declare
  v_ref sys_refcursor;
  n number;
begin
  pro_test@cn2dev.com(v_ref);
  fetch v_ref into n;
  dbms_output.put_line(n);
  close v_ref;
end;

ORA-24338: statement handle not executed
ORA-06512: at line 6


What should be the way to pass result sets across db link from a procedure?

Regards,

Albert Nelson A. 

Tom Kyte
December 15, 2006 - 9:09 am UTC

it is not supported (and documented as not being supported...)

Yes. Documented as not supported :-(

Albert Nelson A, December 15, 2006 - 9:25 am UTC

Thanks Tom.

But is there any other way to pass the result set using procedure?

I have an idea though it is not strictly passing result set 'through procedure'. Populate a global temporary table in the procedure and let the remote select from it:

SQL> create global temporary table gtt(n number, dt date);

Table created

SQL> create procedure pro_test (n number) is
  2  begin
  3    delete from gtt;
  4    insert into gtt values (n,sysdate);
  5  end;
  6  /

Procedure created

-- Now connect to another database

SQL> exec pro_test@cn2dev.com(1);

PL/SQL procedure successfully completed

SQL> select * from gtt@cn2dev.com;

 N DT
-- -----------
 1 15/12/2006


-- May be  we can create a procedure encapsulating the above 

SQL> create or replace procedure pro_test_loc (n number, p_io_cur in out sys_refcursor) is
  2  begin
  3    pro_test@cn2dev.com(n);
  4    open p_io_cur for select * from gtt@cn2dev.com;
  5  end;
  6  /
  
Procedure created


SQL> declare
  2    v_ref sys_refcursor;
  3    type typ_rec is record (
  4    n number,
  5    dt date);
  6    v_rec typ_rec;
  7  begin
  8    pro_test_loc(7, v_ref);
  9    fetch v_ref into v_rec;
 10    dbms_output.put_line(v_rec.n);
 11    dbms_output.put_line(v_rec.dt);
 12    close v_ref;
 13  end;
 14  /
7
15-DEC-06
  

What is your opinion?

Regards,

Albert Nelson A.   

Tom Kyte
December 15, 2006 - 11:40 am UTC

that works.

Thanks

Albert Nelson A, December 18, 2006 - 6:46 am UTC


A Reader, December 20, 2006 - 11:26 pm UTC

hi tom,

we are wanting to use sys_refcursor for a reporting application, where given the parameters the back-end PL/SQL procedure has to do considerable amount of work, that is unrealistic to do in a single query statement (rollup data for periods : YTD, LYTD - compute growth, not to mention applying Security).

very conceptually the back-end job would be best done using a multi-pass SQL where by you create temp tables for some steps and then join some temp tables in the final step to get the results and then drop the temp tables.
(i know you are not a fan of temp tables but to get high level aggregated reports with advanced metrics against a normalized database - i think its a justified use in this case :-).

my question is, in a sys_refcusrsor based solution how can this paradigm be implemented in the back-end proceures - particularly since the fetching from the cursor is the responsibility of the client.

i dont think we can use temp tables as the client fetching data is in a different session,

if we use some kind of permamnent tables (with say a request number) there is the issue of housekeping - who cleans up the tables used to pass results back

would appreciate any pointers / your thoughts ?



Tom Kyte
December 22, 2006 - 5:58 am UTC

doesn't sound unrealistic to me. sounds like extra code I wouldn't want to have to write but whatever.

the client fetching the data is in another session - that does not compute, Oracle doesn't work that way.

sounds like you are trying to build some sort of batch system here - are you really sure you want to do that.

Please help

Rajesh, February 05, 2007 - 2:28 am UTC

Hi Tom,
I have a function like the one below which dynamically
constructs a select string by passing TABLE_NAME,Column_name
and country_code as parameters.
The Country_code is NOT getting passed as 'US' but it gets
passed as US.
when I give as ||'ccode'|| it is passed as a string not as
a literal.
Any help you can provide?

create or replace function testref(tname in varchar2,cname in varchar2,ccode in varchar2)
return sys_refcursor
is
rc sys_refcursor;
begin
open rc for 'select count(distinct'||' '||cname||')'||' '||'from'||' '||tname||' '||
'where country_code = '||ccode;
return rc;
end;
Tom Kyte
February 05, 2007 - 7:55 am UTC

man oh man

BIND VARIABLES
BIND VARIABLES
BIND VARIABLES
BIND VARIABLES
BIND VARIABLES
BIND VARIABLES
BIND VARIABLES
BIND VARIABLES
BIND VARIABLES
BIND VARIABLES

SQL INJECTION
SQL INJECTION
SQL INJECTION
SQL INJECTION
SQL INJECTION
SQL INJECTION
SQL INJECTION
SQL INJECTION
SQL INJECTION
SQL INJECTION
SQL INJECTION
SQL INJECTION
SQL INJECTION
SQL INJECTION
SQL INJECTION
SQL INJECTION
SQL INJECTION
SQL INJECTION
SQL INJECTION

you are not using #1
you are subject to #2

please read about dbms_assert to avoid the sql injection issue with cname and tname

and please for the love of whatever - use bind variables


open rc for '..... where country_code = :x' USING ccode;


Ref cursor through function or procedure

kishore, April 24, 2007 - 7:20 am UTC

Hello Sir,

Could you please let me know which one is better

1. to return a ref cursor from procedure

CREATE OR REPLACE PROCEDURE p_out_cursor(pr_cursor OUT sys_refcursor)
AS
BEGIN
OPEN pr_cursor FOR SELECT /*procedure*/ * FROM dept;
END;


OR

2. to return a ref cursor from function

CREATE OR REPLACE FUNCTION f_return_cursor RETURN sys_refcursor
AS
lr_cursor SYS_REFCURSOR;
BEGIN
OPEN lr_cursor FOR SELECT /*function*/ * FROM dept;
RETURN lr_cursor;
END;

I think the first one, since procedure will do everything by reference ('pass by reference') whereas function would copy it after returning to the local variable which means by value (pass by value). Could you please let me know if I am correct. Is there a way to find out the open ref cursors that have not been closed. I was looking into v$open_cursors with my session id however could not find.

Thanks & Regards
Kishore
Tom Kyte
April 24, 2007 - 11:19 am UTC

which one makes you feel better :)

a function is simply a procedure with the last parameter being an OUT parameter.

except for the calling syntax, there is not really any difference between:

create function f(y in number) return number 
as....

create procedure p( y in number, x OUT number ) 
as....

x := f(y);

p(y,x);


both use a temporary in the body of the function/procedure and have "copy on return" semantics.

Using REF Cursor in FROM clause of SELECT

Shahram Samardar, April 26, 2007 - 3:46 pm UTC

Hi

I have a ref cursor and I want to use this to open another ref cursor

Assume:

declare

acursor ref cursor ;
bcursor ref cursor ;

acursor has pointer to records with columns named a1, a2 , a3, a4

I want to use a1 , a2 columns of above and assign two columns of acursor to bcursor

My psudo requirement is

open bcursor for
select a1 as b1 , a2 as b2
from acursor ;

how can I put acursor in above psudo?
Thanks


Tom Kyte
April 27, 2007 - 10:24 am UTC

sorry, it does not work that way.

getting ora-06504 error for working on ref cursor resultset

shahram samardar, April 27, 2007 - 3:42 pm UTC

I have two types and a function ( part ofa package body) in below.
the last parameter of function is IN OUT CCREF and I defined ccref as
type ccref is ref cursor in package spec




CREATE OR REPLACE
TYPE                 SEC_MAN.GET_RECORD_TYPE AS OBJECT
 (object_type number ,
 object_sha1 varchar2(40) ,
 object_value varchar2(67) ,
 msg   varchar2(255)
 ) 
/

CREATE OR REPLACE
TYPE                 SEC_MAN.GET_RECORD_TABLE_TYPE AS TABLE OF GET_RECORD_TYPE; 
/

function get_castle ( 
        in_client_id in varchar2 , 
        in_out_resultset IN OUT GET_RECORD_TABLE_TYPE ,
        in_out_ccref   IN OUT ccref 
        )
return varchar2
is
   error_code NUMBER ;
   error_msg  VARCHAR2 (3000) ;
   application_task varchar2(30) ;
   sql_stmt varchar2(4000) ;
   k number ;


   type refCursorType  is ref cursor ;

   refCursor refCursorType ;

   sensitive_info_rec CASTLE.sensitive_info%rowtype ;

   result sec_man.GET_RECORD_TABLE_TYPE := GET_RECORD_TABLE_TYPE();

begin

........

loop

  fetch refcursor into sensitive_info_rec ;
   exit when refcursor%notfound ;
   result.extend ;
   result (result.count) := GET_RECORD_TYPE ( sensitive_info_rec.object_type ,
                                              sensitive_info_rec.object_sha1 ,
                                              sensitive_info_rec.object_value ,
                                              NULL  
                                                  ) ;
end loop ;


open in_out_ccref for  select *    from TABLE ( RESULT );

end get_castle;





The code that is calling above function is :

CREATE OR REPLACE FUNCTION myfunction
 (IN_CLIENT_ID IN varchar2
 ,IN_CC_SHA1 IN varchar2
 ,OUT_CC_SHA1 IN OUT newcastle_pkg.ccref
 )
 RETURN VARCHAR2
 IS
 type cc_Number_list_type is table of varchar2(255) ;
 type cc_sha1_list_type is table of varchar2(40) ;
 type object_type_list_type is table of number ;
--  type ccref is ref cursor ;
 error_code NUMBER ;
 error_msg  VARCHAR2 (3000) ;
 input_SHA1_list   tableOfVarcharType := tableOfVarcharType() ;
 result ResultRecTable := ResultRecTable();
 ret varchar2(4000) ;
 in_out_resultset  GET_RECORD_TABLE_TYPE := GET_RECORD_TABLE_TYPE();
 cursor_ref  newcastle_pkg.ccref ;
 cc_number_list cc_number_list_type ;
 cc_sha1_list  cc_sha1_list_type ;
 object_type_list object_type_list_type ;   
 output_cursor_table ResultRecTable := ResultRecTable () ; 
 a number ;
  b varchar2(100) ;
  c varchar2(100) ;
begin

...

  ret := newcastle_pkg.GET_CASTLE ( in_client_id , in_out_resultset , cursor_ref ) ;
         loop 
            fetch cursor_ref into a , b, c ;
            exit when cursor_ref%notfound ;
            dbms_output.put_line ( a || ' :' || b || ':' || c ) ;
         end loop ;


  exception
     When OTHERS then
              error_code  := SQLCODE;
              error_msg   := SQLERRM;
              return error_code || ' ' || error_msg;

end myfunction ;
/


When I run above code it returns for fetch command:
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match


It complains about cursor ref parameter of called function. In called function, cursor ref has three columns and in calling area, I am fetching into three variables.

I appreciate for your help about this that how to change the code.

Thanks

still getting ora-06504

Shahram Samardar, April 27, 2007 - 5:44 pm UTC

That's me again, about last review


in my calling area, I added
tmp GET_RECORD_TABLE_TYPE := GET_RECORD_TABLE_TYPE();

and then tried to fetch
fetch cursor_ref bulk collect into tmp ;

Still I am getting the same ora-06504 error

Appreciate for your help

Tom Kyte
April 29, 2007 - 8:52 am UTC

if you gave us a 100% complete, yet tiny example demonstrating the issue - we'd be glad to peek at it.

as it is, I cannot run your code - starting with a create table, some inserts, all of the needed types, and a tiny block of code....

you will likely find the issue yourself making such an example! I usually do.

copy one ref cursor to another ref cursor

shahram samardar, April 30, 2007 - 2:05 am UTC

Hi



I have a ref cursor and I want to use this to open another ref cursor

Assume:

declare

acursor ref cursor ;
bcursor ref cursor ;

acursor has pointer to records with columns named a1, a2 , a3, a4

I want to use a1 , a2 columns of above and assign two columns of acursor to bcursor

My psudo requirement is

open bcursor for
select a1 as b1 , a2 as b2
from acursor ;

I asked this question and the followup answer was it is not possible. I like to know what is your recommended solution to do above?
Tom Kyte
April 30, 2007 - 9:26 am UTC

it is still not possible, asking again won't really change that.

Change your logic, you already HAVE a1 and a2, I don't see why you need them "again"

How do I know if a ref cursor returns data without fetching from it?

Mauro, May 03, 2007 - 2:53 pm UTC

Hi,

My cursor is:

open v_cur_Loc for
Select code, descr, postal
from Loc_tbl;

I used an exception when no_data_found and this didn't work
I tried ask v_cur_Loc%notfound and this didn't work either
And when I asked v_cur_loc%rowcount, it always returned 0, whether the query returns data or it doesn't.
These two options work fine when the cursor is FETCHed, but after that the cursor returns one less record than it should...
Tom Kyte
May 04, 2007 - 12:40 pm UTC

you don't - you do not know until you fetch that you have exhausted the cursor.

A cursor that returns 0 records is not "exceptional", it is normal, it is not an error or anything like that.

Ordering Result-set Dynamically

Ernie, May 17, 2007 - 12:52 pm UTC

Tom,
One of our applications has a need to query the database dynamically, and once the result-set is returned sort the records differently.

For instance

SELECT x.*
FROM (
SELECT rownum rr,e.*
from EMP e) x
WHERE x.rr BETWEEN 0 and 16;

Once the first 15 records are returned to them, then they have the opportunity to sort the result, and hence run a different query to get the result, which would be in essence..

SELECT x.*
FROM (
SELECT rownum rr,e.*
from EMP e
ORDER BY e.ename) x
WHERE x.rr between 0 and 16;

Now, I understand the "performance" issues, as we are currently having some in certain cases where no criteria is given on a 100k result. But is there a good way to do this?

Thanks in advance...
Tom Kyte
May 18, 2007 - 3:43 pm UTC

do you understand how rownum works? (I do not think so)

See
https://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html

for how to paginate, and if your order by needs to be dynamic, so be it, make it dynamic sql using bind variables - but use the pagination query.

I like it.

ernie, May 21, 2007 - 9:11 am UTC

I do understand what rownum does, hence the question of how to work around it's limitations. I did not know about the analytic ROW_NUMBER function and how to use it however.

Thank you for your response and help as always. I will look at implementing with this new "method" going forward.

Also, this will be dynamic PL/SQL procedure with bind variables of course. The order by will be dynamic, based on what the user asks to sort by.

Thanks again.

Curious Note:

Ernie, May 24, 2007 - 10:18 am UTC

Tom,

I have implemented the solution as you provided, and it's excellent. Am seeing however over a large result-set > 90k records, with multiple table joins in the inner part of the query that the results are not so speedy. Especially when you change the order by statement in the ROW_NUMBER() OVER(---).

We have 8 table joins. the average on our development system is acceptable at ~9 seconds.

For instance:

CREATE TABLE t (id number, c_text varchar2(12),p_seq_num number);
create table p(seq_num number , prsn_name varchar2(60));

select a.*
from (
select row_number() over(order by p.prsn_name asc) rr
,t.id
,t.c_text
from t
,p
where t.p_seq_num = p.seq_num(+)
and t.c_text like '%some_text%'
) a
where a.rr between 0 and 16;

Now the above is just test example tables and we have 7 others, but you get an idea about what we are doing. Some of the order by expressions come from the tables that are joined 5 - 6 - 7 joins deep.

Wouldn't expect a 1 - 2 second response, because the whole result-set has to be ordered by your order by clause in the row_number function. Could there be a faster implementation with pagination and ordering over a vast result set?

Thanks in advance.

Tom Kyte
May 26, 2007 - 11:29 am UTC

you'd have to look at indexing and other things to be able to provide sorted results based on a where clause rapidly.

for an ad-hoc search, many times the right answer is a summary table. Not really sure I want to be doing an 8 way join of normalized transactional data for an ad-hoc search over and over again.


If you can get a query that can find the N records fast (without joining to umpteen other tables) - you can use that as an inline view and then just join to that.

Table Join ?

Ernie, June 06, 2007 - 8:18 am UTC

Tom,

I realize that without knowing the full schema and indexing it's difficult if not impossible to answer definitively. However without putting our patented schema on here, could you give a relative answer as to the diminishing effects of query joins.

For instance all of the tables I mentioned do get search criteria on them. For instance a static table may be joined to get lookup descriptions, and those descriptions are sorted or filtered etc...

All of them are like this, hence I would not have needed them at all in the query to begin with. I do have several procedure based result-sets returning data to end-users and for the most part they return very fast. Use of indexes etc...

I never needed to sort on demand as in this case and knowing better the inner workings of what the sorting is doing in context of multiple table join query may be useful.

The query without sort using FIRST_ROWS and ORDERED hints etc returns data in milliseconds, even with 9 tables being joined. It's just when they start wanting the result set sorted and paged dynamically where the issue comes to play.

Thanks for your great help.

Tom Kyte
June 06, 2007 - 1:48 pm UTC

our patented schema - hey, if it is patented, you can obviously post it, for it is fully documented and publically viewable.

and besides, if you ever release your product into the real world, it'll be there for the entire planet to look at. including any and all sql you ever execute against it.

if your indexing is such that

a) we have to get all of the rows
b) and then sort

versus

a) start walking the main tables via an index that retrieves the data sorted
b) then filter out rows we don't need based on your predicate
>>>> we can start returning data HERE <<<<
c) keep doing a and b until we are done.


then we have to do that. If we can efficiently use an index to retrieve the data already sorted, then filter - you MIGHT be able to get a row back fast (depends on whether we have to filter a million sorted rows before finding one of interest or not)

Ok...

Ernie, June 06, 2007 - 3:40 pm UTC

Tom,

Maybe patented was not the best word to utilize here.

Can you tell me a little bit about your second set of bullet points (i.e. walking the main tables) with data already sorted...

I am curious to see what that would look like.

Thanks...

Tom Kyte
June 06, 2007 - 9:31 pm UTC

look at your ultimate order by;

could an index be used to retrieve the data SORTED and then filter.

select *
from t1, t2, t3, ...
where whatever
order by t1.x, t1.y

if we had an index on t1.x, t1.y - then we could get the data from t1 ordered by x, y and start applying the where clause to it, joining to t2, t3, t4 and so on and finishing the where clause and returning data "fast" (maybe, maybe not - depends on how many rows get filtered before the first row can be returned)

for example.


Looking at it further.

Ernie, June 07, 2007 - 1:46 pm UTC

Tom,

I understand your point about having the order by from the base "driver" table. Meaning that the joined tables are just extra index joins to get information.

However in our implementation it's more dynamic...

For instance.

SELECT x.*
FROM
(
SELECT row_number() over (order by a.a ASC) rr
,COUNT(*) OVER ( ) r_cnt
,a.a
,b.desc b
,c.desc c
,d.desc d
FROM a, b, c, d
WHERE a.a = b.a(+)
and a.c = c.c(+)
and a.d = d.d(+)
and (a.a = :a or :a IS NULL)
and (b.t = :b or :b IS NULL)
and (c.z = :c or :c IS NULL)
and (d.x = :d or :d IS NULL)
) x
WHERE x.rr BETWEEN :e and :f;

Of course the basic query with join conditions returns quickly (millisecond response). The issue that I have been seeing is in re-executing the query with the dynamic variation of ordered results.

If you can imagine the row_number order by statement would / could be dynamic coming from any column of the four that are joined, and the sort order could be either ascending or descending.


Hopefully this helps. So far the row_number() over (order by xxx) has been a raving success and I truly do like it.

Thanks for the continued input and assistance as always.




Tom Kyte
June 07, 2007 - 3:21 pm UTC

,COUNT(*) OVER ( ) r_cnt

that is going to FORCE any query to....

of course get the last row before you see the first row.

forget order by - that count() is killer. It will be your major limiting factor. You are counting all of the hits before the first hit can ever bee seen (because the first hit must report the total)

I'd suggest dropping that concept immediately.

I agree

Ernie, June 07, 2007 - 4:01 pm UTC

It's a killer. However how would I give an "accurate" total of records returned to the end-application without it.

I know you have the concept on your pages here on the site of "potential", however we need an accurate total of records returned. This without having to do a seperate count based on a similarly filtered result set.

Any help would be appreciated.

Tom Kyte
June 09, 2007 - 10:14 am UTC

who cares if it is accurate. end users need to know

a) their search had hits
b) they are looking at N thru M of them
c) there are more after M

beyond that - they need nothing of accuracy (google, gold standard for searching, wouldn't be if they actually told you the truth about the number of hits)

There is no help if you demand to count the entire result set, there is precisely one way to accomplish that - and if this data is subject to modification (inserts, updates, deletes) then this accurate number is not so accurate (it is likely different by the time the end user gets the result). And I have to believe it is under constant heavy modification - else you would not use so many tables to search!!! (this must be OLTP data)

Ok.

Ernie, June 15, 2007 - 7:50 am UTC

Tom,

I realized that this particular counting thing is a sore spot. I know that it doesn't necessarily make sense to us why the business users want / ask / need for these things, but they are "requirements", and although we have told them the down effects of doing things, they continue to say they "need to have it".

So, that being said, we do not always get what we as system / database people like to have.

The application asks for a count of "how many records returned". I don't think I would be any good position to tell a business owner what it is that they should recieve. Only that asking for it could have a "bad" effect. They say what does that mean to me? I say slower performance. They say let me see it and make my own decision. The real effect to me as a developer is minimal, "I can do it". However the effect it has on the business as a whole is well received. So, it remains.

That is the reason why I asked if there could be a different solution to the COUNT() OVER() with the ROW_NUMBER() OVER(ORDER BY) solution.


Thanks for your insights and opinions. I do understand where you are coming from.
Tom Kyte
June 15, 2007 - 7:56 am UTC

if you have to do it, you will pay the price of getting the last row before they see the first

and there is no avoiding that, that should be obvious.


You are in the position to explain to the business owner they will need to fork over 10 times as much money for disk, hardware and everything if they want this number in their lifetime. That in fact - is our job.

It is our job, they do not understand the cost of what they asked for - it is our job to explain that in no uncertain terms.

I've always wondered how IT got into this "we must do whatever they ask" position - it is just so very wrong.

Alexander the ok, June 15, 2007 - 2:03 pm UTC

Tom,

I found your last follow-up very insightful. I always find your out of the box thinking interesting. Specifically, the part about not doing whatever customers want without pushing back when what they want doesn't make sense (or what they think they want).

It surprises me a little to hear you say that because I read in one of your books most of your experience was for government projects. Surprised because I would expect you to have little luck in persuading them. I've worked for the government before and they absolutely would not listen to reason no matter what we said.

Didn't matter that they had no technical knowledge or experience whatsoever either.


Ref Cursor use

Bob Nesbit, September 10, 2007 - 7:24 pm UTC

I've read through this question/answer thread and don't seem to find what I need.
I have been told that "Ref Cursor" can handle my problem.
I need to select in one stored procedure, creating a table in working storage. Then in a second stored procedure select from a table and join to the table that was created in the 1st stored procedure. Is Ref Cursor the way to do this?
Here is my attempt. It doesn't work. The real prooblem is very complicated and over 10K lines of code and many stored procedures. I've simplified.

input tables:
CREATE TABLE books (
isbn CHAR(10) PRIMARY KEY,
category VARCHAR2(20),
title VARCHAR2(100),
num_pages NUMBER,
price NUMBER,
copyright NUMBER(4),
author1 NUMBER CONSTRAINT books_author1
REFERENCES authors(id),
author2 NUMBER CONSTRAINT books_author2
REFERENCES authors(id),
author3 NUMBER CONSTRAINT books_author3
REFERENCES authors(id)
);

CREATE TABLE inventory (
isbn CHAR(10) CONSTRAINT fk_isbn REFERENCES books (isbn),
status VARCHAR2(25) CHECK (status IN ('IN STOCK', 'BACKORDERED', 'FUTURE')),
status_date DATE,
amount NUMBER
);

populate tables:
INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2, author3)
VALUES ('72121203', 'Oracle Basics', 'Oracle DBA 101', 563, 39.99, 1999, 1, 2, 3);

INSERT INTO inventory (isbn, status, status_date, amount)
VALUES ('72121203', 'BACKORDERED', TO_DATE('06-JUN-2004', 'DD-MON-YYYY'), 1000);

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2)
VALUES ('72122048', 'Oracle Basics', 'Oracle8i: A Beginner''s Guide', 765, 44.99, 1999, 4, 5);

INSERT INTO inventory (isbn, status, status_date, amount)
VALUES ('72122048', 'IN STOCK', NULL, 5000);

Package:
CREATE OR REPLACE PACKAGE pks_books_sel AS
PROCEDURE proc_books_sel
(books_results SYS_REFCURSOR);
END pks_books_sel;
/

Body:
CREATE OR REPLACE PACKAGE BODY pks_books_sel AS
PROCEDURE proc_books_sel
(books_results SYS_REFCURSOR)
AS
BEGIN
books_sel (books_results);
books_join (books_results);
END;
END pks_books_sel;
/

2 stored procedures:
CREATE OR REPLACE PROCEDURE books_sel
(books_results IN OUT SYS_REFCURSOR)
IS
BEGIN
OPEN books_results FOR
SELECT ISBN, TITLE
FROM books;
END books_sel;
/

CREATE OR REPLACE PROCEDURE books_join
AS
det_record VARCHAR2(32760);
CURSOR book_join_cursor IS
SELECT b.ISBN, b.TITLE, i.STATUS
FROM books_results b
LEFT JOIN inventory i on
b.ISBN = i.ISBN;
d_rec book_join_cursor%ROWTYPE;
BEGIN
IF NOT book_join_cursor%ISOPEN THEN
OPEN book_join_cursor;
END IF;
FETCH book_join_cursor INTO d_rec;
WHILE book_join_cursor%FOUND
LOOP
BEGIN
det_record :=
d_rec.ISBN
|| '|' || d_rec.TITLE
|| '|' || d_rec.STATUS;
dbms_output.put_line(det_record);
END;
FETCH book_join_cursor INTO d_rec;
END LOOP;
IF book_join_cursor%ISOPEN THEN
CLOSE book_join_cursor;
END IF;
END books_join;
/

Tom Kyte
September 15, 2007 - 3:13 pm UTC

....
I need to select in one stored procedure, creating a table in working storage.
Then in a second stored procedure select from a table and join to the table
that was created in the 1st stored procedure. Is Ref Cursor the way to do
this?
....


no, a ref cursor would be entirely inappropriate - a ref cursor is not a 'table', a ref cursor is a cursor, a result set, something you can fetch from.

it is not anything that you can join to.

However, that said - I fail to see why you having to select in one stored procedure, to put the data into a global temporary table - and then just join to it.

Just join in the first place


My suggestion will be "erase stored procedure one, it is not relevant"


Just incorporate the query you were going to put into some working storage into the greater query in stored procedure two.

Ref Cursor use

Bob Nesbit, September 18, 2007 - 2:06 pm UTC

Hi and thanks for your note. However, as I stated, I over simplified the sample to explain it easier. To attempt to answer your question, WHY:
The "table" created in the first stored procedure needs to be passed to and joined in multiple subsequent stored procedures. Several of these "passed" "tables" are to be created, passed and joined throughout the package. Currently this is done with "cataloged" tables that are truncated at the end of the run. However, our facility is not allowing us to create "temporary" tables any longer (since the DBA's must maintain these definitions of empty tables, from then on, in the production environment).

This is why I'm looking for a method to create and pass tables in working storage from one stored procedure to others. I could do as you say and combine the procedures, but this would make multiple lengthy reads of the same master tables over and over (time and efficiency concern).

Any thoughts?

Regards,
Bob Nesbit

Tom Kyte
September 18, 2007 - 4:58 pm UTC

oh my gosh - so what if the dba must maintain a DEFINITION.


you know, sometimes I just sit here and pound my head on the desk saying "for the life of me, I don't get it"

here you are - just trying to do your job - and because of a stupid rule "no global temporary tables, the DBA's are afraid of them, don't understand them" you are wasting your time, my time, our money.. Great

(can you tell that silly 'rules' like this just annoy me no end?)


You can, sigh, use a collection - but then you'll be wanting to create types and types are definitions..... sigh....

meaning, I could show you - but it would not matter since you would be wanting to issue "create type"

are you allowed to do that?

cannot you fight this silly rule?

Ref Cursor use

Bob Nesbit, September 19, 2007 - 2:07 pm UTC

Tom -
I'm sorry to frustrate you, I've tried to suggest dropping the rule, but the Data Warehouse manager is adamant.
Oddly enough, I can (currently) create types. I guess this is because there is no maintenance on the "type" other than the "name" of it.
It sounds like you are saying that REF CURSOR and PIPELINE neither one can do this, but a "collection" might.
Thanks,
Bob Nesbit
Tom Kyte
September 20, 2007 - 9:23 pm UTC

and there is NO MAINTENANCE ON A GTT - other than the "name of it"

a pipelined function needs a collection of an object type. You need types in order to pipeline.


I'm not frustrated, I just feel really bad for you to have to work in such a silly place.


Ask the DBA what is the difference in their life between you issuing:

create type myScalarType as object ( a int, b date, c varchar2(30) );
create type myTableType as collection of myScalarType;
create procedure p( x in sys_refcursor ) return myTabletype
pipelined
as
begin
.... pipe some rows ....
end;
/



and

create global temporary table foo ( a int, b date, c varchar2(30) );


(well, except that one will eat PGA memory and consume cpu cycles like mad and the other - well, won't. I'll let you guess which is which :) )

ref curcor use

Bob Nesbit, September 21, 2007 - 11:10 am UTC

Tom -
Thanks again for your help. I appreciate your answering. I'll try to make this work.
Regards,
Bob Nesbit

ref cursor

A reader, September 21, 2007 - 10:12 pm UTC


We need a Court of Silly Appeals

Mark Brady, September 24, 2007 - 11:26 am UTC

Tom, in your new role at Oracle, I believe that you should ask to have a courtroom constructed. Virtually, mind you, no need for a carpenter. In this court, hard working developers can bring suit against their DBAs/Managers etc who insist on silly rules. You play Sam Waterson, the prosecuting attourney, and typically have someone in tears by the end of the episode. We'll podcast the whole thing and let people vote on the guilt or ignorance of the silly-rule-maker.

Working title is: "Bind or Suffer" maybe "Raw and Order By".

There could be a court reporter named Star Joins. :-)

Varying the Column Set

Greg Solomon, October 09, 2007 - 6:04 am UTC

Hi Tom

A slightly obscure question ... I created

FUNCTION ff RETURN SYS_REFCURSOR IS
rr SYS_REFCURSOR;
BEGIN
IF dbms_random.VALUE > 0.5 THEN
OPEN rr FOR SELECT * FROM emp;
ELSE
OPEN rr FOR SELECT * FROM dept;
END IF;
RETURN rr;
END;

When I test my function via (for example) PL/SQL Developer, I get either a ref cursor containing emp, or a ref cursor containing dept, and PL/SQL Developer has no trouble working out which. Data comes out on a pretty grid, column headers and so on.

Suppose now that the function is wrapped and I have no idea what's inside it. Is there any way to use it via PL/SQL, or are the column definitions only available if I call it via JDBC, ODBC, etc ?

Thanks and Regards
Greg

Fetching cursor second time

Binish, November 08, 2007 - 1:25 pm UTC

Can we fetch records from a cursor many times? Or in other words, after fetching all records, can we point it back to first record and start fetching records again?

My requirement is to return a cursor to java (from pl/sql package), but before I do so, I need to scan through the fetched records to check for some conditions. Finally if the condition is met, I return the cursor, else I would return another cursor.

Thanks
Binish
Tom Kyte
November 09, 2007 - 11:53 am UTC

why bother returning to java if you are going to process it?

No, you would need two cursors - if they need be identical in contents, you best use flashback query or read only transactions or serializable to ensure the two cursors see the same exact data.

Varying the Column Set

Greg Solomon, November 14, 2007 - 12:32 pm UTC

Hi Tom

In terms of my previous question, I see that the 11g New Features Guide states that in 11g it is possible to convert a REF CURSOR to a DBMS_SQL cursor.

That sounds like it might be one way of doing it ?

Thanks
Greg
Tom Kyte
November 19, 2007 - 6:30 pm UTC

correct, in 11g that would work.

Just wrote about that last month in the Magazine:
https://www.oracle.com/technetwork/issue-archive/2007/07-nov/o67asktom-101004.html

see "Improved Dynamic SQL"

Passing a ref cursor as a java parameter

Andrew Markiewicz, November 20, 2007 - 4:29 pm UTC

Tom.
I have seen numerous examples where java code can create a CallableStatement and retrieve a ref cursor as an OUT parameter or function result. You gave similar examples in this thread.

// Prepare a Callable Statement, to make a call to the PLSQL function
  // demo_refcursor.getRefCursor, and obtain the returned REFCURSOR
  CallableStatement stmt = 
      connection.prepareCall("{? = call demo_refcursor.getRefCursor }");
      
  // Set the Out Parameter type to be of type CURSOR
  stmt.registerOutParameter(1, OracleTypes.CURSOR);
  stmt.execute(); // Execute the statement

  // Cast the returned parameter, (defined as type, OracleTypes.CURSOR)
  // to a JDBC result-set. 
  resultSet = (ResultSet)stmt.getObject(1);



But is it at all possible to pass a ref cursor opened in plsql as a parameter to a java stored procedure? The main problem is: what is the java class of the input parameter for a ref cursor?
The stmt.registerOutputParameter(1, OracleTypes.cursor) command above must be setting up the data type mapping with the OracleTypes.cursor constant so that stmt.getObject(1) command can be cast as a ResultSet.

I am not a java expert so there could easily be something I am missing. Though, I've searched through the onion layers of java class docs and tried several attempts but none works thus far.

A few attempts:

public static String testRef (ResultSet cur)...
public static String testRef (oracle.jdbc.oracore.OracleType cur)...
public static String testRef (oracle.sql.STRUCT cur)...
public static String testRef (oracle.jdbc.oracore.OracleTypeREF cur)...

I find it odd that java must call plsql to get a ref cursor but that java can not seemingly accept a ref cursor parameter.

Thanks
Andrew


Vikas Atrey, November 21, 2007 - 3:53 am UTC

You can bulk collect the result set into a collection before opening the ref cursor and use that collection in your condition checking .
And there is no need to fire the query again to open the ref cursor . You can use something like below.

Open <ref> for select column_value from Table(your collection);
Tom Kyte
November 20, 2007 - 1:22 pm UTC

well, that is not the same as processing a ref cursor twice.

and I'm not sure I'd suggest that in general. No, I'm sure I wouldn't suggest - maybe a pipelined function - but not that.

I don't want a million records in memory.

cursor

vineela, January 08, 2008 - 6:34 am UTC

How we can assign the retrieved cursor values to another cursor which is returned individually
Tom Kyte
January 08, 2008 - 7:35 am UTC

that doesn't even make sense - a cursor is a result set, the result of a query.

You can write a pipelined function - so that the client can "select * from your_plsql_routine" and your plsql routine can process whatever cursor it wants and then return any sort of result set it wants to (search for pipelined on this site for lots of examples)

REFCURSOR

ROOPA, January 18, 2008 - 12:25 am UTC

How to reduce the the burden/main memory on database if i am using refcursor to hold large data to increase performance.
Tom Kyte
January 19, 2008 - 10:33 pm UTC

by teaching yourself that large result sets are not held in memory.


by a refcursor
by a not-a-refcursor

(since a refcursor is just a cursor and a cursor is a cursor and cursors do not copy all of their data into memory)


so, you need do nothing, it already is not a burden on anyone.

passing a cursor to a pipeline function

debyani, April 03, 2008 - 10:27 am UTC

Tom,
We are running oracle 10g on AIX.
We have a pipeline function that parses BLOBs and returns blob values in pivoted format.The function is called in a view.
The actual blob value is stored in mkt.UIDMKTINPUTINTERVAL.valuecodes_blob column. We donot need to return the BLOB.
here's the query:

CREATE OR REPLACE VIEW MKTINPUTINTERVAL_PIV_PV AS
select /*+ ordered */ mkt.UIDMKTINPUTINTERVAL ,
mkt.pit_start ,
mkt.pit_stop ,
mkt.pit_start_yq ,
mkt.starttime ,
mkt.stoptime ,
mkt.spi ,
mkt.intervalcount ,
b.int001 , stat001 ,b.int002 , stat002 ,
b.int003 , stat003
from ndlstar.MKTINPUTINTERVAL mkt,
table (udt_parser.fn_parser_pivoted_cartridge(cursor(SELECT pit_start,pit_stop,
pit_start_yq,
UIDMKTINPUTINTERVAL,
starttime,
stoptime,
spi,
intervalcount,
valuecodes_blob
from dual
),1,1)) b

This seem to work fine, the only puzzling thing is that we are not selecting the BLOB value explicitly from the first table to pass it into the cursor(ie ,select col from dual).
How does the BLOB value get passed into the cursor?

thanks a lot!
Tom Kyte
April 03, 2008 - 9:09 pm UTC

that is called "un-nesting", it happens as a side effect of nested table unnesting.

http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14260/adobjcol.htm#sthref481

it automatically "knows" how to join

passing a cursor to a pipeline function

debyani, April 03, 2008 - 10:33 am UTC

Tom,
sorry- in the above question I meant mkt.valuecodes_blob column.
Thank you

passing a cursor to a pipeline function

debyani, April 04, 2008 - 9:30 am UTC

Thank you so much Tom!

But how scalable?

Bernard van Niekerk, April 12, 2008 - 7:33 pm UTC

Hi Tom

This is borderline new question but please have a look. Reading this article and practicing what you always say about scalability I've tried to test performance of ref cursors against bulk selects into using your big_table the following:

create or replace package pkg_test
as
type rc_test is ref cursor;
procedure get_result_set( p_min_row in number,
p_max_row in number,
p_result_set in out test.rc_test );
end;
/

create or replace package body pkg_test as
procedure get_result_set( p_min_row in number,
p_max_row in number,
p_result_set in out test.rc_test )
is
begin
open p_result_set
for select object_name
from big_table t1
where t1.id <= p_max_row
and t1.id >= p_min_row;
end;
end pkg_test;
/

set serveroutput on

declare
--for use with first test
--test bulk select into cursor
type c_tab is table of big_table.object_name%TYPE;
tab c_tab;

cursor c1 (p_min_row number, p_max_row number) is
select object_name
from big_table t1
where t1.id <= p_max_row
and t1.id >= p_min_row;

-- for use with second test
-- test ref cursor into list
l_object_name big_table.object_name%type;
p_max_row number;
p_min_row number;
rc2 pkg_test.rc_test;

i pls_integer;
begin
-- an element of caching of results in build into the parameters passed in
runstats_pkg.rs_start;

for i in 1 .. 10000 loop

select t1.object_name bulk collect into tab
from big_table t1
where t1.id <= i+10
and t1.id >= i;

--dbms_output.put_line ('Test1:'||i||':'||tab(1));
--dbms_output.put_line ('Test1:'||i||':'||tab(2));

end loop;

runstats_pkg.rs_middle;

for i in 1 .. 10000 loop

pkg_test.get_result_set (i,i+10,rc2);
loop
fetch rc2 bulk collect into tab;
exit when rc2%notfound;
end loop;
--dbms_output.put_line ('Test2:'||i||':'||tab(1));
--dbms_output.put_line ('Test2:'||i||':'||tab(2));
close rc2;
end loop;
runstats_pkg.rs_stop;
end;
/

with the following results:

Run1 ran in 94 hsecs
Run2 ran in 119 hsecs
run 1 ran in 78.99% of the time

Name Run1 Run2 Diff
.........
STAT...undo change vector size 1,996 2,192 196
STAT...redo size 2,632 2,984 352
STAT...opened cursors cumulati 1 10,002 10,001
STAT...parse count (total) 1 10,008 10,007
LATCH.library cache 20,013 50,052 30,039
STAT...recursive calls 10,002 40,081 30,079
LATCH.library cache lock 2 40,005 40,003

Run1 latches total versus runs -- differnece and pct
Run1 Run2 Diff Pct
95,606 165,824 70,218 57.66%

Obviously there is many more parse counts but the lib cache locks is through the roof. I would have expected ref cursors to be faster.

Tom Kyte
April 13, 2008 - 8:42 am UTC

... I would have expected ref cursors to be faster.
...

let us start with that - why? what was the basis of your hypothesis - what makes you think that

because - ref cursors are a layer on top of implicit cursors.... eg: extra work.

implicit cursors can be cached open - ref cursor no

you should use ref cursors only when you HAVE TO - to return result sets to clients typically - or with native dynamic sql (NDS) when NDS truly is the ONLY option.

assigning an existing cursor to a ref currsor

kulauk, April 17, 2008 - 11:35 am UTC

Tom

I have declared a cursor at a package level in the body and i would like to create a procedure that just shows me that contents of that cursor using a ref cursor.

I can't find any simple syntax that will work.

I can think of more fiddly ways of doing it eg

1)
create a pipelined function that opens and fetches my curosr and have my procedure use that function to assign to a REF CURSOR??

2) or just open and fetch the cursor into sql object types and then open the refcursor using the TABLE syntax?

but i was wondering if there was a quick simple method doing something like below:


create or replace package body pkg_test
as

CURSOR c_my_cur (p1 IN NUMBER)
IS
SELECT *
FROM all_tables
WHERE rownum < p1
;

TYPE c_return_cursor IS REF CURSOR;

PROCEDURE proc1 ( po_cur_data OUT c_return_cursor )

IS

BEGIN

-- what i would like to do here is: (pseudo code coming up)
OPEN po_cur_data FOR c_my_cur (3);


END proc1;

END pkg_test;
/


If there's no syntax to support this can you comment on my 2 ideas for doing it above.

many thanks
Tom Kyte
April 17, 2008 - 4:09 pm UTC

sorry, it just does not work that way.


A reader, May 29, 2008 - 2:41 pm UTC

Can a ref cursor have input parameters?

Like

Open myrefcursor(a1,a2,a3);


2) Can the output of Ref cursor be bulk collected into a Table of records

Please can you give practical examples of whichever that is possible?
Thanks

Ravi
Tom Kyte
May 29, 2008 - 9:36 pm UTC

well, you

open C for select .....

or

open C for 'string'


so, sure - they can be parameterized


SQL> create table t ( c1 int, c2 int, c3 int );

Table created.

SQL>
SQL> declare
  2          cur1 sys_refcursor;
  3          cur2 sys_refcursor;
  4
  5          a1 number := 1;
  6          a2 number := 2;
  7          a3 number := 3;
  8  begin
  9          open cur1 for select * from t where t.c1 = a1 and t.c2 = a2 and t.c3 = a3;
 10          open cur2 for 'select * from t where t.c1 = :x and t.c2 = :y and t.c3 = :z' using a1, a2, a3;
 11  end;
 12  /

PL/SQL procedure successfully completed.




karthick, June 13, 2008 - 8:15 am UTC

There is a procedure that returns a ref cursor. The SQL is build dynamically and the number of columns in the query is not fixed.

After executing the procedure using the PRINT command I am able to print the ref cursor and see the values.

But if used in a PL/SQL block how can I achieve the same. How can I print usinga dbms_output or something else. How can I identify the columns in that ref cursor.

Tom Kyte
June 13, 2008 - 10:14 am UTC

You cannot until release 11g when dbms_sql includes a "ref cursor to dbms_sql cursor" API call.

In 10gr2 and before, unless the ref cursor has a static set of outputs, you cannot process it in plsql. There is no way to describe it, no way to bind it. In 11g when you can convert a ref cursor to a dbms_sql cursor, you have a method to describe it and bind and retrieve the i'th column

You could write a java stored procedure that calls your plsql routine that gets the ref cursor and java could print it out

count of records from ref cursor

Girish, July 02, 2008 - 7:21 am UTC

Hi Tom,

Is it possible to return count of records from a ref cursor.
like c1%rowcount this doesn't seem to work with ref cursor.
Is there any example to show rowcount in ref cursor

Thanks,
Girish
Tom Kyte
July 07, 2008 - 7:01 am UTC

basically, until you fetch the last record - you (and we) do not have any idea how many records will be fetched.

Think about this:

open C for select * from ten_billion_row_table;



That will execute *instantly*, immediately - will take no measurable time whatsoever. It is not that the result set is built AND THEN returned. Most result sets are built on the fly, as the data is retrieved. Unless and until you actually fetch the last record - no one knows how many records there will be.

ref cursor performance

Yoel, September 09, 2008 - 1:38 am UTC

Hi Tom,

In relation to the original question.

I have an sql that finishes in 2.5 minutes if i run it straight in sqlplus.
When i put the sql in a package and executed it. It took a very long time and filling up my temp tbs till i had to cancel it.

Can you enlighten me what could cause this different behavior?

I ran it with the same parameter value. Thanks before for your time.

CREATE OR REPLACE PACKAGE pkg_report
AS
TYPE cursortype IS REF CURSOR;

PROCEDURE rpt_overtime (
p_cursor IN OUT cursortype,
p_company_id VARCHAR2,
p_division_id VARCHAR2,
p_dept_id VARCHAR2,
p_begin_date DATE,
p_end_date DATE
);
END;


CREATE OR REPLACE PACKAGE BODY pkg_report
AS
PROCEDURE rpt_overtime (
p_cursor IN OUT cursortype,
p_company_id VARCHAR2,
p_division_id VARCHAR2,
p_dept_id VARCHAR2,
p_begin_date DATE,
p_end_date DATE
)
IS
BEGIN
OPEN p_cursor FOR....
....
....
END;

Cheers,
Yoel

Tom Kyte
September 09, 2008 - 7:49 am UTC

...
I have an sql that finishes in 2.5 minutes if i run it straight in sqlplus.
When i put the sql in a package and executed it. It took a very long time and
filling up my temp tbs till i had to cancel it.

...


no, that is not really what you have.

What you compared was a sql statement with hard coded literals versus a sql statement using bind variables. The package was what we call a red herring - it isn't relevant, you would see the same using bind variables in "straight sqlplus" as well.


select * from t where x = 5;

is very very very very different form

select * from t where x = ?;

Yoel, September 10, 2008 - 2:16 am UTC

Hi Tom,

I understand the different using the hard coded literal and bind variable are hard parsing and soft parsing, but what else could be different in this case until it caused my temp tbs to fill up (the wait event was 'direct path write temp' at the time).

By the way, i tried running the package again today. it ran just as how the sql ran. weird..

my problem is solved, so my question above is to get to know more about oracle.

appreciate your answer..thank you Tom.

Cheers,
Tom Kyte
September 11, 2008 - 10:38 am UTC

I'll answer the last question

read the documentation
read various forums/blogs
practice
test
play
experiment

Ref Cursor Usage

A reader, October 17, 2008 - 11:32 am UTC

Tom,

I have a package wherein I would like to pass out a ref cursor, but I need a separate procedure to populate that ref cursor and pass it back to the original proc then pass it out. (the sql statements are quite large and I would have a 10000 line procedure if I didn't divide the work). I think that my attempt to pass my OUT cursor as a variable to another proc is not legal. But, I'm not sure how to go about that and I can't find refernce for it.

here is an example:

PROCEDURE multi_foo_pull (p_foo_id IN foo_idx_by, c_multfoo OUT foo_cur)
IS
foo_id_type fofo_id_type;
BEGIN
FOR i IN p_foo_id.FIRST .. p_foo_id.LAST
LOOP
foo_id_type.EXTEND (i);
foo_id_type (i) := p_foo_id (i);
END LOOP;
END;

PROCEDURE get_foo (
p_foo_id IN foo_idx_by,
c_foo OUT foo_cur
)
IS
BEGIN
i := p_foo_id.COUNT;

IF i > 1
THEN
multi_foo_pull (p_foo_id, c_foo);
ELSIF i = 1
THEN
single_foo_pull (p_foo_id, c_foo); <--returns error
END IF;

OPEN c_foo FOR
SELECT *
FROM DUAL;
END;
Tom Kyte
October 18, 2008 - 9:40 pm UTC

sorry, not following the logic here at all - why are you passing c_foo anywhere?

Getting data over a db_link via stored procedure: GTT is the way to go?

Jon Waterhouse, October 29, 2008 - 8:18 pm UTC

I have to get data from Production to my Research database. Since they don't want to give me select privileges on the production tables, I was thinking I could have them give me access to a schema that had had only execute privileges on a stored procedure in another schema (that had select access and the procedure would grab the data I need).

I had thought that the stored procedure could return me a ref cursor and I could then insert that data into a table in my research database (both are 9i).

I think what I have learned here is:
a) that the ref cursor cannot be passed back over a db link, and
b) that the best way to do this is have the procedure write the data into a GTT (on production), and then I grab the data from the GTT.

Do I have that right?

Thanks,

Jon
Tom Kyte
October 30, 2008 - 7:58 am UTC

Why not have a set of VIEWS on the remote site.

Select * from VIEW - instead of a) or b) being even thought about.

That is how much of the rest of the world does it.

IF you could have had a stored procedure returning a ref cursor
THEN
A view could be used to do the same

Also, there are restrictions on global temporary tables in a distributed environment you'll have problems with them

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7002.htm#sthref7490

Great advice, as always

Jon, October 30, 2008 - 11:15 am UTC

Thanks.

To date I've not had to worry over-much about data access issues -- I just have to look after the sand in my own sandbox -- so this is new territory for me. I think it is for most of the production people, too. They manage access levels for users accessing the OLTP app via a VB front end, provide canned reports for some people, and have not really had to consider other types of access.


sys_refcursor Qeustion

A reader, December 10, 2008 - 2:57 pm UTC

CREATE TABLE x (
  id NUMBER,
  cnt NUMBER
);

BEGIN
   FOR i IN 1..1000000 LOOP
      INSERT INTO x VALUES (i, 1000000);
   END LOOP;
END;
/


CREATE OR REPLACE PROCEDURE test_proc (
   p_count     OUT NUMBER,
   p_cursor IN OUT SYS_REFCURSOR
)
AS
   l_SQL LONG;
   l_id  NUMBER;

BEGIN
   l_SQL := 
'SELECT id, cnt
 FROM (
    SELECT id, cnt
    FROM   x
    ORDER  BY id
 )
 WHERE   rownum <= 10 AND
         rownum >= 1';


   OPEN p_cursor FOR l_SQL;
   FETCH p_cursor INTO l_id, p_count;

   OPEN p_cursor FOR l_SQL;
END;
/

set timing on
VAR c REFCURSOR
VAR p_count NUMBER
set autoprint on

exec test_proc(:p_count, :c);

        ID        CNT
---------- ----------
         1    1000000
         2    1000000
         3    1000000
         4    1000000
         5    1000000
         6    1000000
         7    1000000
         8    1000000
         9    1000000
        10    1000000

10 rows selected.

Elapsed: 00:00:00.56

   P_COUNT
----------
   1000000

Elapsed: 00:00:01.17


Question:

In the above code, by opening the cursor twice, does the SQL get executed twice? If yes, is there anyway to get the SQL executed only once, giving that p_count (the OUT parameter) is based on the first record of the result set?


Tom Kyte
December 10, 2008 - 4:04 pm UTC

it is very *very* unclear what exactly you are trying to do here.

what is your goal - don't post code, tell us what you are attempting to accomplish.


this is very suspicious:

WHERE rownum <= 10 AND
rownum >= 1'

you do know if you use any number greater than one in the second rownum filter - you will never get any rows...

sys_refcursor

A reader, December 10, 2008 - 5:32 pm UTC

Sorry for the confusion and the bad example. I was just trying to find a way to pass a result set and an OUT parameter to a Java program. The OUT parameter will always be from a column of the first record of the result set. Of course, the Java program can always fetch the value from the first record of the result set, do some processing based on that value, and then display the entire result set. However, that would mean the program would have to code this extra logic. Instead, I was wondering if I could just encapsulate this logic in a stored procedcure and in a way that does not cause the cursor to be executed twice?

Tom Kyte
December 10, 2008 - 7:17 pm UTC

encapsulate *what* logic exactly??

if you are trying to count the rows in a result set - what an exercise in futility that is. First - to count the rows - ALL ROWS HAVE TO BE SELECTED. So, it will be "less than fast, less than efficient, a complete waste of resources"


A reader, December 10, 2008 - 5:40 pm UTC

SQL> CREATE OR REPLACE PROCEDURE test_proc (
  2     p_cursor IN OUT SYS_REFCURSOR
  3  )
  4  AS
  5     l_SQL   LONG;
  6  
  7  BEGIN
  8     l_SQL := 
  9  'SELECT id, cnt
 10  FROM (
 11     SELECT id, cnt, rownum rn
 12     FROM (
 13        SELECT id, cnt
 14        FROM   x
 15        ORDER  BY id
 16     )
 17     WHERE   rownum <= 10
 18  )
 19  WHERE  rn >= 1';
 20  
 21     OPEN p_cursor FOR l_SQL;
 22  END;
 23  /

Procedure created.

Elapsed: 00:00:00.01
SQL> 
SQL> VAR c REFCURSOR
SQL> VAR p_count NUMBER
SQL> set autoprint on
SQL> set timing on
SQL> 
SQL> exec test_proc(:c);

PL/SQL procedure successfully completed.


        ID        CNT
---------- ----------
         1    1000000
         2    1000000
         3    1000000
         4    1000000
         5    1000000
         6    1000000
         7    1000000
         8    1000000
         9    1000000
        10    1000000

10 rows selected.

Elapsed: 00:00:00.29
Elapsed: 00:00:00.32


Question: Why are there 2 elapsed times here?

Tom Kyte
December 10, 2008 - 7:23 pm UTC

one for running the procedure
another for fetching and printing the ref cursor

sys_refcursor Qeustion

A reader, December 10, 2008 - 9:35 pm UTC

>> if you are trying to count the rows in a result set - what an exercise in futility that is. First - to count the rows - ALL ROWS HAVE TO BE SELECTED. So, it will be "less than fast, less than efficient, a complete waste of resources" <<

Yes. I've read all the discussions on this site about this topic. However, that applies to OLTP systems, right? If I have a reporting system and my SQL has at least one aggregate, would this still apply?

The SQL below would perform approximately the same with or without "COUNT(*) OVER () Total" since it has to process all rows anyways. Isn't it?

CREATE TABLE x (
  id   NUMBER,
  type VARCHAR2(10)
);


BEGIN
   FOR i IN 1..1000000 LOOP
      INSERT INTO x VALUES (i, 'TYPE ' || ROUND(dbms_random.value(1, 100)));
   END LOOP;
END;
/

SQL> CREATE OR REPLACE PROCEDURE test_proc (
  2     p_count     OUT NUMBER,
  3     p_cursor IN OUT SYS_REFCURSOR
  4  )
  5  AS
  6     l_SQL   LONG;
  7     l_type  VARCHAR2(10);
  8     l_dummy NUMBER;
  9  
 10  BEGIN
 11     l_SQL := 
 12  'SELECT type, cnt, Total
 13  FROM (
 14     SELECT type, cnt, rownum rn, Total
 15     FROM (
 16        SELECT type, cnt, COUNT(*) OVER () Total
 17        FROM (
 18           SELECT type, COUNT(*) cnt
 19           FROM   x
 20           GROUP  BY type
 21           ORDER  BY cnt DESC
 22        )
 23     )
 24     WHERE  rownum <= 20
 25  )
 26  WHERE  rn >= 1';
 27  
 28     OPEN p_cursor FOR l_SQL;
 29     FETCH p_cursor INTO l_type, l_dummy, p_count;
 30  
 31     OPEN p_cursor FOR l_SQL;
 32  END;
 33  /

Procedure created.

Elapsed: 00:00:00.02
SQL> 
SQL> set timing on
SQL> VAR c REFCURSOR
SQL> VAR p_count NUMBER
SQL> set autoprint on
SQL> 
SQL> exec test_proc(:p_count, :c);

PL/SQL procedure successfully completed.


TYPE              CNT      TOTAL
---------- ---------- ----------
TYPE 62         10375        100
TYPE 38         10345        100
TYPE 26         10299        100
TYPE 97         10296        100
TYPE 76         10292        100
TYPE 20         10288        100
TYPE 41         10285        100
TYPE 18         10280        100
TYPE 11         10269        100
TYPE 45         10252        100
TYPE 10         10247        100
TYPE 3          10225        100
TYPE 53         10216        100
TYPE 58         10207        100
TYPE 99         10202        100
TYPE 49         10200        100
TYPE 66         10199        100
TYPE 46         10192        100
TYPE 78         10187        100
TYPE 72         10184        100

20 rows selected.

Elapsed: 00:00:00.37

   P_COUNT
----------
       100

Elapsed: 00:00:00.68

Tom Kyte
December 11, 2008 - 7:30 am UTC

... If I have a reporting system and my SQL has at least one aggregate, would this still apply? ...

no, it would always apply.


getting a count of hits - must find the last hit.

getting an aggregate BY something - does not have to find the last row in all cases.


if you are going to count the rows (what an UTTER waste of resources), let the application get the count with the first row and deal with it (or not, it could ignore it too). That is just as easy and even less logic for the application itself.

And it will avoid the "let us run the query to completion twice, just to really annoy the end users and consume as much hardware as humanly possible" issue.

A reader, December 11, 2008 - 11:23 am UTC

>> getting an aggregate BY something - does not have to find the last row in all cases. <<

Hi Tom, can you please show an example of this? Thanks.
Tom Kyte
December 11, 2008 - 9:05 pm UTC

think about grouping by something indexed....

group by <indexed columns>
order by <indexed columns>

ops$tkyte%ORA10GR2> /*
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t;
ops$tkyte%ORA10GR2> create table t
ops$tkyte%ORA10GR2> as
ops$tkyte%ORA10GR2> select * from all_objects
ops$tkyte%ORA10GR2> /
ops$tkyte%ORA10GR2> create index t_idx on t(owner);
ops$tkyte%ORA10GR2> */
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set echo on
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set autotrace on
ops$tkyte%ORA10GR2> select *
  2    from (
  3  select /*+ first_rows(5) */ owner, count(*)
  4    from t
  5   group by owner
  6   order by owner
  7         ) a
  8   where rownum <= 5
  9  /

OWNER                            COUNT(*)
------------------------------ ----------
AQ                                     28
BIG_TABLE                              12
COMMON                                  3
CTXSYS                                312
DBSNMP                                 46


Execution Plan
----------------------------------------------------------
Plan hash value: 112435869

--------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     5 |   150 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY         |       |       |       |            |          |
|   2 |   VIEW                 |       | 40783 |  1194K|     2   (0)| 00:00:01 |
|   3 |    SORT GROUP BY NOSORT|       | 40783 |   677K|     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN    | T_IDX | 40783 |   677K|     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=5)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets<b>
          3  consistent gets</b>
          0  physical reads
          0  redo size
        564  bytes sent via SQL*Net to client
        399  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

ops$tkyte%ORA10GR2> select *
  2    from (
  3  select /*+ first_rows(5) */ owner, count(*)
  4    from t
  5   group by owner
  6   order by owner
  7         ) a
  8   where rownum <= 15
  9  /

OWNER                            COUNT(*)
------------------------------ ----------
AQ                                     28
BIG_TABLE                              12
COMMON                                  3
CTXSYS                                312
DBSNMP                                 46
DMSYS                                 189
EXFSYS                                279
HR                                     34
MAP                                     2
MDSYS                                 863
OLAPSYS                               718
OPS$ORA10GR2                            8
OPS$TKYTE                             193
ORACLE_OCM                              8
ORDPLUGINS                             10

15 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 112435869

--------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |    15 |   450 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY         |       |       |       |            |          |
|   2 |   VIEW                 |       | 40783 |  1194K|     2   (0)| 00:00:01 |
|   3 |    SORT GROUP BY NOSORT|       | 40783 |   677K|     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN    | T_IDX | 40783 |   677K|     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=15)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets<b>
          9  consistent gets</b>
          0  physical reads
          0  redo size
        728  bytes sent via SQL*Net to client
        399  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed

ops$tkyte%ORA10GR2> set autotrace off



use the index, read the data sorted and group - knowing that all of the columns in the group by are already "next to each other"

3 IO's to get the first 5, 9 IO's to get the first 15 and so on..

A reader, December 12, 2008 - 1:12 pm UTC

Thank you. So obvious now that you demonstrated. Can I assume that this will only work in simple cases? If the SQL involves more than a table and also selects columns from more than one table, all data most be retrieved, correct?

Tom Kyte
December 12, 2008 - 4:13 pm UTC

nope - this can work in more complex ones.

think anything that organizes data, for example... partitioning....... What if you group by a partition key - we know that that partition key won't be in any other partition right - so you can in effect hit partition 1 - aggregate - return data AND THEN hit partition 2.


What if the ref cursor return is unkonwn

Chinni, February 16, 2009 - 3:27 am UTC

i Tom,

I have a function which returns a ref cursor. I would like to display the data returned by the ref cursor query. Here from the calling program should i always declare a compatible database before fetching. What I meant is in the below function I am selecting the complete row set of emp table and since I know the return data type of the function i could declare v1 as emp%rowtype and fetch. My question is what if we select different columns(from multiple tables), after joining multiple table, and pass back the query and outside want to display the results.

create or replace function func_ref_test return sys_refcursor
as 
v sys_refcursor;
begin
open v for 'select * from emp';
return v;
end;

declare
v sys_refcursor;
v1 emp%rowtype; -- CAN I AVOID THIS????
begin
v:=func_ref_test;
loop
fetch v into v1;
exit when v%notfound;
dbms_output.put_line(v1.ename);
end loop; 
end;


Please suggest. Thanks for your time
Tom Kyte
February 16, 2009 - 12:31 pm UTC

prior to 11g - this is not possible in plsql.

In 10g and before, when using a ref cursor YOU MUST know the 'shape of it' in plsql.

In 11g, you can dbms_sql.TO_CURSOR_NUMBER a ref cursor (turn a ref cursor into a dbms_sql cursor) and then procedurally describe and process it.

In 10g and before, you would need to have a client program do this (or write a java stored procedure that calls your plsql and then procedurally processes the output if you need this in the server)

Thank you

Chinni, February 16, 2009 - 2:02 pm UTC

Hi Tom,
Thank you clarifying the ref cursor part.
...
In 10g and before, you would need to have a client program do this (or write a java stored procedure that calls your plsql and then procedurally processes the output if you need this in the server)
...
Could you point me to any such document/article if you have on this site?

Thank you for your suggestions
Tom Kyte
February 16, 2009 - 5:14 pm UTC

on what - how to write a java program or a client program? there are thousands, probably billions, of such examples? Not sure what you "want" here?

ref cursor

anupam pandey, March 04, 2009 - 4:15 am UTC

Hi Tom ,

I am trying to encapsulate a sql in a function which return me a ref cursor .Now i wat to loop throug th ref -cursor to get the individual rows ,but i am facing following problem .

demo@WORK>create or replace function get_emps return sys_refcursor
2 is
3 return_value sys_refcursor;
4 begin
5 open return_value for
6 select EMPLOYEE_ID,LAST_NAME from employee ;
7 return return_value;
8 end;
9 /

Function created.

demo@WORK>declare
2 l_id number;
3 l_name varchar2(20);
4 c1 sys_refcursor;
5 begin
6 open c1 for select get_emps from dual;
7 fetch c1 into l_id,l_name;
8 loop
9 exit when c1%NOTFOUND ;
10 dbms_output.put_line ('ID is =='||l_id);
11 dbms_output.put_line ('Name is =='||l_name);
12 End loop ;
13 close c1;
14 end;
15 /
declare
*
ERROR at line 1:
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
ORA-06512: at line 7


demo@WORK>


Please provide any pointr to solution .

Thanks And Regards,
Anupam Pandey

Tom Kyte
March 04, 2009 - 1:18 pm UTC

why in the world would you use a SELECT to do an ASSIGNMENT?

c1 := get_emps;
fetch c1 into ....
loop
    exit when....

Refcursor

A reader, April 23, 2009 - 1:41 am UTC

Good day to you Tom..

I have a doubt regarding refcursor. For example:

VARIABLE employee_info REFCURSOR

BEGIN
OPEN :employee_info FOR SELECT EMPLOYEE_ID
FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN' ;
END;
/

Now, can I use ':employee_info' in a select query, such as

select name from emp where emp_id in (:employee_info) or so?


Tom Kyte
April 27, 2009 - 10:29 am UTC

no, it is already a cursor, a cursor cannot be used as an input to a cursor itself.

OK

Ram, June 03, 2009 - 12:05 pm UTC

Hi Tom,
I am using an OUT parameter of Ref cursor type which is returned to the client. Procedure runs instantaneously
But printing the ref cursor result set(some 500 odd rows) takes a long time. Any instance parameter need to be set for quicker printing/returning the result set to the client?
what can be the reason behind this?
Can you please help me out on this issue
Tom Kyte
June 04, 2009 - 12:25 pm UTC

when you open a cursor - such as "select * from ten_billion_row_table", that happens "right away" - no work needs to be done.

fetching from the cursor does the work. You need to look at your sql query, if you just run it in sqlplus, it'll take a long time to print the 500 rows - but that is the query working.

Tune the query if you think it should go faster than it does. Just be realistic, if the query is something like:

select a, b, c, count(*) from ten_billion_row_table group by a,b,c;

and that returns 500 rows (after aggregating 10,000,000,000 rows....) - it will take a while

Refcursor fetch

Pointers, July 01, 2009 - 1:41 pm UTC

Hi Tom,

I am facing problem in my application. Please help me on this.

I have a package procedure which is called by VB program for sending alerts.
My package sends output to VB program in the form of refcursor. To track what my refcursor is sending to VB, i have created a table and using the same procedure to insert data. i.e. i am fetching the same refcursor in the same packaged procedure and inserting data in to the table.
When i execute the packaged procedure i am getting an error saying 'ora 1002: fetch out of sequence error'.

My sample code goes like this..

CREATE OR REPLACE PACKAGE pkg_test_po
IS
TYPE typ_refcursor IS REF CURSOR;

PROCEDURE mine (t_refcursor OUT typ_refcursor);
END pkg_test_po;


CREATE OR REPLACE PACKAGE BODY pkg_test_po
IS
PROCEDURE mine (t_refcursor OUT typ_refcursor)
AS
a NUMBER;
BEGIN
OPEN t_refcursor FOR
SELECT *
FROM mine;

LOOP
FETCH t_refcursor
INTO a;
insert into mine values(a);

EXIT WHEN t_refcursor%NOTFOUND;
END LOOP;
END;
END pkg_test_po;

As per my analysis on the code, i am thinking that we should not fetch refcurosr in the same procedure where we are outing the refcurosr.
Is this fetching causing problem. If so, can you pls expatiate more theory on this.

I tried by creating another rercursor variable in the same procedure and assgned the refcursor to it and then fetching. But still the same error.

I cant open one more refcurosr for the same query as if i open one more time my results may vary as my base tables are transaction table which gets data every second or minute.

Plese tell me how to insert my cursor rows into a table for tracking before sending to VB program.

Regards,
Pointers

Tom Kyte
July 06, 2009 - 7:14 pm UTC

when you fetch from a cursor, you have fetched from the cursor - no one else will fetch that row from the cursor again.

this seems like a very excessively extremely 'strange' requirement.

why not audit the SQL statement being performed and the SCN as of it being performed (we call that fine grained auditing - you can read all about that in the documentation). Then, you can use flashback query if you wanted to see the data "as of that point in time" within a reasonable amount of time.

Otherwise, you have to open TWO cursors (and their results could be different unless you know what you are doing since you won't be opening them at exactly the same instant, just really close to eachother)

but in short, if someone asked me to do this "record every row sent back to a client", I'd be calling them "crazy"


To the other readers out there: yes, I know of many ways to do this, No - I am not going to mention them - why not? Because I sort of like Oracle to have a change to perform adequately and something like this is just not going to do that.

ref_cursor definition

A reader, July 08, 2010 - 6:34 am UTC

A DEFINITION FROM Burleson Consulting:

http://www.dba-oracle.com/t_pl_sql_ref_cursor_benefits.htm

Definition of a ref cursor:
The name "ref cursor" can be confusing because a ref cursor variable is not a cursor, but a variable that points to a cursor. In essence, a ref cursor allows you to store the result of a "bulk collect" from a table (or any PL/SQL cursor return set) into a PL/SQL table (RAM array) and then reference the array with the ref cursor as a pointer. Here is a simple example of using a ref cursor.
Once you load a PL/SQL table (an in-RAM array), a ref cursor allows you to reference the array within your PL/SQL code without having to pass the array to a new procedure or function. Instead of passing the "baggage" of the actual RAM array, you simple pass the ref cursor, which points to the array values.

Is it not wrong ???
Actually we DON’T STORE THE RESULT SET ANYWHERE IN PGA- UNTILL WE FETCH and also we pass a ref_cursor to other client/program
And there we fetch the data , its not something that we fetch the data into PGA and pass the reference …
Am I correct ?

Tom Kyte
July 08, 2010 - 12:28 pm UTC

it is pretty much very wrong, yes.


It is not a pointer to an array of any sort.

A reader, August 06, 2010 - 2:27 pm UTC

Hi Tom,

The data will be retrieved from the database as we 'fetch'
THROUGH the cursor handle and the cursor points to the each row fetched .


"when you fetch from a cursor, you have fetched from the cursor - no one else will fetch that row from the cursor again. "


Can you please explain more in detail about this ?
Tom Kyte
August 09, 2010 - 12:26 pm UTC

Not sure how else to say it.


You have a cursor, say:


cursor c is select * from emp;


You open it:

open cursor c;



You fetch from it:


fetch c into l_record;


Now, suppose that record is for empno 1234 (and empno is the primary key of that table). You will NOT fetch empno 1234 from that cursor ever again, not until you close it and reopen it. Once a row is fetched from a cursor - it is fetched, you will not fetch it again, you cannot un-fetch it, it is fetched and done.

ORA-06504 error

Tony, September 16, 2010 - 1:19 am UTC

Hi Tom,

I have the following issue.

In my procedure i am using the below statements

open ref1 for m_sql_stmt;
ref2 := ref1

where ref1 is a weak refcursor type and ref2 is a strong ref cursor type

The procedure gets compiled but while running i am getting the below error
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match

If I run the procedure replacing m_sql_smt with static sql there is no error

Can I assign a weak refcursor type to a strong type where weak refcursor is opened using dynamic sql?

Thank you
Tom Kyte
September 16, 2010 - 6:58 am UTC

got an example to work from, ran this in 10g and 11g:

ops$tkyte%ORA10GR2> l
  1  declare
  2      type weak is ref cursor;
  3      type strong is ref cursor return all_users%rowtype;
  4
  5      c1 weak;
  6      c2 strong;
  7
  8      l_rec all_users%rowtype;
  9  begin
 10      open c1 for 'select * from all_users';
 11      c2 := c1;
 12      fetch c2 into l_rec;
 13* end;
ops$tkyte%ORA10GR2> /

PL/SQL procedure successfully completed.

ORA-06504 error

Tony, September 17, 2010 - 1:05 am UTC

Hi Tom,

Thank you for the clarification

Is there any restriction in Oracle 9i? Given below is a sample test case
-----------------------
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL>
SQL>
SQL> create table test1
  2  (f1 varchar2(5),
  3  f2 number(10)
  4  );

Table created.

SQL> insert into test1 values ('abc',10);

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace package test_pkg is
  2     type rectype is record
  3     ( x1 varchar2(5),
  4       x2 number(10)
  5       );
  6
  7  type reftype is ref cursor return rectype;
  8
  9  Procedure Get_Data(iop_data in out reftype);
 10  end test_pkg;
 11  /

Package created.

SQL> create or replace package body test_pkg is
  2      procedure Get_Data(iop_data in out reftype) is
  3          ref1 sys_refcursor;
  4      begin
  5          open ref1 for 'select f1, f2 from test1';
  6          iop_data := ref1;
  7      end Get_Data;
  8  end test_pkg;
  9  /

Package body created.

SQL> declare
  2  m_ref test_pkg.reftype;
  3  m_rec test_pkg.rectype;
  4  begin
  5  test_pkg.Get_Data(m_ref);
  6  loop
  7  fetch m_ref into m_rec;
  8  exit when m_ref%notfound;
  9  null;
 10  end loop;
 11  end;
 12  /
declare
*
ERROR at line 1:
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
ORA-06512: at "UHFBS.TEST_PKG", line 6
ORA-06512: at line 5


SQL> create or replace package body test_pkg is
  2      procedure Get_Data(iop_data in out reftype) is
  3          ref1 sys_refcursor;
  4      begin
  5          open ref1 for select f1, f2 from test1;
  6          iop_data := ref1;
  7      end Get_Data;
  8  end test_pkg;
  9  /

Package body created.

SQL> declare
  2  m_ref test_pkg.reftype;
  3  m_rec test_pkg.rectype;
  4  begin
  5  test_pkg.Get_Data(m_ref);
  6  loop
  7  fetch m_ref into m_rec;
  8  exit when m_ref%notfound;
  9  null;
 10  end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL>

Tom Kyte
September 17, 2010 - 7:09 am UTC

looks like there is - you could have used my very simple small test to confirm that.

when not given a version, I assume relatively current versions.

Refcursor

Julio, November 19, 2010 - 5:45 am UTC

When creating a procedure to return one row and two(or more) columns, it is better to use "select into" or "open for"?

Tom Kyte
November 19, 2010 - 9:38 am UTC

i would prefer select into.


it VERIFIES that at least one and at MOST one row is found.


open for, doesn't - the client would have to do that work

REF CURSOR on stanby

Anand, December 14, 2010 - 9:46 am UTC

Can REF CURSOR be used on read-only standby database (Oracle 10.2.0.4) ?

(Using REF CURSOR on read-only standby gives error. In a procedure, only a SELECT statement is used to open REF CURSOR and it gives error "ORA-16000 database open for read-only access" )

Thank you very much for your help
Anand


Tom Kyte
December 14, 2010 - 1:07 pm UTC

sys%ORA11GR2> alter database open read only;

Database altered.

sys%ORA11GR2> connect /
Connected.

ops$tkyte%ORA11GR2> variable x refcursor;

ops$tkyte%ORA11GR2> exec open :x for select * from dual;
PL/SQL procedure successfully completed.



ops$tkyte%ORA11GR2> exec open :x for 'select * from dual';
PL/SQL procedure successfully completed.



give example please.

Fetch one column from ref cursor

Ammar Kh, November 15, 2011 - 6:09 am UTC

Dear Tom,

I have a reference cursor which is filled with dynamic select statement (Every time the ref cursor has different columns in its result). But this ref cursor ALWAYS has a column named "Record Count".

How can i fetch this column only into a variable ? i need to get the record count from the first row of this ref cursor only.

Something like :
fetch p_ref_cur.record count
into v_count;
close p_ref;

Thank you.
Tom Kyte
November 15, 2011 - 8:57 am UTC

are you in 11g or some earlier release?

In 11g you can "dbms_sql" this cursor and then use dbms_sql to describe the query - get the column names - figure out what column you want and then access just that one.

read about dbms_sql.TO_CURSOR_NUMBER in 11g.

before that, you have to know what column you want.

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