Thanks for the question, Les.

Asked: November 05, 2001 - 4:20 pm UTC

Last updated: May 15, 2004 - 12:27 pm UTC

Version: 8.1.7.0.0

Viewed 1000+ times

You Asked

Good day Tom,

Many thanks for receiving my question regarding ref cursors.

I am working on an application that uses a generic weakly typed ref cursor to return rows to an active server page that uses vbscript to format a web page. I would like to be able to return that same weakly typed ref cursor to a plsql procedure that would display the rows on a web page.

For example, the following creates a table of output with an update and delete link. Active server pages just executes this and receives the data in a record set.

Procedure get_parm_groups
(p_group_name in varchar2 default null,
p_result_set out types.cursor_type)
is

Begin
Open p_result_set for
select pg.group_name,
pg.group_desc,
'<A HREF="parm_group_parms.asp?theRowID='||
urlencode(rowidtochar(pg.rowid))||
'&Refresh=No'||
decode(rec_count.parms_by_group(pg.rowid),0,'">Parameters</A>',
'">Parameters ('||
rec_count.parms_by_group(pg.rowid) ||
')</A>') "Detail Link",
'<A HREF="parm_group_updt.asp?theRowID='||
urlencode(rowidtochar(pg.rowid))||
'&Refresh=No'||
'">Update</A>' "Update Link",
decode(nvl(rec_count.parms_by_group(pg.rowid),0),0,
('<A HREF="parm_group_delete.asp?theRowID='||
urlencode(rowidtochar(pg.rowid))||
'&Refresh=No'||
'">Delete</A>'),
' ') "Delete Link",
'<a href="parm_group_insert.asp?ReFresh=No">New Group</a>' "New Link"
from parm_group pg
where nvl(p_group_name,pg.group_desc) = pg.group_desc
order by lower(pg.group_name);

In active server pages the following happens:

set rs = Server.CreateObject("ADODB.Recordset")
Set rs = cmdStoredProc.Execute

'Fomat the output - Note the Add new Group is in the header
if (not rs.EOF) then
Response.Write ("<h2>Parameter Groups - " & rs (5) & "</h2>" & vbCrLf)
emptyTable = "No"
Response.Write ("<table border = 1>" & vbCrLf)
Response.Write ("<tr>" & vbCrLf)
Response.Write ("<th>Group Name </th>" & vbCrLf)
Response.Write ("<th>Group Desc </th>" & vbCrLf)
Response.Write ("<th>Detail </th>" & vbCrLf)
Response.Write ("<th>Update </th>" & vbCrLf)
Response.Write ("<th>Delete </th>" & vbCrLf)
Response.Write ("</tr>" & vbCrLf)
While (Not rs.EOF)
Response.Write ("<tr>" & vbCrLf)
Response.Write ("<td>" & rs (0) & "</td>" & vbCrLf)
Response.Write ("<td>" & rs (1) & "</td>" & vbCrLf)
Response.Write ("<td>" & rs (2) & "</td>" & vbCrLf)
Response.Write ("<td>" & rs (3) & "</td>" & vbCrLf)
Response.Write ("<td>" & rs (4) & "</td>" & vbCrLf)
Response.Write ("</tr>" & vbCrLf)
rs.MoveNext
Wend
Response.Write ("</table>" & vbCrLf)
else
Response.Write ("<h2>No Parameter Groups Currently Defined</h2>" & vbCrLf)

Please correct me if I am wrong (fingers crossed, as I hope I am getting corrected), but from what I have learned by searching your site amoung other things, is that the out parameter would need to change to an in out parameter and a record type would need to be created to recieve the data in another plsql procedure.

What I am hoping you will tell me is that there is a way to interrogate the ref cursor at run time similiar to the following for dynamic sql (after opening a cursor and doing a parse of a select statement):

dbms_sql.describe_columns(theCursor,v_column_count,v_describe_table);

...or maybe there is a way to extract the select statement from the ref cursor, and re-parse and describe...Or maybe the only way is to copy that select statement out ( Hmmm... 2 things to change in 2 places when something changes) of that procedure and use the dbms_sql package to parse the statement...

I am open to any suggestion that would allow both active server pages to use the procedure, as well as plsql, with a minimum of change.

Again, many thanks for accepting my question and assistance you may offer.

Les Davidner

and Tom said...

You cannot do this with a ref cursor -- not in plsql. You would have to use DBMS_SQL to open the query -- not a ref cursor.

If you do not know the number and types of output elements in the cursor at compile time -- there is simply NO way for plsql to execute the "fetch ref_cursor into <unknown list of variables>".

you need to use the procedural API for dyanmic sql in this case.

The only suggestion I have is to put the procedure in a package (to isolate the code) and create overloaded procedures -- on that returns a ref cursor and one that returns a dbms_sql cursor.

Looking on the bright side - I really truly thing you want to recode that procedure anyway-- that trick you are using:

where nvl(p_group_name,pg.group_desc) = pg.group_desc

is a KILLER -- it is a pretty bad thing. It obviates indexes, forces a full scan. What you should code is:

if ( p_group_name is not NULL )
then
Open p_result_set for
select pg.group_name,
pg.group_desc,
'<A HREF="parm_group_parms.asp?theRowID='||
urlencode(rowidtochar(pg.rowid))||
'&Refresh=No'||
decode(rec_count.parms_by_group(pg.rowid),0,'">Parameters</A>',
'">Parameters ('||
rec_count.parms_by_group(pg.rowid) ||
')</A>') "Detail Link",
'<A HREF="parm_group_updt.asp?theRowID='||
urlencode(rowidtochar(pg.rowid))||
'&Refresh=No'||
'">Update</A>' "Update Link",
decode(nvl(rec_count.parms_by_group(pg.rowid),0),0,
('<A HREF="parm_group_delete.asp?theRowID='||
urlencode(rowidtochar(pg.rowid))||
'&Refresh=No'||
'">Delete</A>'),
' ') "Delete Link",
'<a href="parm_group_insert.asp?ReFresh=No">New Group</a>' "New
Link"
from parm_group pg
where p_group_name = pg.group_desc
order by lower(pg.group_name);
else
Open p_result_set for
select pg.group_name,
pg.group_desc,
'<A HREF="parm_group_parms.asp?theRowID='||
urlencode(rowidtochar(pg.rowid))||
'&Refresh=No'||
decode(rec_count.parms_by_group(pg.rowid),0,'">Parameters</A>',
'">Parameters ('||
rec_count.parms_by_group(pg.rowid) ||
')</A>') "Detail Link",
'<A HREF="parm_group_updt.asp?theRowID='||
urlencode(rowidtochar(pg.rowid))||
'&Refresh=No'||
'">Update</A>' "Update Link",
decode(nvl(rec_count.parms_by_group(pg.rowid),0),0,
('<A HREF="parm_group_delete.asp?theRowID='||
urlencode(rowidtochar(pg.rowid))||
'&Refresh=No'||
'">Delete</A>'),
' ') "Delete Link",
'<a href="parm_group_insert.asp?ReFresh=No">New Group</a>' "New
Link"
from parm_group pg
order by lower(pg.group_name);
end if;

You'll get the best performance that way.

Rating

(6 ratings)

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

Comments

Anyway to get the count from Ref Cursor ?

Ken Chiu, July 26, 2002 - 3:41 pm UTC

Hi tom, thanks in advance for many useful insights.

As the title says, without using a type for the ref cursor to fetch into, is it possible to get the count of records from a ref cursor ?

Can you elaborate more on the APIs ? Do you mean replacing the ref cursor with DBMS_SQL ?

thanks,
ken.

July 28, 2002 - 3:17 pm UTC

The answer is the same for ALL CURSORS -- ref or otherwise.

You can find out the number of rows you have fetched so far with C%rowcount where C is the cursor name/variable.

Until you fetch the last row, no one -- including Oracle -- knows how many rows will be the results. We tend to not pre-answer queries (consider "select * from one_trillion_row_table", we don't count up the answer first and then return rows, we just return rows until we are done)

Yes, I mean -- replace the use of the ref cursor with calls to DBMS_SQL. search this site for dbms_sql for tons of examples. search for dump_csv for a very generic example.

Adding values to ref-cursor

B, September 26, 2003 - 3:01 pm UTC

Hi Tom,

Is their any other way to load data into a refcursor other than the following

CREATE OR REPLACE PROCEDURE return_many_rows_ref(RSET IN OUT test_pack.mycur)
IS
BEGIN
OPEN RSET FOR SELECT * FROM dept;
END;

Say, if I want to do it in a loop like the following, it has only the last value in the table of record as the previous ones gets overwritten (which is valid as I keep on opening itb again)..

FOR I IN table_of_rec.first..table_of_rec.last Loop
open a_ref_cursor for
select b(I).p_price_list as p_price_list,
'2' as p_currency_code,
from dual;
dbms_output.put_line(b(I).p_price_list);
end loop;

Is there a way to this achieve this functionality and retining all the data..

Regards,
Bharath

September 26, 2003 - 8:07 pm UTC

i don't understand the logic here.

a ref cursor is opened via a query -- period. the query can be "select * from PLSQL_FUNCTION" or select * from tables -- but it'll be a query.

i don't understand the psuedo code logic if your loop there at all.

Adding data to ref-cursor

Bharath, September 28, 2003 - 4:24 am UTC

Hi Tom,

Sorry for not being clear in my previous posting..

I have a table of record.. I go through the data in the table of record in a loop.. And what I want to know is
while going through the loop I want to keep adding the data one-by-one to a ref-cursor.. i.e all the data in the table of record should be available in the refcursor..

When I tried, the ref-cursor has only the last row in the table of record(i.e. data fetched by the last loop) as the previous ones gets overwritten, which is valid as I keep on opening the ref-cursor again and again..

I wanted to find out is their anyway the above functionality can be achieved..

And anyways you have said the only way to add data to a ref-cursor is through a query.. which can either select from a PL/SQL Function or a table..

Thanks

September 28, 2003 - 9:27 am UTC

once a row is fetched, it is "fetched", gone, processed, finished.

you would just open two ref cursors with the same defining query.

you might consider changing your logic so as to NOT NEED to read and re-read the same row(s) over and over and over (that would be my choice)

if you are more clear as to your processing needs, I might be able to be more clear as to how to most efficiently approach the problem.

Ref-Cursor

Bharath, September 30, 2003 - 12:22 pm UTC

Tom,
Thanks.. This was required by my friend.. Even he was not sure why his PM wanted it this way.. Anyways he has dropped the idea of getting the data into a ref-cursor.. Again thanks a lot..

Getting Data Into A Ref Cursor

nr, May 13, 2004 - 7:22 pm UTC

Hi Tom, hopefully this clarifies what Bharat was asking:

I have a pl/sql table type, say:

-- in package:
type pkgname.tbltype as number(11);

Then I create a variable of this type and fill it with data:

vtbl pkgname.tbltype := new pkgname.tbltype();
-- add elements
...

Now I'd like to get the contents of this var into a ref cursor. Since this is a pl/sql collection type I can't "cast" as sql table, etc, etc. Let's assume creating a SQL collection type is not an option.

One way I can think of is to create a string of SQL statements in a string var, and open a ref cursor on it, as follows:

sSQL varchar2(1000) := '';
for i in 1..1 loop
sSQL := 'select ' || vtbl(i) || ' from dual';
end loop;

for i in 2..vtbl.last() loop
sSQL := sSQL || ' union all select ' || vtbl(i) || ' from dual';
end loop;

open <ref cursor> for sSQL;

What's a better way?

In my case I'd use this to return the results of running dbms_describe.describe_procedure in a ref cursor: pack all the "table of number" param-values as rows of a ref cursor.

Thanx

--A

May 14, 2004 - 10:06 am UTC

a better way would be to use a type like

SQL> create type myTableType as table of number(11);

so you can do it easily.

Your approach would be a literal "performance killer of the 1st, 2nd, and 3rd degree" as it is the least bind friendly approach.


You could use an application context:


ops$tkyte@ORA9IR2> create or replace context my_ctx using demo_pkg
  2  /
 
Context created.
 
ops$tkyte@ORA9IR2> create or replace package demo_pkg
  2  as
  3      procedure p( p_cursor in out sys_refcursor );
  4  end;
  5  /
 
Package created.
 
ops$tkyte@ORA9IR2> create or replace package body demo_pkg
  2  as
  3      type myTableType is table of number(11);
  4
  5  procedure p( p_cursor in out sys_refcursor )
  6  is
  7      l_data myTabletype := myTableType( 1,2,5,67,74,2,23);
  8  begin
  9      for i in 1 .. l_data.count
 10      loop
 11          dbms_session.set_context( 'my_ctx', 'l_data_' || i, l_data(i) );
 12      end loop;
 13
 14      open p_cursor
 15      for
 16      select to_number(value)
 17        from session_context
 18       where namespace = 'MY_CTX'
 19         and attribute like 'L\_DATA\_%' escape '\';
 20  end p;
 21
 22
 23  end;
 24  /
 
Package body created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable x refcursor
ops$tkyte@ORA9IR2> exec demo_pkg.p(:x)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> print x
 
TO_NUMBER(VALUE)
----------------
              74
               2
               1
              23
               2
               5
              67
 
7 rows selected.

Getting Data Into A Ref Cursor

nr, May 14, 2004 - 5:07 pm UTC

Tom, I know not using bind vars is BAD, but alternatives aren't very palatable because it looks like I can't keep things confined to a package. Either I create a SQL collection type, or I create a SQL context (I should look into whether I can create a context within a package). Ideally I'd like to be able to "select" from a PL/SQL collection type; perhaps that'll happen eventually. (Yes, I realize it only makes sense that an object to be manipulated by SQL be defined in SQL). Anyhoo, thanx for the input.

--A

May 15, 2004 - 12:27 pm UTC

palatable is in the eye of the beholder here I guess.

I mean really -- so what? I cannot keep it in a package. Big spiel.

PLSQL is PLSQL -- procedural language.

SQL is the guy that *needs* to see the stuff here. SQL.

If you want sql access to something, you'll need to use SQL datatypes. PLSQL is a layer on top of SQL, not the other way around.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Anyway to get the count from Ref Cursor ?

Adding values to ref-cursor

Adding data to ref-cursor

Ref-Cursor

Getting Data Into A Ref Cursor

Getting Data Into A Ref Cursor

More to Explore

PL/SQL demos

PL/SQL docs

Parse out columns of a weakly typed ref cursor

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Anyway to get the count from Ref Cursor ?

Adding values to ref-cursor

Adding data to ref-cursor

Ref-Cursor

Getting Data Into A Ref Cursor

Getting Data Into A Ref Cursor

More to Explore

PL/SQL demos

PL/SQL docs