Skip to Main Content
  • Questions
  • Return values from a PL/SQL table into a ref cursor?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Arindom.

Asked: May 26, 2000 - 9:56 am UTC

Last updated: December 06, 2023 - 1:32 pm UTC

Version: 8.1.6

Viewed 50K+ times! This question is

You Asked

How do I return the values from a PL/SQL table
(indexed by BINARY_INTEGER) into a ref cursor?

The contents of the PL/SQL table are NOT returnable by a single
SQL statement. AS it is a PL/SQL table ; I can't do a standard
select (This doesn't work -open TunnelCrs for
'select gw1, gw2 from a')

Currently, to create the ref. cursor am currently doing thefollowing:
open TunnelCrs for
'select '||to_char(a(1).gw1)||','||
to_char(a(1).gw1)||' from dual';

If there are multiple rows ; I am using an 'union all' .


The following is my type and PL/SQL table definitions:
TYPE gw_ttn is record (
gw_id1 INTEGER,
gw_id2 INTEGER
);
TYPE gw_tn is table of gw_ttn index by binary_integer;
TYPE TunnelCursor IS REF CURSOR;
a gw_tn;




Is there a more elegant solution?

and Tom said...

the proper way to do this is to NOT use a PLSQL table type but to use a SQL Object Type instead. It would look like this:


tkyte@OSI1.WORLD> create or replace type myScalarType as object
2 ( x int,
3 y date,
4 z varchar2(25)
5 )
6 /
Type created.

tkyte@OSI1.WORLD> create or replace type myTableType as table of myScalarType;
2 /
Type created.

tkyte@OSI1.WORLD> create or replace
2 function demo_proc( p_start_row in number,
3 p_end_row in number )
4 return myTableType
5 as
6 l_data myTableType := myTableType();
7 l_cnt number default 0;
8 begin
9 for x in ( select * from emp order by sal desc )
10 loop
11 l_cnt := l_cnt + 1;
12 if ( l_cnt >= p_start_row )
13 then
14 l_data.extend;
15 l_data(l_data.count) :=
16 myScalarType( x.empno,
17 x.hiredate,
18 x.ename );
19 end if;
20 exit when l_cnt = p_end_row;
21 end loop;
22
23 return l_data;
24 end;
25 /
Function created.

tkyte@OSI1.WORLD> select *
2 from the ( select cast( demo_proc(2,6) as mytableType )
3 from dual ) a
4 /

X Y Z
---------- --------- -------------------------
7788 09-DEC-82 SCOTT
7902 03-DEC-81 FORD
7566 02-APR-81 JONES
7698 01-MAY-81 BLAKE
7782 09-JUN-81 CLARK

tkyte@OSI1.WORLD>


So, I am recommending you use a SQL type -- not a plsql table type (they work very much the same with the notable exception that the SQL Nested table demands you use .EXTEND to allocate space whereas the plsql table type just "makes room" as needed.

By using the SQL Type, you can select from the table easily. Your ref cursor example would be:



tkyte@OSI1.WORLD> create or replace package my_pkg
2 as
3 type rc is ref cursor;
4
5 procedure p( p_cursor in out rc );
6 end;
7 /

Package created.

tkyte@OSI1.WORLD> create or replace package body my_pkg
2 as
3
4 procedure p( P_cursor in out rc )
5 is
6 l_data myTableType := myTableType();
7 begin
8 for i in 1 .. 3 loop
9 l_data.extend;
10 l_data(i) :=
11 myScalarType( i, sysdate+i, i || ' data');
12 end loop;
13
14 open p_cursor for
15 select *
16 from TABLE ( cast ( l_data as myTableType) );
17 end;
18
19 end;
20 /

Package body created.

tkyte@OSI1.WORLD> set autoprint on
tkyte@OSI1.WORLD> variable x refcursor
tkyte@OSI1.WORLD> exec my_pkg.p(:x)

PL/SQL procedure successfully completed.


X Y Z
---------- --------- -------------------------
1 27-MAY-00 1 data
2 28-MAY-00 2 data
3 29-MAY-00 3 data

tkyte@OSI1.WORLD>


Rating

  (139 ratings)

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

Comments

A reader, April 15, 2001 - 8:46 pm UTC


A reader, April 30, 2001 - 2:22 am UTC


A reader, May 20, 2001 - 6:24 pm UTC


Faris, July 23, 2001 - 12:55 pm UTC


krotov sergey, August 16, 2001 - 11:04 am UTC

the great;the best; in Oracle Forms

Super!!

Maeve, August 29, 2001 - 6:52 am UTC

This is EXACTLY what I was looking for. I have to say that I am WELL impressed with asktom.oracle.com the answers to the questions are easy to understand and the practical examples are brilliant.

Is returning RefCursor better than returning the table itself?

Niranjan Ramakrishnan, August 30, 2001 - 8:07 pm UTC

Is there a performance reason why one would want to return
a ref cursor as opposed to the table itself (and access it
from JDBC using ARRAYs or CustomDatums)?

Tom Kyte
August 30, 2001 - 8:40 pm UTC

Yes, it is better (to return the ref cursor).

If I was to return an array -- i would have to fetch all of the rows on the PLSQL side. With a ref cursor -- the client gets to get at the data right away (don't have to fetch the last row before they see the first row). The application would appear to be faster to the end user.

Additionally, if I was to return an array -- I would have to fetch all of the rows, allocate storage in plsql for them, copy the data there and then send that data back over the network in one big chunk where the client would have to allocate storage to hold the whole thing in memory. With a ref cursor -- i send back a pointer (don't have the overhead of allocating the storage in plsql and copying all of the data into it). I send rows in chunks -- not all at once (10 rows at a time by default -- you control the size). I don't have to allocate memory to hold the entire thing in the client -- just a slice of it.

A ref cursor is infintely more scalable then sending a data structure back and forth for these reasons.

Not only that but the java code to access a result set is trivial as compared to accessing an ARRAY or CustomDatum type.

forgive me, for i have doubts ;-)

frank, August 31, 2001 - 5:24 am UTC

I think the downside of your approach is that you have to define your SQL-Types apart from your pck. this means, that when you -not unlikely- have to change your table/object definition, you have to change the package (to fill the object) as well as the SQLtype.
Can't it all be wrapped _within_ a package ?

(btw GREAT book !)

Tom Kyte
August 31, 2001 - 6:57 am UTC

You must use a SQL type -- you want to use it in SQL right? You want to be able to select from it. In order to be able to select from it -- you must use a SQL type that is registered in the data dictionary. Otherwise SQL will have no idea about it.



Very good

Kamlesh Patel, October 24, 2001 - 2:49 pm UTC

This is what I was looking for. Thanks a lot.

I'm a happy puppy...

Greg Sullivan, December 02, 2001 - 9:18 am UTC

This is exactly what I was looking for...

I'm abstracting specific queries with stored procedures for some applications, due to a possible migration of portions of my application to SAP R/3. Our release date is not in sync with the migration of these tables to the R/3 environment, meaning that, to be prudent, we SHOULD put a layer between our application (which is currently written in PowerBuilder and C++) and R/3.

While knowing that this is this philosophy is the "right thing to do" (hey, I'm the one recommending it! ;-) ) and that "there ought to be a way to force a result set in, regardless of what occurs on the other end", it's certainly comforting to know that I can use a brute force method if need be, while maintaining an elegant reference cursor/SQL interface to the data as long as possible.

In short, thanks. My comfort level has gone up astronomically, thanks to this answer.

Great Solution!!!

R, March 07, 2002 - 11:42 am UTC

Tom,
Based on your great solution to the main question above, How would object types work in a multi user environment?? Suppose more than one users pass some parameters and execute the 'my_pkg' package above, is it going to return the ref cursor result set specific to that user only ??
Another question, Do we have to empty out the object type once the processing is done or does it store data in memory only?? I request you to tell me asap before I use this solution because we have a multi user environment.


Peter, March 11, 2002 - 1:52 pm UTC

Is there any way to make the following declarations within a PL/SQL package?
thanks

tkyte@OSI1.WORLD> create or replace type myScalarType as object
2 ( x int,
3 y date,
4 z varchar2(25)
5 )
6 /
Type created.

tkyte@OSI1.WORLD> create or replace type myTableType as table of myScalarType;
2 /
Type created.



Tom Kyte
March 11, 2002 - 3:34 pm UTC

if you want to use them in SQL, as demonstrated, they must be defined at the SQL layer (as standalone types).

if you don't want to use them in SQL, just want a table of records, we would use a table of records in PLSQL (eg: type array is table of emp%rowtype index by binary_integer; )

Thanks Tom, great

A reader, March 12, 2002 - 5:27 am UTC


Followup

Tim Glasgow, March 14, 2002 - 11:08 am UTC

1.Are these permanent once created or do they only last for the session?
2. Are they like arrays?
3. When using ref cursors is it better to point to the data set in these objects or a regular database table?
4. Can these objects be created using execute immediate?

Tom Kyte
March 14, 2002 - 11:14 am UTC

1) is WHAT permanent? The contents of a collection or the collection types themselves?

the contents -- session specific.
the TYPE itself -- it is there until you drop the type. Consider them a VIEW of sorts.

2) yes, it is fair to think of them as similar to arrays.

3) does not make sense to me.

4) yes but that would be a truly bad idea. You do not want to be doing DDL all over the place. They should be created ONCE per database just like your code is. They should be created at installation time -- period.

Mr Kyte strikes again

Phenoracle, April 04, 2002 - 5:38 pm UTC

Again another reader friendly explanation by Mr Kyte.
Time and time again we as techis look for answers to questions, and it is so refreshing that today there are so many people willing to share their knowledge.
My thoughts are I.T. will evolve at a greater rate due to this.

Many Thanks (Great Book)

Phenoracle

A reader, April 09, 2002 - 4:11 am UTC

Very cute method to do this ,thank you!

What about Pipe?

Joshua Perry, April 11, 2002 - 6:11 pm UTC

What are the performance characteristics of using this REF Cursor method or say pipelined return value PIPE ROW(ROW_TYPE)? Or, are the two methods not related/interchangable?


Tom Kyte
April 11, 2002 - 7:33 pm UTC

You cannot compare them?

You could use a ref cursor to return a pipelined result.
You could use a ref cursor to return the above results (fill the variable, return the variable)

a pipelined function allows the first row of the "collection" to be returned to the client well before the last. A function as above must wait until the last row of the collection is assembled before returning a single byte. So, pipelining allows for more "client/server interaction" where as the above method is "client calls server, server builds entire result and returns it". depends on what you are doing as to which will be more appropriate.

Edric, July 25, 2002 - 7:48 am UTC


Cons of using Ref Cursor

Ken Chiu, July 30, 2002 - 11:19 am UTC

hi Tom,

I agree with all the pros of using a Ref Cursor you mentioned, like not doing the fetch into a pl/sql table. But every request from user means we'll execute the SQL again right ? In our shop we've a store proc where 15 tables are joined and union agained, we'll tune the SQL eventually, but I wonder in case like this and we've many, what is more expensive: the pl/sql table is more expensive or the executing the SQL again on request ? Actually now after typing it I'll do some tests, but please share your opinions.

Thanks as usual.

Tom Kyte
July 30, 2002 - 12:45 pm UTC

SQL is what the database was written to do.

SQL is what the database does best.

See
</code> http://groups.google.com/groups?selm=a1g2kv02r69%40drn.newsguy.com&oe=UTF-8&output=gplain <code>

for another take on this.

You are right, letting the DB do it is faster.

Ken Chiu, July 31, 2002 - 9:34 am UTC

I did my test on comparing the time on fetching into pl/sql table vs. fetching into a SQL type and then CAST it, and yes the SQL type option is much faster,28% on average, in a 30,000 records resultset.

Thanks,
Ken.

Fetching from RefCursor into a SQL Type ?

Ken Chiu, August 10, 2002 - 8:01 pm UTC

hi tom,

I need to retrieve 10 records within the RefCursor, the code sample above works great, but now I have to separate the Select data logic from the return data logic, basically I'll have 1 function that returns the RefCursor, and a procedure to return 10 records from that RefCursor.

But the code below is giving an incosistent type error )ORA-00932):

create or replace type date_obj as object (myDate Date)
/
create or replace type date_obj_tbl as table of date_obj
/

create or replace function rtn_refCursor
Return Std_Utl.generic_refcursor_Typ
Is
l_refCur Std_Utl.generic_refcursor_Typ;
begin
open l_refCur for select sysdate from dual ;

return l_refCur ;
end;
/

declare

l_data date_obj_tbl := date_obj_tbl();
l_refCur Std_Utl.generic_refcursor_Typ ;
begin
l_refCur := rtn_refCursor();

loop
l_data.extend ;
fetch l_refCur into l_data(l_data.count);
Exit when l_refCur%NOTFOUND or l_refCur%NOTFOUND Is NULL ;
end loop ;
Close l_refCur ;
exception when others then
dbms_output.put_line('Sqlerrm=> ' || Sqlerrm );
end ;
/

Why the datatype error ? I'm only fetching a date into a date variable here ?

I tried replacing the fetch line by expliciting giving it the object's member name:
fetch l_refCur into l_data(l_data.count).myDate ;

But the error is then:
ORA-06530: Reference to uninitialized composite.

I'm not sure what I did wrong here.

One last question, instead of returning RefCursor from a Function, I set the refCursor as an OUT parameter, but I don't seem to be able to get data from the RefCursor, is RefCursor not suppose to be used like this ?

Thanks a lot !

Tom Kyte
August 11, 2002 - 9:43 am UTC

The datatype error is that l_data(l_data.count) is not a DATE, it is a "DATE_OBJ".

declare

l_data      date_obj_tbl := date_obj_tbl();
l_refCur    Std_Utl.generic_refcursor_Typ ;
l_tmp       date;
begin
    l_refCur := rtn_refCursor();

    loop
       l_data.extend ;
       fetch l_refCur into l_tmp;
       l_data(l_data.count) := date_obj(l_tmp);
       Exit when l_refCur%NOTFOUND or l_refCur%NOTFOUND Is  NULL ;

will work. or


declare

l_data      date_obj_tbl := date_obj_tbl();
l_refCur    Std_Utl.generic_refcursor_Typ ;
begin
    l_refCur := rtn_refCursor();

    loop
       l_data.extend ;
       l_data(l_data.count) := date_obj(null);
       fetch l_refCur into l_data(l_data.count).myDate;
       Exit when l_refCur%NOTFOUND or l_refCur%NOTFOUND Is  NULL ;



ref cursors most certainly work as OUTS


ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure rtn_refCursor( p_refCur in out std_utl.generic_refcursor_typ )
  2  Is
  3  begin
  4  open p_refCur for select sysdate from dual ;
  5  end;
  6  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2  
  3  l_data      date_obj_tbl := date_obj_tbl();
  4  l_refCur    Std_Utl.generic_refcursor_Typ ;
  5  begin
  6      rtn_refCursor(l_refCur);
  7  
  8      loop
  9         l_data.extend ;
 10             l_data(l_data.count) := date_obj(null);
 11         fetch l_refCur into l_data(l_data.count).myDate;
 12         Exit when l_refCur%NOTFOUND or l_refCur%NOTFOUND Is  NULL ;
 13      end loop ;
 14     Close l_refCur ;
 15  end ;
 16  /

PL/SQL procedure successfully completed. 

Excellent Way

Bhaggs, August 21, 2002 - 7:24 am UTC

I used this to call a cursor object from VB using oracle ole provider..excellent example

Yes, but it's not exactly what I was looking for

Carlos, August 22, 2002 - 3:47 pm UTC

I was needing help to create a Stored Procedure returning a Table which will be the datasource for a Forms block.
That's due to the limitation of Ref Cursor in disallowing DML when using it as datasource...
The problem: If I declare de type in the Procedure as a PL/SQL Table of Records, the trigger query-Procedure wich will populate the block doesn't recognize it; and if I declare the table as a SQL Type, compiling the query-procedure trigger says that RPCs cannot use variables with schema level user-defined type in this release. I'm using Forms Developer 6i and Oracle 8i...

Tom Kyte
August 22, 2002 - 10:43 pm UTC

why do you believe this table of records would not suffer from the same dml "limitations"?

Very helpful but how due you maipulate an object table

Mark McLauchlan, October 17, 2002 - 3:51 pm UTC

I have created my object table and can select from it
but is it possible to delete from the object table where conditions are met by joining to a database table.

Something like

DELETE from "myObjectTable" test
WHERE EXISTS
(SELECT 'X'
FROM table a
WHERE a."column_name" = test."objectAttribute")



Tom Kyte
October 17, 2002 - 4:29 pm UTC

You cannot use INSERT/UPDATE/DELETE DML on a "variable" -- only on tables (real tables, stored on disk)

You procedurally process the variable, just like an array. There are functions like .DELETE you can apply to each element.

Dunno if its related but....

A Reader, October 17, 2002 - 9:10 pm UTC

Tom,
I have a situation where i need to reuse the result of a query. These are expensive queries and consume a lot of resources. So, is there a way so that i can avoid firing them again?

If i have a database function which returns a refcursor can i have the resultset cached such that i can reuse that.
Initially thought of having a PL/SQL table in the database.

regards




Tom Kyte
October 18, 2002 - 8:43 am UTC

global temporary tables.


create global temporary table gtt ( .... ) on commit PRESERVE|DELETE rows;

do that once in your database for each "expensive query type" and then in your code, you will



insert into gtt <select complex query here>;

and now you just use gtt where ever you would have used complex query.

Mark McLauchlan, October 18, 2002 - 6:44 am UTC

What I am trying to do is return a cursor that includes only the records in the object table that don't don't exist in my physical table(allocated_panel_periods -millions of rows). Looking to select a small subset from the object table using a join condition that removes all the records that exist on the system from the result set.

The problem is it always seem to return all records in the object table. Any help would be much appreicated??


FOR c_import_upsite IN
(SELECT *
FROM TABLE (CAST (
gl_import_upsite_tab AS importUpsiteTableType)) import_upsite WHERE NOT EXISTS
(SELECT 'x'
FROM allocated_panel_periods app
WHERE app.contractor_code = import_upsite.contractor_code
AND app.district_code = import_upsite.district_code
AND app.TOWN_CODE = import_upsite.town_code
AND app.SITE_CODE = import_upsite.site_code
AND app.PANEL_CODE = import_upsite.panel_code
AND app.package_id = p_package_id)
) LOOP


DBMS_OUTPUT.put_line ('output1 - ' || _import_upsite.site_code );

END LOOP;


Tom Kyte
October 18, 2002 - 10:16 am UTC

ops$tkyte@ORA920.US.ORACLE.COM> create or replace type myScalarType as object
  2  ( username varchar2(30),
  3    created date,
  4    user_id number
  5  )
  6  /

Type created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create or replace type myArrayType as table of myScalarType
  2  /

Type created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> declare
  2      l_data myArrayType := myArrayType( myScalarType( null, null, null ),
  3                                         myScalarType( 'Hi', sysdate, 55 ) );
  4  begin
  5      select username, created, user_id
  6        into l_data(1).username, l_data(1).created, l_data(1).user_id
  7        from all_users
  8       where username = USER;
  9
 10      for x in
 11      ( select *
 12          from TABLE( cast( l_data as myArrayType ) ) import_upsite
 13         where NOT EXISTS ( select null
 14                              from all_users
 15                             where all_users.username = import_upsite.username
 16                               and all_users.created = import_upsite.created
 17                               and all_users.user_id = import_upsite.user_id )
 18      )
 19      loop
 20          dbms_output.put_line( x.username || ',' || x.created || ',' || x.user_id );
 21      end loop;
 22  end;
 23  /
Hi,18-OCT-02,55

PL/SQL procedure successfully completed.



worked for me (817 on up) -- perhaps P_PACKAGE_ID is not set or not set properly??

 

Tried this?

Mark McLauchlan, October 22, 2002 - 5:48 am UTC

Tom,

I am using Oracle 8.1.7.0.0

Tried your example and NOT EXISTS does not return anything. 

When I change it to EXISTS it returns everything in the object.

SQL> l
  1  declare
  2          l_data myArrayType := myArrayType( myScalarType( null, null, null ),
  3                                            myScalarType( 'Hi', sysdate, 55 ) );
  4      begin
  5          select username, created, user_id
  6            into l_data(1).username, l_data(1).created, l_data(1).user_id
  7            from all_users
  8           where username = USER;
  9         dbms_output.put_line('These are the lines in the array');
 10         dbms_output.put_line( l_data(1).username || ',' ||  l_data(1).created || ',' || l_data(1).use
 11         dbms_output.put_line( l_data(2).username || ',' ||  l_data(2).created || ',' || l_data(2).use
 12         dbms_output.put_line('----------------------------');
 13         dbms_output.put_line('Now select the data required');
 14         for x in
 15         ( select *
 16             from TABLE( cast( l_data as myArrayType ) ) import_upsite
 17            where NOT EXISTS ( select null
 18                                 from all_users
 19                                where all_users.username =
 20  import_upsite.username
 21                                  and all_users.created = import_upsite.created
 22                                  and all_users.user_id = import_upsite.user_id
 23  )
 24         )
 25         loop
 26             dbms_output.put_line( x.username || ',' || x.created || ',' ||
 27  x.user_id );
 28         end loop;
 29*    end;
SQL> /
These are the lines in the array
MARK_DEV_DATA,19-JUN-02,74
Hi,22-OCT-02,55
----------------------------
Now select the data required

PL/SQL procedure successfully completed.

SQL>  

Tom Kyte
October 22, 2002 - 7:29 am UTC

Suggest you look into patching up to 8174 -- the supported release. It would appear to be an issue with 8170 given that I know it works on 8174.

selecting object via view

Mahomed, November 06, 2002 - 7:06 am UTC

I have created an object type in a package and return it via a view as follows:

Create or Replace Type Shm_Row_Type
As Object (
shm_id number(5),
shm_val number(1),
shm_dat date
);
/
show errors;

Create or Replace Type Shm_Type Is Table Of Shm_Row_Type;
/
show errors;

create or replace package pkg_shm
as

procedure init_shm ;

function ret_shm return Shm_Type;

Shm_Tab Shm_Type;

end;
/
show errors;

create or replace package body pkg_shm
as

procedure init_shm
is
BEGIN

pkg_shm.Shm_Tab := Shm_Type();
pkg_shm.Shm_Tab.Extend;
pkg_shm.Shm_Tab( 1 ) := Shm_Row_Type( 1, 9, sysdate );
pkg_shm.Shm_Tab.Extend;
pkg_shm.Shm_Tab( 2 ) := Shm_Row_Type( 2, 8, sysdate + 1);
pkg_shm.Shm_Tab.Extend;
pkg_shm.shm_Tab( 3 ) := Shm_Row_Type( 3, 7, sysdate + 2);



END;

function ret_shm return Shm_Type
is
--
--
BEGIN

return pkg_shm.Shm_Tab ;

END;

end;
/
show errors;

create or replace view vwshm
as Select * From Table( Cast(pkg_shm.ret_shm() As Shm_Type ) )
/

If I run sqlplus in one session, then exec pkg_shm.init_shm,
I can subsequently select * from vwshm and get 3 rows.
If I leave this session running and run sqlplus in another session and select * from vwshm, I get no rows. My question is how can I get another session to see this object? Is this a valid operation in the first place?


Tom Kyte
November 06, 2002 - 7:08 am UTC

Each session has its own "data segment". It would be a really bad thing if that other session could "see" that sessions data (meaning it could change that sessions data -- meaning that package global variables would be virtually USELESS)

If you want all sessions to see that data -- put it in a real table and commit it.

Select specific value

Ken, November 06, 2002 - 11:08 am UTC

How do I select a specific value..say May-28-00 from this table type?

Tom Kyte
November 06, 2002 - 3:24 pm UTC

don't understand the question?

Where clause for cast

A reader, November 06, 2002 - 5:05 pm UTC

I think, Ken want to know how a where clause is applied for cast.

Tom Kyte
November 06, 2002 - 6:30 pm UTC

Just like anything else -- where....


14 open p_cursor for
15 select *
16 from TABLE ( cast ( l_data as myTableType) );
where column_value = .....
17 end;


(column_value is the name given to single column tabletypes built on scalars)

SELECT from Ref cursor

Edgar Chupit, February 12, 2003 - 12:22 pm UTC

Hello Tom,

Is there a posibility to select from function that returns ref cursor. For example:

create or replace package my_pkg
as
type rc is ref cursor;

function p return rc;
end;

create or replace package body my_pkg
as

function p return rc
is
p_cursor rc;
l_data myTableType := myTableType();
begin
for i in 1 .. 3 loop
l_data.extend;
l_data(i) :=
myScalarType( i, sysdate+i, i || ' data');
end loop;

open p_cursor for
select *
from TABLE ( cast ( l_data as myTableType) );

return p_cursor;
end;

end;

Can I now do something like

select * from ( cast ( my_pkg.p ) as ??? )

Tom Kyte
February 12, 2003 - 3:42 pm UTC

No, not really. You would have to write a pipelined function in 9i that fetched the rows and piped them from the ref cursor (so sort of, but not really)

Great idea, but I cann't get Oracle Report to work on the Ref Cursor

Michael, February 14, 2003 - 3:55 am UTC

Hi Tom

I follow your initial example and made a little modification to it so that I can pass a STRONG ref cursor to Oracle Report for getting the column name (required for building the layout).  However, whenever I run the report, it hangs, never return and I have to terminate it (in window 'end task').  Could you please help me out?  

The reason I did this test is that I have project that has an existing procedure that takes in a plsql table A and return anther plsql table B. And I want to be able to run report to format and print out the content of plsql table B.

I am on Oracle 8.1.7.4, running Oracle Report Builder 6.0.8.13.1 on Window NT 4.0

The code is as follow:

On SqlPlus Release 8.1.7.0.0 I did this 

SQL*Plus: Release 8.1.7.0.0 - Production on Fri Feb 14 16:41:01 2003

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

SQL> create or replace type myScalarType as object
  2  (   x     int,
  3      y    date,
  4      z    varchar2(25)
  5  )
  6  /

Type created.

SQL> create or replace type myTabType as table of myScalarType;
  2  /

Type created.

SQL> create or replace package my_pkg
  2   as
  3   type rc is ref cursor;
  4  
  5   type rec_type is record
  6   (  x     int,
  7      y    date,
  8      z    varchar2(25)
  9   );
 10  
 11   type rc_rec is ref cursor return rec_type;
 12  
 13   procedure p( p_cursor in out rc_rec );
 14  end;
 15  /

Package created.

SQL> create or replace package body my_pkg
  2   as
  3  procedure p( P_cursor in out rc_rec )
  4  is
  5      l_data  myTabType := myTabType();
  6  begin
  7      for i in 1 .. 3 loop
  8          l_data.extend;
  9          l_data(i) :=
 10             myScalarType( i, sysdate+i, i || ' data');
 11      end loop;
 12  
 13      open p_cursor for
 14      select *
 15        from TABLE ( cast ( l_data as myTabType) );
 16  end;
 17  
 18  end;
 19  /

Package body created.

The reason that I use rc_rec is that it is a strong ref cursor, so that in the Oracle report I can get the columns and build the layout.  That the only modification!

In Oracle Report, the Query PL/SQL is: 

function QR_1RefCurDS return my_pkg.rc_rec is
  x  my_pkg.rc_rec;
begin
  my_pkg.p(x);
  return x;
end;

As far as I know this is the standard way Oracle Report based its data from a Ref cursor.  I can see all the columns BUT when I run the reports it hangs! no response at all and eventually I had to terminate Report builder!

I went back to sqlplus and tested the package once again.  It works from sqlplus!

SQL> set autoprint on
SQL> variable x refcursor
SQL> exec my_pkg.p(:x)

PL/SQL procedure successfully completed.


        X Y         Z
--------- --------- -------------------------
        1 15-FEB-03 1 data
        2 16-FEB-03 2 data
        3 17-FEB-03 3 data

Could you please help!
Thanks a million.
 

Tom Kyte
February 14, 2003 - 7:42 am UTC

I've never used reports in my life -- have to refer you (in this order) to

o support

o otn.oracle.com -> discussion forums -> developer to see if anyone there has an idea.


but - this is clearly a support issue.

How to "fetch into" dynamically set of columns?

A reader, April 23, 2003 - 6:45 am UTC

Tom,
I am opening a ref cursor based upon argument of table name as:
....
OPEN cv FOR 'SELECT * '||' FROM '||p_tname;
...
1. How I do issue FETCH INTO .....?
as number of columns are different for different tables. If you could also please show me the reference to these INTO variables for further processing.
2. Is it possible to use BULK PROCESSING with ref cursors?
Thanks

Tom Kyte
April 23, 2003 - 7:36 am UTC

you use DBMS_SQL cursors for this -- they can be programatically described, bound to, and fetched from.


yes you can bulk collect with ref cursors.
no you cannot in your case.

but that is OK, dbms_sql allows for an array bind -- if you have my book "Expert one on one Oracle" -- lots of examples in there.

Exactly what I was looking for!!!

D.Marcil, June 02, 2003 - 12:19 pm UTC

Thanks a lot! I was searching everywhere I knew for the answer!
But your site gave me the exact way to do it! (it is not the first time!!!!)

I also wanted to sort that SQL table. I finaly succeeded by using «order by 3,1,5» just like an ORDER BY clause in a UNION select clause.

Thanks !!!

How to convert local PL/SQL table to ref cursor

Sachin, August 12, 2003 - 7:00 am UTC

since all the mentioned solutions were for PL/SQL types defined using CREATE TYPE, what abt the PL/SQL type created locally witin a package as shown below - compiles with error

Package TEST_PKG
IS

type ref_cur_type is ref cursor;
type record_type is record(col1 varchar2(50),col2 varchar2(50));
type table_type is table of record_type index by binary_integer;
PROCEDURE ref_proc
( param2 OUT ref_cur_type);

FUNCTION ref_function
RETURN table_type;

END; -- Package spec

Package Body TEST_PKG
IS

PROCEDURE ref_proc
( param2 OUT ref_cur_type)is
x table_type ;
begin
x := ref_function;
open param2 for select * from table(cast(x as table_type)) ;


end;

FUNCTION ref_function
RETURN table_type
is
x table_type;
begin
x(1).col1 := '1';
x(1).col2 := '2';
x(2).col1 := '3';
x(2).col2 := '4';
return x;
end;

END;

error at the table-cast stmt
----
(1):PLS-00382: expression is of wrong type
(2):PL/SQL: SQL Statement ignored





Tom Kyte
August 12, 2003 - 8:53 am UTC

if you want SQL to see the TYPE -- you need to create the TYPE in SQL -- not in plsql.

a plsql table type is just that - PLSQL only.

PLSQL is "above" SQL, PLSQL can see all things PLSQL and SQL.

SQL is "below" PLSQL. SQL can see all things SQL.



SQL functions on a single-column table type

B. Robinson, October 03, 2003 - 4:32 pm UTC

I've found SQL objects and nested table types to be extremely useful for handling and passing collections of data that are derived from a PL/SQL application (i.e. cannot be selected from a database table).

But I have a slight problem with this table of NUMBER.

SQL> create type numbertable as table of number;
  2  /

I can use TABLE and CAST to SELECT * from it.

But I also want to do things like SELECT SUM(), STDDEV() and other functions on it.  The trouble is that I don't know name what to put within the parentheses.  Will I be forced to create an Object type which holds nothing but a single NUMBER field, or is there a way to use SQL functions on the single nameless NUMBER column as defined above? 

Tom Kyte
October 04, 2003 - 9:40 am UTC

COLUMN_VALUE

is the name of the "magical" column you are looking for.  It's documented -- but the way I found it was to use the fact that a view will expose it for me.

ops$tkyte@ORA920> create or replace type myTblType as table of Number
  2  /
 
Type created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace view v
  2  as
  3  select * from TABLE( myTblType(1,2,3,4) );
 
View created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> desc v
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 COLUMN_VALUE                                 NUMBER

ops$tkyte@ORA920> select sum(column_value) from table( myTblType(1,2,3,4) );
 
SUM(COLUMN_VALUE)
-----------------
               10
 

Extending the table uses memory from which pool?

Dale Ogilvie, February 12, 2004 - 5:57 am UTC

In your example below you call l_data.extend and allocate a new object of myScalarType. From what pool does Oracle grab memory for the new object, and what limits might be reached in extending l_data? (Say for instance that the loop went from 1..3000000)

4 procedure p( P_cursor in out rc )
5 is
6 l_data myTableType := myTableType();
7 begin
8 for i in 1 .. 3 loop
9 l_data.extend;
10 l_data(i) :=
11 myScalarType( i, sysdate+i, i || ' data');
12 end loop;
13
14 open p_cursor for
15 select *
16 from TABLE ( cast ( l_data as myTableType) );
17 end;


Tom Kyte
February 12, 2004 - 8:41 am UTC

it is UGA memory which in

a) dedicated server is PGA (process) memory in the dedicated server memory space
b) shared server is SGA memory, optimally in the large pool but if no large pool was configured will be in the shared pool

so, the limits are bounded by your connection type and then either based on the max size a process can grow to or the size of your SGA components.

Return sql table type into sql select

Steve, February 17, 2004 - 6:55 pm UTC

I am currently using packaged functions to return collections of types which are tables of record types. For example:

CREATE OR REPLACE TYPE my_record_type IS OBJECT
(
column1 VARCHAR2(200)
,column2 VARCHAR2(200)
,summary1 NUMBER
,summary2 NUMBER
,etc.... );

CREATE OR REPLACE TYPE my_table_type IS TABLE OF my_record_type;

I then retrieve my collection with a sql statement similar to the following:

SELECT * FROM TABLE( CAST(
my_pkg.build_sql_table(:parm1, :parm2, etc.... )
AS my_table_type));

where the packaged function builds the collection using dynamic sql and bulk collect.

This works very well as long as I stay within a single instance, but when I try to pass the collection across a database link, it becomes much more difficult. I define my types on both instances (say db1 and db2, where db1 is 9.0.1.4 and db2 is 9.2.0.4), but I get a type mismatch error when I run the select. It seems that it wants me to use the SAME type in each instance, not just an equivalent type. If I try to use type@db1, I get an error saying that types can't be used that way.

There is much, much more to this application than this, but it wouldn't be practical to go into depth on all of the issues. I've been working on the application for several months during which I've read your books and this site extensively and gotten many ideas and solutions to problems along the way, but I haven't found any examples showing how to handle multiple instances.

I remember reading somewhere that collections can be passed across db links whereas ref cursors cannot. The method to do this is probably some simple syntax trick, but I haven't been able to figure it out. Hopefully, you can shine some light on this for me. Thanks in advance.

Steve

Tom Kyte
February 17, 2004 - 7:39 pm UTC

object types are not dblink friendly (like ref cursors).

you can pass PLSQL types back and forth easily however, you just create a synonym for the remote package ( i find that easiest ) and then

is
l_data remote_package.type_name;
....




More on sql type....

Steve, February 17, 2004 - 9:30 pm UTC

Thanks much for the remarkably fast response.

When you say 'not dblink friendly', does that mean that either or both options are not useable at all or that it's not a good idea to use them across a dblink?

Since I'm using the type in a sql statement, it's my understanding that I MUST use a schema type. Is that true? Is it possible to convert my sql type collection to a corresponding pl/sql type before passing it and convert it back to a sql type before using it on the receiving end?

Tom Kyte
February 18, 2004 - 7:42 am UTC

if you want to readily pass collections back and forth across a dblink, you'll be doing it procedurally with plsql types (eg: local procedure calls remote procedure, gets data from remote procedure and packages it up locally)

A reader, February 18, 2004 - 4:35 am UTC


How to bulk collect from refcursor to a table object

Manoj, February 18, 2004 - 12:10 pm UTC

The following compiles well,

But gives the error when you execute it
ERROR at line 1:
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
ORA-06512: at "MTEST.MKG_TEST1", line 13
ORA-06512: at line 1

create type mkgRecordType as object
(institution_id VARCHAR2(10) ,
entity_id VARCHAR2(30) ,
entity_acct_id NUMBER,
USAGE VARCHAR2(30))
/


create or replace type mkgTableType
as table of mkgRecordType
/


create table mkg
(institution_id VARCHAR2(10) ,
entity_id VARCHAR2(30) ,
entity_acct_id NUMBER,
USAGE VARCHAR2(30))
/


create or replace procedure mkg_test(mref OUT SYS_REFCURSOR)
as
begin
open mref for
select institution_id,
entity_id,
entity_acct_id,
USAGE
from mkg;
end;
/

PROCEDURE MKG_TEST1
as
mref sys_refcursor;
l_data mkgTableType :=mkgTableType();
mrec mkg%rowtype;
begin

mkg_test(mref);

loop
l_data.extend ;
l_data(l_data.count) := mkgRecordType(null,null,null,null);
fetch mref bulk collect into l_data;
Exit when mref%NOTFOUND or mref%NOTFOUND Is NULL ;
end loop;
close mref;

end;


Tom Kyte
February 18, 2004 - 9:26 pm UTC

well, they do not match

the ref cursor selects 4 scalars.

the fetch tries to fetch (with a bulk collect no less) all 4 scalars into a single object (not 4 scalars).


whats the goal here, what do you need to do?


But can you bulk collect from an ARRRY into a table

KDB, March 15, 2004 - 6:40 pm UTC

I have a similar problem, howerver I need to buik insert into a table from an array but get a similar error.

Example

RPT_PROP_MERCH_ARRAY ARRAY of ARRAY(2) of VARCHAR2(10)
PROCEDURE prc_merch_qual(on_success OUT NUMBER,
ovc_err_msg OUT VARCHAR2,
id_start_date IN DATE,
id_end_date IN DATE,
iarr_prop_merch IN RPT_PROP_MERCH_ARRAY)
AS
ld_processing_date date;
ln_success number;
ln_success2 number;
lvc_test1 varchar2(10);
lvc_test2 varchar2(10);
BEGIN
--- Insert Records from Array into Table for Better Performance on Queries

for i IN iarr_prop_merch.first..iarr_prop_merch.last
LOOP
insert into mms_gtmp_prop_merch values (iarr_prop_merch(i)(1),iarr_prop_merch(i)(2));
END LOOP;

forall i IN iarr_prop_merch.first..iarr_prop_merch.last
insert into mms_gtmp_prop_merch values (iarr_prop_merch(i)(1),iarr_prop_merch(i)(2));

END;

Thanks
Keith

Tom Kyte
March 15, 2004 - 6:45 pm UTC

not sure what RPT_PROP_MERCH_ARRAY ARRAY of ARRAY(2) of VARCHAR2(10) represents there.

But forall processing is going to want contigous stuff, assuming that is "arrays of arrays", they won't be contigous segments of data. You'll need two arrays there.

How to execute this code

Jennifer Chen, March 24, 2004 - 3:27 pm UTC

Hi Tom,

Your demo code is very helpful. I modified it slightly and am having trouble to test it:

CREATE OR REPLACE TYPE t_rspweb AS OBJECT
(
 request_number        NUMBER(10),
 dob                    DATE,
 date_received          DATE,
 date_sent              DATE,
 nam                    VARCHAR2(30),
 prn                    VARCHAR2(7),
 response_data          CLOB,
 sid                    VARCHAR2(8),
 user_id                VARCHAR2(15),
 viewed_flag            CHAR(1),
 clob_length        NUMBER(10)
)
/

CREATE OR REPLACE TYPE a_rspweb AS TABLE OF t_rspweb
/
SHOW errors

CREATE OR REPLACE PACKAGE alias.qdmsg_pkg
......

CREATE OR REPLACE PACKAGE BODY alias.qdmsg_pkg
AS
   FUNCTION mod_rsp_web (p_userid IN alias.rsp_web.user_id%TYPE)
      RETURN a_rspweb
   AS
      v_data     a_rspweb := a_rspweb ();
      v_length   NUMBER;
   BEGIN
      FOR x IN (SELECT request_number, dob, date_received, date_sent, nam,
                       prn, response_data, SID, user_id, viewed_flag
                  FROM alias.rsp_web
                 WHERE user_id = p_userid)
      LOOP
         v_data.EXTEND;
         v_length := DBMS_LOB.getlength (x.response_data);
         v_data (v_data.COUNT) :=
            t_rspweb (x.request_number,
                      x.dob,
                      x.date_received,
                      x.date_sent,
                      x.nam,
                      x.prn,
                      x.response_data,
                      x.SID,
                      x.user_id,
                      x.viewed_flag,
                      v_length
                     );

         UPDATE alias.rsp_web
            SET viewed_flag = 'Y'
          WHERE request_number = x.request_number;
      END LOOP;

      RETURN v_data;
   END mod_rsp_web;

   PROCEDURE get_rsp_web_by_userid (
      p_userid      IN       alias.rsp_web.user_id%TYPE,
      p_refcursor   OUT      sys_refcursor
   )
   AS
   BEGIN
      OPEN p_refcursor
       FOR
          SELECT /*+ CARDINALITY(T 10 ) */
                 *
            FROM TABLE (CAST (mod_rsp_web (p_userid) AS a_rspweb)) t
           WHERE ROWNUM >= 0;
   END get_rsp_web_by_userid;
END qdmsg_pkg;
/
SHOW errors

SQL> variable c refcursor
SQL> set autoprint on
SQL> exec qdmsg_pkg.get_rsp_web_by_userid('CHEN', :c);
BEGIN qdmsg_pkg.get_rsp_web_by_userid('CHEN', :c); END;

*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "ALIAS.QDMSG_PKG", line 35
ORA-06512: at "ALIAS.QDMSG_PKG", line 77
ORA-06512: at line 1


ERROR:
ORA-24338: statement handle not executed


SP2-0625: Error printing variable "c"

Or if I do this:

SQL> DECLARE 
  2    P_USERID VARCHAR2(200);
  3    P_QUERY_ALL NUMBER;
  4    P_REFCURSOR sys_refcursor;
  5  
  6  BEGIN 
  7    P_USERID := 'CHEN';
  8    P_REFCURSOR := C;  
  9  
 10    ALIAS.QDMSG_PKG.GET_RSP_WEB_BY_USERID ( P_USERID, P_QUERY_ALL, P_REFCURSOR );
 11    COMMIT; 
 13  END;
 14  /
  P_REFCURSOR := C;
                 *
ERROR at line 9:
ORA-06550: line 9, column 18:
PLS-00201: identifier 'C' must be declared
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored

Would you please help?

Thank you for the demo code and this wonderful site. 

Tom Kyte
March 24, 2004 - 4:24 pm UTC

you cannot do modifications in a SELECT like that (unless you use an autonomous transaction but that would probably be a "bug" on your part to do so)

Tell me, what is your "goal" here.

Thank you

Jennifer Chen, March 24, 2004 - 8:04 pm UTC

Hi Tom,

Thank you for taking your time to look into this.

I would need to return the following data set and also modify each viewed_flag in the data set:

SELECT request_number, dob, date_received, date_sent, nam,
prn, response_data, SID, user_id, viewed_flag
FROM alias.rsp_web
WHERE user_id = p_userid

Thanks again for your time and help.

Tom Kyte
March 25, 2004 - 8:51 am UTC

You would have to use an autonomous transaction for that and it would be WHOLLY unreliable.

with an array fetch (most all clients do that) the client might see the first row (but have fetched 10 of them) and never see the next 9.

Or worse (and probably more common) the optimizer will use a plan that "sees" lots more records then are returned -- hence leading to "false" viewed updates.


soooo -- whats the goal, what do you really need to do here? Perhaps a result set is NOT the right way and a stored procedure that feeds a single record using OUT parameters is.

Refcursor as table in queries.

Tony, April 16, 2004 - 2:41 am UTC

Tom,

I've a function returning refcursor. I want to join the result of the refcursor as table with another table. Something like this

SELECT a.col1, a.col2, b.col3
FROM emp a, (refcursor returned by the function) b
WHERE a.deptno=b.deptno;

Is it possible to do so? If yes, How?

Thanks in advance.



Tom Kyte
April 16, 2004 - 7:32 am UTC

you cannot do that.

Thanks a lot !!!!!

Vidya, June 11, 2004 - 2:26 pm UTC

Hi Tom, Thanks a lot for the useful explanations and the brilliant examples. This was exactly what I was looking for.

Very enlightening

BW, July 01, 2004 - 2:49 pm UTC

I've been through this thread forward and backwards and can't find, what to me, should be the simplest of answers.

Using your code example

tkyte@OSI1.WORLD> create or replace package my_pkg
  2  as
  3      type rc is ref cursor;
  4  
  5      procedure p( p_cursor in out rc );
  6  end;
  7  /

Package created.

tkyte@OSI1.WORLD> create or replace package body my_pkg
  2  as
  3  
  4  procedure p( P_cursor in out rc )
  5  is
  6      l_data  myTableType := myTableType();
  7  begin
  8      for i in 1 .. 3 loop
  9          l_data.extend;
 10          l_data(i) :=
 11             myScalarType( i, sysdate+i, i || ' data');
 12      end loop;
 13  
 14      open p_cursor for
 15      select *
 16        from TABLE ( cast ( l_data as myTableType) );
 17  end;
 18  
 19  end;
 20  /

Package body created.

I want to call the procedure from another stored procedure, but I can't for the life of me figure out the proper data type to fetch the ref cursor into.  This compiles.

SQL> CREATE OR REPLACE PROCEDURE test_demo_proc AS
  2  
  3      my_cur  My_Pkg.rc;
  4      my_rec  myScalarType;
  5  
  6  BEGIN
  7      My_Pkg.p (my_cur);
  8      FETCH my_cur INTO my_rec;
  9      
 10      LOOP
 11          EXIT WHEN my_cur%NOTFOUND;
 12          DBMS_OUTPUT.PUT_LINE ('looping');
 13          DBMS_OUTPUT.PUT_LINE  (' X ' || my_rec.x || ' Y ' || my_rec.y || ' Z ' || my_rec.z);
 14      END LOOP; 
 15  END;
 16  /

Procedure created.

SQL> 

But when I run it I get

SQL>  exec test_demo_proc;
BEGIN test_demo_proc; END;

*
ERROR at line 1:
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
ORA-06512: at "TEST_DEMO_PROC", line 8
ORA-06512: at line 1

I also tried making my_rec of the type myTableType.  That also compiles, but I get the same error.  I can't seem to wrap my brain around the data type for the record that the ref cursor fetches into.  This is my first foray into the use of Oracle objects so I have to crawl before I can walk.

 

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

if you look at the sqlplus output:

tkyte@OSI1.WORLD> set autoprint on
tkyte@OSI1.WORLD> variable x refcursor
tkyte@OSI1.WORLD> exec my_pkg.p(:x)

PL/SQL procedure successfully completed.


X Y Z
---------- --------- -------------------------
1 27-MAY-00 1 data
2 28-MAY-00 2 data
3 29-MAY-00 3 data


it is returning 3 scalar columns -- NOT an object type.

you need to fetch into a number, date and string -- 3 columns.

Excellent as always

BW, July 02, 2004 - 2:34 pm UTC

Hmmm. Why do we try to make things so hard??? Just accept the simple answer as the correct one every once in a while. Thanks.

Object Type in dynamic SQL

Steve, July 13, 2004 - 10:50 am UTC

The object type is exactly what I was looking for, however I'm having trouble using it when I need to reference it in a dynamic statement. The simple example below gives "invalid column name" on the cursor open (Ora 8.1.7). Suggestion?

CREATE OR REPLACE TYPE objType AS object (col1 VARCHAR2(10));
CREATE OR REPLACE TYPE tblType AS TABLE OF objType;

DECLARE
myTblType tblType := tblType();
TYPE cur IS REF CURSOR;
myCur cur;
BEGIN
FOR i IN 1 .. 2 LOOP
myTblType.extend;
myTblType(myTblType.Count) := objType('test 1');
END LOOP;

OPEN myCur FOR
'SELECT temp.col1
FROM TABLE (CAST(myTblType AS tblType)) temp';
END;

Tom Kyte
July 13, 2004 - 12:24 pm UTC

.... from table( cast( x: as tblType ) ) temp' using myTblType;

bind it -- it wouldn't work if mytbltype was a number you tried to select from dual either!

OPEN_CURSOR

San, July 14, 2004 - 4:59 pm UTC

When I execute the following query, the number cursors opened increase with the number of rows returned by the query and I get the oracle error "maximum cursor opened".

Is there a way I can reuse the already opened cursor and not hit the maximum OPEN_CURSOR value?


SELECT po.prod_offer_id, pot.dscr, po.qual_req, po.prod_id, pt.dscr
,
CURSOR (SELECT pi.prod_iden_id, pi.parent_id, pit.dscr, pi.VALUE
FROM PROD_IDEN pi, PROD_OFFER_IDEN poi, PROD_IDEN_TYPE pit
WHERE pit.prod_iden_type_id = pi.prod_iden_type_id
AND pi.prod_iden_id = poi.prod_iden_id
AND poi.prod_offer_id = po.prod_offer_id
)
FROM PROD_OFFER po,
PROD_OFFER_TYPE pot,
PROD p,
PROD_TYPE pt,
PROD_OFFER_AUDNC_PRFLE poap
WHERE po.prod_offer_type_id = pot.prod_offer_type_id
AND po.prod_id = p.prod_id
AND pt.prod_type_id = p.prod_type_id
AND po.prod_offer_id = poap.prod_offer_id
AND poap.audnc_prfl_id = 16
AND pt.dscr IN ('LD');

Thanks

Tom Kyte
July 15, 2004 - 11:43 am UTC

as you fetch rows -- you have to close the cursor you just fetched. are you doing that?

reg returning rows from pl/sql procedure across a db link

Ramanan, July 30, 2004 - 3:01 pm UTC

Hi tom,
I executed the example which you had given for passing the plsql table to the remote database. I am getting the error
"remote operations are not permitted on object tables".
What am i missing. I am using oracle 9.0.2 on both the remote machine as well as local machine.

i am attaching the code which i had used below.

In the Remote Machine
*********************
create table CO_PROCESS_SUMMARY
(
PROCESS_ID NUMBER not null,
NT_LOGIN VARCHAR2(20),
APP_ID NUMBER,
PROCESS_TYPE VARCHAR2(15),
PROCESS_STATUS CHAR(1),
PROCESS_DESCRIPTION XMLTYPE,
PROCESS_START_DATE DATE,
PROCESS_END_DATE DATE
)
/

create or replace package ora815_pkg
as
type tblType is table of co_process_summary%rowtype index by binary_integer;

procedure p( x in tblType );

end;
/
create or replace package body ora815_pkg
as

procedure p( x in tblType )
as
begin
for i in 1 .. x.count
loop
insert into t (process_id)
values
( x(i).process_id );
end loop;
end;

end;

In the local server
*******************
declare
l_data ora815_pkg.tblType@dnyitit1_itall.world;
begin
for x in ( select * from co_process_summary@dnyitit1_itall.world )
loop
l_data(l_data.count+1).process_id := x.process_id;
end loop;

ora815_pkg.p@dnyitit1_itall.world(l_data );
end;
/

Tom Kyte
July 30, 2004 - 6:07 pm UTC

you are not using object tables at all so how about a cut and paste from sqlplus showing the exact error and code

reg returning rows from pl/sql procedure across a db link

Ramanan, August 02, 2004 - 1:00 pm UTC

Hi tom,
Thanx for the reply.
Sorry tom, i had missed out giving definition for the table t.
create table T
(
PROCESS_ID NUMBER,
NT_LOGIN VARCHAR2(20),
APP_ID NUMBER,
PROCESS_TYPE VARCHAR2(15),
PROCESS_STATUS CHAR(1),
PROCESS_DESCRIPTION SYS.XMLTYPE,
PROCESS_START_DATE DATE,
PROCESS_END_DATE DATE
);

The below is the code which i execute from sql plus on the local server and also given is the error which i get when i run this.

declare
l_data ora815_pkg.tblType@dnyitit1_itall.world;
begin
for x in ( select * from co_process_summary@dnyitit1_itall.world )
loop
l_data(l_data.count+1).process_id := x.process_id;
end loop;

ora815_pkg.p@dnyitit1_itall.world(l_data );
end;
/
l_data ora815_pkg.tblType@dnyitit1_itall.world;
*
ERROR at line 2:
ORA-06550: line 2, column 19:
PLS-00453: remote operations not permitted on object tables or user-defined
type columns
ORA-06550: line 2, column 19:
PL/SQL: Item ignored
ORA-06550: line 6, column 21:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 6, column 21:
PL/SQL: Statement ignored
ORA-06550: line 9, column 46:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 9, column 12:
PL/SQL: Statement ignored

Regards,
Ramanan

Tom Kyte
August 02, 2004 - 1:09 pm UTC

or user-defined
type columns


SYS.XMLTYPE falls into that category.

reg returning rows from pl/sql procedure across a db link

Ramanan, August 02, 2004 - 2:41 pm UTC

Sorry tom, i didn't get what you were trying to say.
Can you please elborate .

Thanks,
Ramanan

Tom Kyte
August 02, 2004 - 3:36 pm UTC

create table T
(
PROCESS_ID NUMBER,
NT_LOGIN VARCHAR2(20),
APP_ID NUMBER,
PROCESS_TYPE VARCHAR2(15),
PROCESS_STATUS CHAR(1),
PROCESS_DESCRIPTION SYS.XMLTYPE,
PROCESS_START_DATE DATE,
PROCESS_END_DATE DATE
);

you are doing a select * from that remote table.

sys.xmltype is a user defined type.

PLS-00453: remote operations not permitted on object tables or user-defined
type columns


it is erring on the select from the table, not any of the collection manipulations.

reg returning rows from pl/sql procedure across a db link

Ramanan, August 02, 2004 - 6:45 pm UTC

Thanks Tom it worked. But now i need that PL/SQL table to be returned to .NET.
How will i be able to do that.

Regards,
Ramanan

Tom Kyte
August 02, 2004 - 7:32 pm UTC

guess you'd ask the makers of ".nyet" how to do that. sorry -- I've just never touched a line of vb code in my life (assuming that is what you are programming in)

Sharing Procedures between different schema

Ramanan, August 03, 2004 - 12:41 pm UTC

Hi Tom,
We are planning to have a centralised schema where we will have the common data shared between differnt schema.Also we are planning to create procedures in the centralised schema.
The centralised schema will be in a separate server.

From the local schema we will be calling the remote procedures to get common data.

what is the best way to do ?



Tom Kyte
August 03, 2004 - 5:14 pm UTC

the best way is

a) one schema for data
b) another schema (or two or three or four or whatever) for code.
c) in a single server, no distributed complexity, no way I'd made this server by server by server.

separate the code from the schema for security -- grant on the most basic privs needs to the code schemas (select if that is all they need, select/insert if that is what they need and so on)


c) is the most important point.

Sharing Procedures between different schema

Ramanan, August 03, 2004 - 6:22 pm UTC

Thanks Tom. Do correct me regarding point C ,if i am wrong.

Consider the following points.

1. Schema A is the centralised schema where i have all the common data.
2. There are 3 more schemas B,C,D.

I need to write a proc , say proc1 for getting data from table tab1 present in Schema A.

Does this proc1 need to be written separately in B,C and D??
a)in this case, are we not writing the same code again and again for different schemas??
b)I would like to create a common procedure and tell others who working on differnt schemas to use that common procedure by using the database link. is it possible ?.







Tom Kyte
August 03, 2004 - 8:07 pm UTC

you put code into schemas b,c,d -- if b needs something that C provides, C grants B the ability to execute that.

the concept of "b,c,d" is the concept of least privileges -- if you can break the code out into say "read only", "read mostly, little updates", "read/write fully" -- you can gain a level of security by putting read only stuff in the read only schema (which would have NO ability to write to the data at all) for example...

I still don't get the point behind the dblink here....

Sharing Procedures between different schema

Ramanan, August 03, 2004 - 8:48 pm UTC

Thans tom. The common schema will contain user details, privileges for different users etc. the common schema is going to be in a separate server.

The schemas B,C,D will be in different servers. But they need to get the user details, privileges etc from schema A through procedures. This will be done using the database link.

for example,
IN SCHEMA A
we have table user_info containing user_id,name.



consider the procedure
create or replace procedure get_user_info(i_user_id varchar2,user_det sys_refcursor)
is
begin
open user_det for select * from user_info where user_id=i_user_id;
end;

Does the above procedure need to be created in B,C and D.
What i would like to do is create this procedure in the common schema and just call the procedure from B,C and D with the help of database link. Since i will not be able to use the REF cursor. So planning to use PL/SQL table.
The problem now i am having is i have to return this as cursor.

Hope i am clear. I would also like to know whatever i am doing is a right method or not.




Tom Kyte
August 04, 2004 - 8:40 am UTC

sounds like you want to turn on OID (Oracle Internet Directory) or some directory service. It is the year 2004, no need to reinvent this common, standard wheel.

You'd be able to centralize user creation, authentication, roles, etc in an industry standard LDAP repository usable not only by your applications but tons and tons and tons of applications out there.

Now that I know what you are trying to do the answer is now "don't do it"

Sharing Procedures between different schema

Ramanan, August 04, 2004 - 9:51 am UTC

Thanks Tom,
Can you guide me to any document on LDAP..


Tom Kyte
August 04, 2004 - 10:52 am UTC

Iryna Buchynska, October 28, 2004 - 6:07 pm UTC

This code is very helpful.
Thank you very much.


A reader, November 01, 2004 - 1:20 pm UTC


Joe L., November 01, 2004 - 1:23 pm UTC

Thanks Tom you've made my life a lot easier with this example.

Use Scalar Types for multiple commits?

Rob H, February 09, 2005 - 3:53 pm UTC

I have a PL/SQL proc that does some ETL from external Tables into production. I would like it to log to a table. The problem is that I want the procedure to rollback on errors but I need the log to be updated with the results of the process.

Here is a very simplistic version of the proc.

Create or replace procedure etl
as
begin
SAVEPOINT start_transaction;

insert into tab1 (....
insert into log_file (SQL%ROWCOUNT);

delete from tab2 (....
insert into logfile (SQL%ROWCOUNT);

.
.
.

commit;
exception
when other then
rollback;
end;

My problem is that if any of the transactions fail (I've simplified the exception to just others for example) the log is not commited.

I was wondering if I should create a scalar type of the log_file table (logFileType), create a table of type (logFileTableType)and insert into the log_logFileTableType the log entries, wait until the etl is commited or rolled back and then insert logFileTableType into log_file table?

I have no idea the best way to do this....


Tom Kyte
February 10, 2005 - 12:46 am UTC

you could either save the log data in a plsql variable (which means you'll lose it if you "crash" before you save it) or you can use an autonomous_transaction to log and commit it immediately as you go without committing the larger parent transaction:


create procedure logit( p_msg in varchar2 )
as
pragma autonomous_transaction;
begin
insert into log values ....
commit;
end;



Excellent

Rob H, February 10, 2005 - 9:38 am UTC

I was just reading about this in the doc's. I guess my post doesn't really belong here.

You always know how to take something I've over designed into something simple. Its hard to read all the docs and then know the best way to use them (until you post to AskTom....)

Bulk collect from ref cursor into SQL object

John, February 18, 2005 - 10:22 am UTC

Tom,
is it possible to bulk collect from a ref cursor into sqlobject?

Something like this..

declare
l_data mytabletype := mytabletype();

begin

open rc for select * from emp;
fetch rc bulk collect into l_data;

end;

Thanks,

Tom Kyte
February 18, 2005 - 2:04 pm UTC

given:

ops$tkyte@ORA9IR2> create table emp as select empno, ename, deptno from scott.emp where rownum <= 5;
 
Table created.


either of:



ps$tkyte@ORA9IR2> declare
  2          type myArray is table of emp%rowtype;
  3          l_data myArray;
  4          l_rc   sys_refcursor;
  5  begin
  6          open l_rc for select empno,ename,deptno from emp;
  7          fetch l_rc bulk collect into l_data;
  8          close l_rc;
  9          for i in 1 .. l_data.count
 10          loop
 11                  dbms_output.put_line( i || ') ' || l_data(i).ename );
 12          end loop;
 13  end;
 14  /
1) Smith
2) Allen
3) WARD
4) JONES
5) MARTIN
 
PL/SQL procedure successfully completed.


or


ops$tkyte@ORA9IR2> create type myScalarType as object(empno number, ename varchar2(10), deptno number)
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2> create type myTableType as table of myScalarType
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_data myTableType;
  3          l_rc   sys_refcursor;
  4  begin
  5          open l_rc for select myScalarType(empno,ename,deptno) from emp;
  6          fetch l_rc bulk collect into l_data;
  7          close l_rc;
  8
  9          for i in 1 .. l_data.count
 10          loop
 11                  dbms_output.put_line( i || ') ' || l_data(i).ename );
 12          end loop;
 13  end;
 14  /
1) Smith
2) Allen
3) WARD
4) JONES
5) MARTIN
 
PL/SQL procedure successfully completed.
 

Bulk collect from ref cursor into SQL object

John, February 18, 2005 - 3:46 pm UTC

Thanks tom,
2nd method is what i was looking for. Thanks a bunch

Bulk collect from ref cursor into SQL object

John, February 18, 2005 - 5:51 pm UTC

Tom,
One quick question. For the above query, i'm trying to do some computations on a particular column and then sending this info back to .NET using a ref cursor.

open rc for
select * from TABLE(CAST(l_data as mytabtype));

Can I use a where condition on this one.

open rc for
select * from TABLE(CAST(l_data as mytabtype))
where empno is not null;

when i use where condition, i'm no getting any errors , but at the same time, i ma not getting any records too. But if i remove that condition, then i am getting records(and that condition does not filter those records).

Thanks,

Tom Kyte
February 18, 2005 - 6:47 pm UTC

give me a for example I can run to reproduce with.

Bulk collect from ref cursor into SQL object

John, February 19, 2005 - 3:17 pm UTC

Tom,
Sorry, I just tried with some small table and it worked just fine with where clause. I will try again and let you know if i get this problem again.

Thanks,

Bulk collect from ref cursor into SQL object

John, February 19, 2005 - 3:24 pm UTC

It's me again..i forgot to ask you this..how can i do a select from sql plus from this object type after executing procedure?

select * from (select cast(test_proc() as mytabtype) from dual);

If my procedure is test_proc and it's returning a ref cursor?

Thanks,


Tom Kyte
February 19, 2005 - 3:51 pm UTC

if test proc returns a ref cursor, you will not "select" from it like that.

you would:

variable x refcursor
exec test_proc( ...., :x )
print x

ARS

ARS, February 22, 2005 - 11:19 am UTC

The sample code helped me a lot with the reports, but I am encountering another problem.

I am getting ORA-00932: inconsistent datatypes when I call the sproc sp_sprocrpt,
Eg:
exec testpkg.sp_sprocrpt (null,null,null,'2005','F',:rcur);
Pl let me know where I am going wrong.
procedure sp_sprocrpt (t_param1 in varchar2,
t_param2 in number,
t_param3 in varchar2,
t_param4 in number,
t_param5 in varchar2,
t_param6 in varchar2,
t_param7 in char,
select_cursor out t_cursor);

is
m_project_cursor m_cursor;
m_projectdetail_cursor m_cursor;
m_rec project_actual_rec;
m_detailrec project_actual_detail_rec ;
m_report_data rptdetail_rec_collection := rptdetail_rec_collection();

begin


sproc1 (t_param1,
t_parma2,
m_project_cursor);

loop

FETCH m_project_cursor into m_rec;
exit when m_project_cursor%NOTFOUND;


sproc2(m_rec.field1,
m_rec.field2,
m_rec.field3,
m_rec.field4,
m_rec.field5,
m_projectdetail_cursor);


loop

fetch m_projectdetail_cursor into m_detailrec;
exit when m_projectdetail_cursor%NOTFOUND;


m_report_data.extend;
m_report_data(m_report_data.count) := rptdetail_rec(m_rec.field1,
m_rec.field2,
m_rec.field3,
m_rec.field4,
m_rec.field5,
m_rec.field6, m_rec.field65
m_rec.field7,
m_rec.field8,
m_rec.field9,
m_rec.field10,
m_detailrec.field1,
m_detailrec.field2,
m_detailrec.field3,
m_detailrec.field4,
m_detailrec.field5,
m_detailrec.field6,
m_detailrec.field7,
m_detailrec.field8,
m_detailrec.field9);


end loop;

close m_projectdetail_cursor;

end loop;

close m_project_cursor;


open select_cursor for
Field1 ,
Field2 ,
Field3 ,
Field4 ,
Field5 ,
Field6 ,
Field7 ,
Field8 ,
Field9 ,
Field10 ,
Field11 ,
Field12 ,
Field13 ,
Field14 ,
Field15 ,
Field16 ,
Field17 ,
Field18 ,
Field19
from
table (cast (m_report_data as rptdetail_rec_collection));
end sp_sprocrpt;




Tom Kyte
February 22, 2005 - 11:40 am UTC

no clue, no test case!

make it small, remove anything not relevant to the error (eg: spend some of your time on this)

make it complete, include the table, the columns

make it concise, concise being another word for small

but make sure it is standalong, self contained - COMPLETE.


for you see -- no one has any idea or clue what *line* is causing the error. so get it tiny, teeny tiny, small, concise, yet 100% complete.

you'll probably discover your mistake in doing so, that is how I find mine!

Skipping the Temp Table with a REF-CURSOR

Andries Hanekom, June 30, 2005 - 5:49 am UTC

Hi Tom

I have never used a ref-cursor before, I was recently faced with the dilemma where the only obvious solution available to me was to create the date in a PLSQL table via a stored procedure, insert the data to a temp table and then perform a query joining standard tables and the temp table.

With the ref-cursor solution I was able to entirely skip the temp table insert, and could go straight to the select.

Love your work.

Best Regards
Andries Hanekom

excellent Tom

Hawk, August 03, 2005 - 9:11 am UTC


need help

Mrinal Kanti Ghosh, September 03, 2005 - 4:26 am UTC

The questin and solutions are very good. which is very helpfull for me. With this we have developed some dynamic projects.
Now need your help.
Can you please provide some idea, how can we show the BLOB data into the report output?

Tom Kyte
September 03, 2005 - 8:55 am UTC

depends

depends on what is in the blog, word document, spreadsheet, a picture of your friend, etc.


depends on the reporting tool too and what it is capable of doing.

When to return using refcursor vs. pipelined function

Roger, November 08, 2005 - 1:49 pm UTC

Hi Tom,

When would I use a refcursor vs. pipelined function to return lots of rows of a table efficiently from a function.

Don't they both return a pointer to a resultset (set of rows)?

What is the big difference between the two? Thanks for your help.

-Roger

Tom Kyte
November 08, 2005 - 10:28 pm UTC

I would use a refcursor unless I could not.

That is, pipelined functions are something you use when you cannot accomplish it via traditional techniques.


One is SQL - pure SQL.

The other is procedural code being called from SQL.

Ref Cursor vs. Pipeline function

Roger, November 09, 2005 - 10:39 am UTC

Hi Tom,

Thank you for your quick response.

If I use ref cursors to return lots of rows to a client (written say in Java), and the client doesn't always close the cursor, what kind of errors would result from that?

Can I make sure that a ref cursor automatically closes after a duration incase it wasn't close manually? Thanks.

Tom Kyte
November 11, 2005 - 10:02 am UTC

you could get max open cursors exceeded.

no, we do not "time out" ref cursors. the client has all of the control here.

Use of subtype/type

Mahomed Suria, December 05, 2005 - 1:07 pm UTC

Hi Tom,

Is it possible to declare a user defined type base on the standard data type and then subsquently use that type in table definitions?

e.g

"user type" sscode char(18)


then

create table t1 ( a number(11),
s1 sscode
);

create table t2 ( b number(11),
c char (10),
s2 sscode
);

I am told this is something users can do in MS SQL SERVER !!


Thanks.

Tom Kyte
December 06, 2005 - 5:13 am UTC

I am told there are lots of things you can/have to do in sql server that you do not have to do in some other database.

I am sure there are lots of things you can do in Oracle that you cannot do in sql server.

If that were not true, what would be the difference - why would we have more than one?


Hey:

create or replace package.....


do that in SQL Server?

next up, connect by.

or the full suite of analytic functions (lag, lead, row_number, rank et.al.)

Followup

Mahomed Suria, December 06, 2005 - 6:32 pm UTC

Hi Tom,

It was not trying to point out any deficiencies in Oracle compared to SQL Server. I am a fan of Oracle. It is just that I am trying to help a colleague port his application and database from SQL Server to Oracle and I wanted to know if it was possible to do something similar in Oracle.

Tom Kyte
December 07, 2005 - 1:46 am UTC

You have to MIGRATE an application from database to database - not "port". There will be many things different between the two and the right way to do something in Sqlserver is the wrong way to do something in Oracle and vice versa.

This will be the first of many such nuances (and you would have the same problem going the other way)


Wait till you get to concurrency control differences, that'll be eye opening, I hope they have no triggers/code that pretends to try to enforce data integrity constraints - that'll all have to go away and be implemented declaritively.

Nested array

oracleo, December 20, 2005 - 9:20 am UTC

I create an object and then table of that. Then i create an object of that table with one more column and create table of that. The following script did just that.
------------------------------------------
create or replace type ver_doc_data as object(

doc_path VARCHAR2(200),
converted_doc_path VARCHAR2(200),
annexure_required VARCHAR2(200),
doc_remarks VARCHAR2(200),
pl_document_type_id NUMBER(10)
)
/
PROMPT
PROMPT Creating Nested Table Type VER_DOC_DATA_COL
PROMPT *********************************************

create or replace type ver_doc_data_col as table of ver_doc_data;
/

PROMPT
PROMPT Creating Type VER_DOC_DATA_Q
PROMPT ****************************************

create or replace type ver_doc_data_q as object(
product_question_id number(10),
verification_data_doc ver_doc_data_col
)
/

PROMPT
PROMPT Creating Type VER_DOC_DATA_Q_COL
PROMPT ************************************

create or replace type ver_doc_data_q_col as table of ver_doc_data_q;
/

---------------------------------------------------

now can i make it a IN parameter in fucntion like this

CREATE OR REPLACE FUNCTION Fn_Create_Data (
pi_collection ver_doc_data_q_col)

after this how do i refer to values in this type. I need to take the individual values and insert into various tables.

Also, this function will be called from java programme.
what would be the best way of doing this.

Thanks for your time .

Tom Kyte
December 20, 2005 - 9:54 am UTC

Have you read the application developers guide for objects?
</code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14260/toc.htm

And you would be very interested in jpublisher as well.
http://docs.oracle.com/docs/cd/B19306_01/java.102/b14188/toc.htm <code>

(my recommendation - use "real" tables, don't get caught up in "objects" because they are cool....)




Joining | Number and Array

Rahul Dutta, December 26, 2005 - 11:18 am UTC

Hi Tom,

I am having these two table Master and Tran. My Problem is to create a view which will show ENAME and JOB. 

How can I join EMPNO in Master table with EMPNO in Tran table which is in the form of Array.

SQL>  select * from master;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD

SQL> select * from tran;

EMPNO                                JOB
----------------------------------- ----------------
7788,7902,7934                       ANALYST
7369,7876,7900                       CLERK
7499,7521,7654,7844                  SALESMAN
7521                                 MANAGER
7900                                 PRESIDENT

Please help me in choosing the right approach.

Thanks a lot.

Rahul  

Tom Kyte
December 26, 2005 - 12:49 pm UTC

the right approach is only to fix your data model.

You don't have an "array", you have a string - and if it were a varray/collection - it would still be "wrong"

SQLPLUS and a function that returns a ref cursor

James, January 09, 2006 - 1:27 am UTC

This has been a very usefull thread and I almost fell off my chair when I ran the following:

--------------------------------------------------

create or replace function test_rc_n return SYS_REFCURSOR
is

rc SYS_REFCURSOR;

begin

open rc for
select object_name from all_objects where rownuM<20;

return rc;

end;
/

select test_rc_n from dual;

TEST_RC_N
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

OBJECT_NAME
------------------------------
/1005bd30_LnkdConstant
/10076b23_OraCustomDatumClosur
/10297c91_SAXAttrList
/103a2e73_DefaultEditorKitEndP
/1048734f_DefaultFolder
/10501902_BasicFileChooserUINe
/105072e7_HttpSessionBindingEv
/106ba0a5_ArrayEnumeration
/106faabc_BasicTreeUIKeyHandle
/10744837_ObjectStreamClass2
/1079c94d_NumberConstantData
/10804ae7_Constants
/108343f6_MultiColorChooserUI
/10845320_TypeMapImpl
/10948dc3_PermissionImpl
/1095ce9b_MultiComboBoxUI
/109a284b_OracleXMLStaticQuery
/109cbb8e_SpanShapeRendererSim
/10a45bfe_ProfilePrinterErrors

19 rows selected.

----------------------------------------

What is happening here? What is SQLPLUS *really* doing here?

Also, you've said that you like REF CURSORS to be in/out parameters to procedures but can you think of any serious problems if I make a function return a REF CURSOR? Perhapse clients other than SQLPLUS support the package in/out method better?

--not on topic :-)
Anyway, hope you had a holiday (!) and can spare a few words answering this question. Honestly, I've posted some dubious questions in the past but even the shortest of your answers have helped!

Tom Kyte
January 09, 2006 - 8:00 am UTC

sqlplus is just printing results of a query - you could just as easily execute:


select cursor(select object_name from all_objects where rownum <= 20 ) from dual;

so now cursor is a function?!

James, January 09, 2006 - 9:52 am UTC

I didn't know you could do that!

The reason I coded the test_rc_n function was to start testing ref cursors for an API I was developing. I had been wanting to use collections in my API as existing code was using bulk collects (in PL/SQL) and I thought it would be much faster to bulk collect and then provide the collection via the API. This thread has highlighted some problems with using collections this way and it has also proven hard to get other clients to understand collections - they all seem to want to know about REF CURSORS.

The problem I saw that if we code an API using REF CURSORS we are at the mercy of how a client fetches this data. In the old PL/SQL application we opened the cursor and proceded to bulk collect the data into a collection - full control of fetching, most efficient processing of the data (but useless outside of pl/sql)

My burning question is thus:

Would you recommend the REF CURSOR approach to providing an API to return results from an Oralce Database to an arbitrary (lets say non Oracle) client?

Regards

Tom Kyte
January 09, 2006 - 9:58 am UTC

Would I recommend it? Definitely - here is a blurb from Effective Oracle by Design regarding this:

<quote>
Returning Data

There is a rumor that Oracle does not support returning result sets from stored procedures. It does seem that every SQL Server programmer who uses Oracle has fallen into this trap. PL/SQL can return result sets, and it is no harder (or easier) to do than it is in other databases. It is just different. In Oracle you use a ref cursor (a pointer to a cursor). This is a feature that has been available with Oracle since version 7.2 of the database (introduced in 1995).

Advantages of Ref Cursors

In general, using a ref cursor is the optimum method for returning results to clients. The reasons for this are as follows:

· Ease of programming&#8195;Every language can deal with a result set—a cursor.
· Flexibility&#8195;The client application can choose how many rows at a time to fetch. Rather than send back 10,000 items in an array, you send back a result set that the client can fetch from 10 items at time.
· Performance&#8195;You do not need to have PL/SQL fetch the data from a cursor, fill up an array (allocating memory on the server), and send the array to the client (allocating memory on the client), making the client wait for the last row to be processed before getting the first row. Instead, a ref cursor will let you immediately return data to a client without doing any of that.
So, for reasons very similar to limiting the bulk collection size, you want to use ref cursors as opposed to PL/SQL table types or SQL collections to return result sets to client applications.


Use Ref Cursors to Return Result Sets

As an example of where ref cursors are suitable, letÂ’s use a Java client that fetches data from a copy of ALL_OBJECTS. We will code this once using PL/SQL table types and once using ref cursors.

The following is the package specification for our example. It has an INDEX_BY routine that takes as input an OWNER name and returns as output three columns.

scott@ORA920> create table t
2 as
3 select * from all_objects;
Table created.

scott@ORA920> create or replace package demo_pkg
2 as
3 type varchar2_array is table of varchar2(30)
4 index by binary_integer;
5
6 type rc is ref cursor;
7
8 procedure index_by( p_owner in varchar2,
9 p_object_name out varchar2_array,
10 p_object_type out varchar2_array,
11 p_timestamp out varchar2_array );
12 procedure ref_cursor( p_owner in varchar2,
13 p_cursor in out rc );
14 end;
15 /
Package created.

You can see how this approach gets unwieldy for large (wider) result sets quickly. The ref cursor interface, on the other hand, simply takes as input the OWNER to search for and returns a single ref cursor that can select as many columns as you like.

Now, letÂ’s move onto the package bodies for the implementation.

scott@ORA920> create or replace package body demo_pkg
2 as
3
4 procedure index_by( p_owner in varchar2,
5 p_object_name out varchar2_array,
6 p_object_type out varchar2_array,
7 p_timestamp out varchar2_array )
8 is
9 begin
10 select object_name, object_type, timestamp
11 bulk collect into
12 p_object_name, p_object_type, p_timestamp
13 from t
14 where owner = p_owner;
15 end;
16
17 procedure ref_cursor( p_owner in varchar2,
18 p_cursor in out rc )
19 is
20 begin
21 open p_cursor for
22 select object_name, object_type, timestamp
23 from t
24 where owner = p_owner;
25 end;
26 end;
27 /
Package body created.

Here, the INDEX_BY routine uses BULK COLLECT to fetch all of the data. The REF_CURSOR routine simply does an OPEN.

The Java client for the INDEX_BY routine might look like the following. WeÂ’ll start with a very simple timing routine that will print out elapsed times in milliseconds between calls.

import java.sql.*;
import java.util.Date;
import oracle.jdbc.driver.*;
import oracle.sql.*;

class indexby
{

static long start = new Date().getTime();
public static void showElapsed( String msg )
{
long end = new Date().getTime();

System.out.println( msg + " " + (end - start) + " ms");
start = end;
}

Every time we call that routine, it will print the elapsed time since the last time we called it, and then remember this new last time.
Next, letÂ’s look at the main routine. We begin by connecting to Oracle.

public static void main(String args[])throws Exception
{
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());

Connection conn=DriverManager.getConnection
("jdbc:oracle:oci8:@ora920.us.oracle.com","scott", "tiger");

showElapsed( "Connected, going to prepare" );

Then we prepare a call to the INDEX_BY routine in the DEMO_PKG. We will bind SYS to the first input, and then define the output PL/SQL index by tables, one by one.

OracleCallableStatement cstmt =
(OracleCallableStatement)conn.prepareCall
( "begin demo_pkg.index_by(?,?,?,?); end;" );

showElapsed( "Prepared, going to bind" );
int maxl = 15000;
int elemSqlType = OracleTypes.VARCHAR;
int elemMaxLen = 30;

cstmt.setString( 1, "SYS" );
cstmt.registerIndexTableOutParameter
( 2, maxl, elemSqlType, elemMaxLen );
cstmt.registerIndexTableOutParameter
( 3, maxl, elemSqlType, elemMaxLen );
cstmt.registerIndexTableOutParameter
( 4, maxl, elemSqlType, elemMaxLen );

Notice that we are setting three elements: MAXL, which is the maximum number of “rows” we are prepared to deal with, the maximum size of our array; ELEMSQLTYPE, the datatype of each output array; and ELEMMAXLEN, the maximum width of each array element we anticipate.

Next, we execute the statement. After executing the statement, we retrieve the three arrays of data representing our result set.

showElapsed( "Bound, going to execute" );
cstmt.execute();

Datum[] object_name = cstmt.getOraclePlsqlIndexTable(2);
Datum[] object_type = cstmt.getOraclePlsqlIndexTable(3);
Datum[] timestamp = cstmt.getOraclePlsqlIndexTable(4);

Then we simply access each one in turn to show how long it takes to go from the first row to the last row in this result set.

showElapsed( "First Row "+object_name.length );
String data;
int i;
for( i = 0; i < object_name.length; i++ )
{
data = object_name[i].stringValue();
data = object_type[i].stringValue();
data = timestamp[i].stringValue();
}
showElapsed( "Last Row "+i );
}
}

The first time I ran this, I used 10,000 instead of 15,000 for MAXL (the maximum array length). Here is what I discovered:

$ java indexby
java.sql.SQLException: ORA-06513: PL/SQL:
index for PL/SQL table out of range for host language array
ORA-06512: at line 1

I guessed wrong. The client undersized the array, so it received an error instead of data. Using this approach, the client needs to know the maximum number of rows as well as the maximum column width for each column. That is information you may not have at compile time.

Now we can look at the REFCUR class. The first half of this code is identical (except for the class name) to the INDEXBY class, up to the code immediately after the connect. WeÂ’ll pick it up there, where we start by setting the row prefetch size (the array size). It defaults to 10 for JDBC, but I generally use 100.

showElapsed( "Connected, going to prepare" );
((OracleConnection)conn).setDefaultRowPrefetch(100);

Now, we prepare and bind the statement just as with the PL/SQL tables, using the syntax for ref cursors instead of index by tables.

OracleCallableStatement cstmt =
(OracleCallableStatement)conn.prepareCall
( "begin demo_pkg.ref_cursor(?,?); end;" );

showElapsed( "Prepared, going to bind" );
cstmt.setString( 1, "SYS" );
cstmt.registerOutParameter(2,OracleTypes.CURSOR);

Then we execute the statement and get the result set. Again, we print the time to get the first row and the last row after touching each column of every row in between.

showElapsed( "Bound, going to execute" );
cstmt.execute();
ResultSet rset = (ResultSet)cstmt.getObject(2);

if ( rset.next() )
showElapsed("First Row");

String data;
int i;
for( i = 1; rset.next(); i++ )
{
data = rset.getString(1);
data = rset.getString(2);
data = rset.getString(3);
}

showElapsed("Last Row "+i );

Table 9.1 provides a summary of the results of running these two versions.

Wait Time INDEXBY REFCUR Difference
Time to first row 825ms 25ms (800)ms
Time to last row 1,375ms 860ms (515)ms
Total time to fetch all rows 2,200ms 885ms (1,315)ms

Table 1: Comparing the PL/SQL table and reference cursor techniques for returning results

LetÂ’s go a step further and add a table to the mix to keep statistics on the PGA and UGA memory use in the server.

scott@ORA920> create table stats ( which varchar2(30), uga number, pga number );
Table created.

And weÂ’ll add this SQL statement after the last showElapsed in each Java routine (replacing the indexby with ref_cursor in the other routine):

Statement stmt = conn.createStatement();
stmt.execute
( "insert into stats "+
"select 'indexby', "+
"max(decode(a.name,'session uga memory max',b.value,null)) uga, "+
"max(decode(a.name,'session pga memory max',b.value,null)) pga "+
"from v$statname a, v$mystat b "+
"where a.name like '%memory%max' "+
"and a.statistic# = b.statistic# " );

WeÂ’ll see that the INDEXBY approach consumes a large amount of memory as compared to the ref cursor approach.

scott@ORA920> select which, trunc(avg(uga)), trunc(avg(pga)), count(*)
2 from stats
3 group by which
4 /

WHICH TRUNC(AVG(UGA)) TRUNC(AVG(PGA)) COUNT(*)
--------------- --------------- --------------- ----------
indexby 76988 4266132 9
ref_cursor 76988 244793 9

This shows that over nine runs, the INDEXBY approach consumed 4.2MB of RAM on the server. The ref cursor approach used a rather small 244KB of RAM to accomplish the same thing.
</quote>

Excellent -

A reader, January 09, 2006 - 3:25 pm UTC

Thank you, thank you, thank you!! Your testcases and measurement techinques are ingenious. It gives such an intelluctual pleasure to read you Tom. Wonderful!

what about the parsing problem...

James, January 10, 2006 - 1:09 am UTC

I wrote a small test in PL/SQL that did a collection API versus ref cursor API test and this is the summary of the trace:

--collections built with bulk collected native sql
--with the collection passed back to the calling pl/sql program:
call count
------- ------
Parse 0
Execute 72
Fetch 72
------- ------
total 144

--ref cursor created via api and passed back to calling sql program with subsequent bulk collected fetch:
call count
------- ------
Parse 72
Execute 72
Fetch 72
------- ------
total 216

You definitly discuss this issue in the question "Parsing with Ref cursor" </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:904605517791 <code>

It appears that my REF CURSOR API is not as good as it could be for PL/SQL applications. Infact I should write *another* API that uses collections for use in PL/SQL for best performance.

Interestingly though, the REF CURSOR API ran marginally quicker and used less CPU than the COLLECTION API!

Would you say that the REF CURSOR API is still appropriate for use by PL/SQL applications when there appears to be a better soloution with regards to parsing?

Regards





Tom Kyte
January 10, 2006 - 8:06 am UTC

above I was comparing:

a) open a QUERY, fetch all of the data, bundling it into an array, return to client

vs

b) open a QUERY, return to client.


You are comparing apples to flying toaster ovens here.


You are comparing parsing a string and returning results to running a query against data in a table and returning results.

So used bulk collect or ref cursor?

Sejas Patel, January 10, 2006 - 4:56 pm UTC

Hi Tom,

James above might have confused some of the comparison but there was a nice question.

<quote>
Would you say that the REF CURSOR API is still appropriate for use by PL/SQL applications when there appears to be a better soloution with regards to parsing?
</quote>

Now forgetting about the "Parsing" bit in the question, what should be the prefferred method to use while:

a. Oracle is being used as a RDBMS only and another client handles the data collection.

b. serving web apps via mod_plsql.

c. a combination of both a and b

I guess the core information James and me want is that, we would like to know the pros and cons of using both methods, as you demonstrated above, in either of the environment, which is pl/sql based and third-party client.

But seems like you earlier made your point in using ref cursors when it comes to " external clients" accessing oracle to retrive data.

Now within pl/sql i would like to create an API for internal use (mod_plsql or pure oracle based reports in batch).

So would you use bulk collect in table types/plsql types etc., OR pass on ref cursors to the parent program?

Can you please give us the clear picture of what should be done in our case where the core method of data retrieval is undecided to start with.

Hope to get some apples to apples comparison.

:)

Tom Kyte
January 10, 2006 - 7:32 pm UTC

a) ref cursors (the way to return a result set to a client)
b) just cursors (the way a client that opens a result set would process the data)




collection vs ref cursor

James, January 10, 2006 - 6:47 pm UTC

Hi Tom,
Hopefully from the following code it will be clear what I have been testing.

create or replace type emp_dept_type as object(name varchar2(2000),dept varchar2(2000))
/
create type emp_dept_Tabtype as table of emp_dept_type
/
create or replace type dept_type as object(id number,dept varchar2(2000))
/
create type dept_Tabtype as table of dept_type
/

create or replace package test_api
is

function getDeptTable return dept_Tabtype;
function getEmpTable(dept_id number) return emp_dept_Tabtype;

procedure getDeptCursor(deptCursor in out SYS_REFCURSOR);
procedure getEmpCursor(dept_id number, empCursor in out SYS_REFCURSOR);

end test_api;

/

create or replace package body test_api
is

function getDeptTable return dept_Tabtype
is
dept_t dept_Tabtype;
begin

select dept_type(DEPTNO,DNAME)
bulk collect
into dept_t
from dept;

return dept_t;

end getDeptTable;

function getEmpTable(dept_id number) return emp_dept_Tabtype
is
emp_t emp_dept_Tabtype;
begin

select emp_dept_type(ENAME,DNAME)
bulk collect
into emp_t
from emp,dept
where emp.deptno=dept.deptno
and dept.deptno=getEmpTable.dept_id;

return emp_t;

end getEmpTable;

procedure getDeptCursor(DeptCursor in out SYS_REFCURSOR)
is
begin

open DeptCursor for
select deptno,dname
from dept;

end getDeptCursor;

procedure getEmpCursor(
dept_id number,
EmpCursor in out SYS_REFCURSOR)
is
begin

open EmpCursor for
select ENAME,DNAME
from emp,dept
where emp.deptno=dept.deptno
and dept.deptno=getEmpCursor.dept_id;

end getEmpCursor;

end test_api;

/

Now the API package has been created lets test using collections and ref cursors...

alter session set sql_trace=TRUE;
alter session set timed_statistics=TRUE;

--test api using functions that return collections
declare

dept_t dept_Tabtype;
emp_t emp_dept_Tabtype;

begin

dept_t := test_api.getDeptTable;

for i in 1 .. dept_t.count loop

dbms_output.put_line( i || ') ' || dept_t(i).dept );

emp_t := test_api.getEmpTable(dept_t(i).id);
for j in 1 .. emp_t.count loop
dbms_output.put_line(emp_t(j).name||'-'||emp_t(j).dept);
end loop;

end loop;

end;
/

--test api using ref cursors
declare

my_rc SYS_REFCURSOR;

type dept_rt is record (id number,dept varchar2(2000));
type dept_tt is table of dept_rt;
type emp_rt is record (name varchar2(2000),dept varchar2(2000));
type emp_tt is table of emp_rt;
dept_t dept_tt;
emp_t emp_tt;

begin

test_api.getDeptCursor(my_rc);
fetch my_rc bulk collect into dept_t;
close my_rc ;

for i in 1 .. dept_t.count loop

dbms_output.put_line( i || ') ' || dept_t(i).dept );

test_api.getEmpCursor(dept_t(i).id,my_rc);
fetch my_rc bulk collect into emp_t;
close my_rc ;

for j in 1 .. emp_t.count loop
dbms_output.put_line(emp_t(j).name||'-'||emp_t(j).dept);
end loop;

end loop;

end;
/

Does this help? Could one argue that whilst a REF CURSOR api will allow greater flexibility a collection API is superior if for use solely by a pl/sql application?

Possibly the answer to this question is trivial and along the line of:

"Of course you can write a better interface for use solely by PL/SQL rather than a 3rd party client - derrrrrrr!"

Thanks in advance Tom - I've just ordered 2 of your books from Amazon btw!

Effective Oracle by Design
Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions


Can't wait!!


Tom Kyte
January 10, 2006 - 7:46 pm UTC

doesn't really help - I don't see any concrete reason to fill up the arrays in the first place. I'm always suspicious of code that does that (because they are missing the "just JOIN - let the database JOIN" point frequently).

In plsql - I'd be processing a cursor - doubtful I'd be passing ref cursors about (in fact, I don't, I process cursors in my code directly)

almost there...

James, January 31, 2006 - 1:26 am UTC

Tom,

I started at
1. PL/SQL application component using unbound dymanic sql

and moved to
2. bound sql using ref cursors

and then to
3. explicit cursors and sql collections

and finally to
4. explicit cursors and pl/sql types

print_procedure_in_package;

Now my parsing is looking excelent:

call count
------- ------
Parse 210
Execute 3382
Fetch 3382
------- ------
total 6974

And my latching (from original) is much better:

Run1 Run2 Diff Pct
512,722 322,046 -190,676 159.21%

The problem moving from step 3 to step 4, is that I had to include a "print" function in my application package that was initially part of a "utilities" function.

I did this so I could use PL/SQL collection types instead of SQL collection types. ie I didn't have to call another package.

Now for optimumal database performance, I have had to descentralize some code. This is not optimal for code development, maintenance and reuse.

Is this a catch 22 or can I do it better?

Regards


Tom Kyte
January 31, 2006 - 2:21 am UTC

I don't know what you mean by moving from 3 to 4, nor why sql collections and plsql types suddenly appeared or why you might be using them.

If you are returning data to a client program, you should have stopped at #2 - opening ref cursors using static sql and returning them.

sql types are sooo 2005

James, January 31, 2006 - 8:17 am UTC

Sorry Tom,

I chopped out to much in the interests of brevity and "step" should really have been "approach." All I have been trying to determine the best method to get and process smallish data sets from Java and PL/SQL. (Smallish because we are talking web page - everything is small...)

Approach 1 - Coded 6 years ago... no binds etc... we need to change this now.

For Approach 2 I created an API that accepted REF CURSOR IN/OUT parameters.

This worked fine for clients like Java but for PL/SQL we could do better - we want parse once execute many.

So for Approach 3, as you suggested I created a set of cursors in my pl/sql application.

To process the cursors I bulk collected into a SQL collection type and passed the collection to processing function.

Now, as you also suggested filling an array for no "concrete reason" is something to be wary of. Well I decided that there was no good reason to do this and after looking at some of the runstats output I seemed to creating additional latching due to using the objects.

So for approach 4 I bulk collected the data into a PL/SQL type and called my processing function with the pl/sql type.

Doing it this way resulted in simpler looking cursors and some latch reductions invloving object access.

Where I went wrong with my last followup was not remembering that my processing function could use globally declared pl/sql types in my calling package.

This caused me to put the processing function in the calling package and ask embarrassing questions about "code development, maintenance and reuse."

So to answer this question:

What is the best way to pass data from a PL/SQL procedure to another PL/SQL for processing? (Lets assume < 100 rows here)

From my testing via runstats and tkprof:

REF CURSORS - unnecessary parsing
SQL TYPES - object latching, unnecessary overhead

leaving a PL/SQL type (globally defined in a package) as the best soloution.

Regards

Tom Kyte
January 31, 2006 - 3:23 pm UTC

option N)

plsql table types passed with IN OUT NOCOPY (like passing a pointer - not as evil as globals, not as good as COPY speaking from a purist point of view, but the speed of the global without the mess of the global)

Extremely helpful to define ref cursor vs collection

A reader, April 28, 2006 - 7:55 am UTC


Cursor lifetime

totu, May 08, 2006 - 3:01 pm UTC

Dear Tom.

I have package which consist of function that return sys_refcursor:

open <cursor_name> for <select statement>

I call this funtion from client application (.NET) and fill my dataset. If I recall this function what will oracle do? opening new cursor? Where just went previous cursor?

Thanks in advance.


Tom Kyte
May 08, 2006 - 5:02 pm UTC

it'll open a new cursor - closing a ref cursor is the job of the CLIENT - the client application must close it

(has to - you could call this function of yours 50 times and get 50 cursors and not have fetched a single row from any yet - the CLIENT makes the decision to close things)

Think of this like opening a FILE - the "thing" that gets the file decides when to close it.

But where is cursor handle

Totu, May 09, 2006 - 1:45 am UTC

OK.

But how I have to close the previous opened cursor?

I opened cursor via function which resides inside of package.

I have OracleCommand object that executes that function using ExecuteNonQuery() method, but how close it, where is the handle?

Thanks in advance.

Tom Kyte
May 09, 2006 - 7:49 am UTC

You would have to ask the people that wrote the API itself. The CLIENT (Your API) fetched the result set (the cursor). the CLIENT API is responsible for closing it.


So, I can only say "refer to the documentation for whatever api you happen to be using to see if you need to do anything else here, or if the api itself already took care of it for you"

Need more information

aruna, May 09, 2006 - 9:47 pm UTC

Is there any way to create an object like :

CREATE OR REPLACE TYPE recInvestorDetail2 IS OBJECT (
InvestorFID PARTY_SOURCE.party_fid,
Surname INDIVIDUAL.surname
);

I need to return a ref cursor from the pl/sql stored procedure which is currently returning a pl/sql table.
But our coding standards insist in using table.column%type to declare variable types.

Please suggest ASAP


Tom Kyte
May 10, 2006 - 7:34 am UTC

your coding standards conflict with how it works.

SQL types do not do that, no, you cannot reference table.column in them.


but - that is OK since you said "...to return a ref cursor..." - perfect, you don't need the type at all therefore. That is the best.

Perfect

Roger, June 26, 2006 - 7:33 am UTC

Hi Tom,
Is there any way we can bring table data to front end (oracle forms) everytime we open an oracle session and use that data for that session. This is just to eliminate calls to database. We have a table with about 250 rows only and forms use that table and we know in live system there will be thousands of call and we just want to avoid network traffic and calls to DB. Can we use PL/SQL table for that.

Best regards,
Roger

Tom Kyte
June 26, 2006 - 7:51 am UTC

why? databases were born to do this stuff.

Are you trying to solve a hypothetical problem?

forms will array fetch from the database quite naturally (it'll reduce the round trips already).

you can populate a record_group, much like an array, using a query.

Very Good

Roger, June 26, 2006 - 10:19 am UTC

Cheers TOM,
will rely on database calls then.

Regards,

At Last I Found it !!!!

Dave Sharpe, August 02, 2006 - 10:19 pm UTC

I'm relatively new to the "PL" part of PL/SQL. For the better part of a week I've been trying to get a "virtual" table that I could populate via looping thru a cursor (massaging the data). Then query this "virtual" table to return a ref cursor.

Trying to track down solutions to errors like "ora-06530 initialize collection". "Can't declare", "must be declared globally" but nothing in language that I could comprehend. I'm quite good at Googling but I couldn't seem to ask the right question. I got to "Ask Tom" quite a few times and thankfully I finally got to this Q&A.

ignoring a column in the select list of ref cursor

Ramakrishna, August 29, 2006 - 3:45 am UTC

I am trying to ignore few columns while fetching from a cursor variable.
For example, if my cursor variable is opened for a query that returns 3 columns,
I want to fetch only two columns and ignore the third one i.e. last one.

It is working when my query is dynamic. But it is not working with the static query.
Could you please explain why? We are using 9.2.0.5.0 database. Below is my test case.

SQL> declare
  2   cv sys_refcursor;
  3   leno emp.empno%type;
  4   lename emp.ename%type;
  5  begin
  6   open cv for 'select empno, ename, job from emp'; --query is enclosed in quotes
  7   loop
  8    fetch cv into leno, lename;
  9    exit when cv%notfound;
 10    --dbms_output.put_line('emp no ' || leno);
 11    --dbms_output.put_line('emp name ' || lename);
 12   end loop;
 13   close cv;
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL> declare
  2   cv sys_refcursor;
  3   leno emp.empno%type;
  4   lename emp.ename%type;
  5  begin
  6   open cv for select empno, ename, job from emp;
  7   loop
  8    fetch cv into leno, lename;
  9    exit when cv%notfound;
 10    dbms_output.put_line('emp no ' || leno);
 11    dbms_output.put_line('emp name ' || lename);
 12   end loop;
 13   close cv;
 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 8
 

Tom Kyte
August 29, 2006 - 7:13 am UTC

if you have three, you need to fetch all three, that is just the way it works.

Makes sense to me. You'll fetch it and then you'll ignore it.

Ramakrishna, August 30, 2006 - 12:43 am UTC

We are able to ignore the column when the query is dynamic. How is it getting handled? Does oracle process it differently?

Even for static query, ORA-06504 error is raised when we execute code. We were able to compile the code successfully (I tried this with a procedure)



Tom Kyte
August 30, 2006 - 8:13 am UTC

it is a type mismatch, it is the way it works. Since you have a STATIC cursor and you have a STATIC type (known at compile time), this should not really be an issue.

I would suggest you just use a record


l_data cursor_name%rowtype;


and be done with it.


fetch cursor_name into l_data;




Very useful feature but i am getting error with nvarchar2 datatype

vijay sehgal, September 25, 2006 - 7:27 am UTC

Dear Tom,
this is a great example and I tried using the logic you have demonstrated to return refcursor with all lookup values in one call to java, but the system we are working for is to support arabic data so we are using nvarchar2 data type to hold the arabic data, I get the following error any workaround or should I return the array (knowing that it's not a good idea ) or should I ask the java guys to call different procedures for lookup values in different tables.

my example is as below.

CREATE OR REPLACE TYPE lookup_data_type AS OBJECT (
table_name VARCHAR2(30),
key_id NUMBER,
key_value_eng VARCHAR2(100),
key_value_arb NVARCHAR2(100)
);

CREATE OR REPLACE
TYPE lookup_table_type AS TABLE OF lookup_data_type;

PROCEDURE return_result(p_result OUT ref_cur) AS l_data lookup_table_type := lookup_table_type(); l_cnt PLS_INTEGER := 0; BEGIN FOR i IN (SELECT 'COMM_REFUND_FREQ'
table_name,crf.cmf_id_pk,crf.cmf_description_eng,crf.cmf_description_arb
FROM OMTM_COMM_REFUND_FREQ crf)
LOOP
l_data.EXTEND;
l_cnt := l_cnt + 1;
l_data(l_cnt) :=
lookup_data_type(
i.table_name,i.cmf_id_pk,i.cmf_description_eng,i.cmf_description_arb);

END LOOP;

OPEN p_result FOR
SELECT * FROM TABLE ( CAST ( p_result AS lookup_table_type) );

END return_result;

When I compile the code I get the following error message.
PL/SQL: ORA-12714: invalid national character set specified.

your help in this regard is much appreciated.

Regards,
Vijay Sehgal


still waiting for your thoughts on this.

Vijay Sehgal, September 29, 2006 - 3:14 am UTC

Dear Tom,
good day to you as always, I don't mean to be persuasive but any thoughts on the error I am getting or any workaround for the same.

Awaiting your reply on this.

Kind Regards,
Vijay SEhgal

Tom Kyte
September 29, 2006 - 8:13 am UTC

persuasive would be a good thing. I think the adjective however you were looking for was "pestering", "a bother".

persuasive means you enticed me so much to answer that I did :)

sorry, i've never worked with arabic data, I'll recommend you to support for this.

sorry to be pressing for the workaround to the problem I am facing

Vijay Sehgal, September 29, 2006 - 12:09 pm UTC

Dear Tom,
I am really sorry for posting the question back again, though I know you try your level best to take as many questions and follow ups you can, really sorry for my mistake, I have for now got the workaround from your site as select * from table(function_name(parameters)), but would like to know, is this problem with all the national character sets or only national characterset for arabic support.

Thanks for all your efforts and the time you take to answer the questions and the help you are to everyone for Oracle related queries.

Kind Regards,
Vijay Sehgal

Tom Kyte
September 30, 2006 - 7:45 am UTC

I don't know, I quite simply *cannot reproduce* your issue, please utilize support.

appending to the end of the table type.

push, October 31, 2006 - 3:39 pm UTC

Tom,
How do I append data into a object table type in a loop.

For example,

create type emp_obj_typ as object (name varchar2(20),
sal number);
/
Type created.

create type emp_tab_typ as table of emp_obj_typ;
/
Type created.

DECLARE
emp_tab emp_tab_typ := emp_tab_typ();
CURSOR c1 IS
SELECT deptno FROM dept;
BEGIN

FOR rec IN c1 LOOP

SELECT CAST(MULTISET(SELECT ename,sal FROM emp
WHERE deptno = rec.deptno ) as emp_tab_typ)
INTO emp_tab
FROM dual;

dbms_output.put_line(emp_tab.COUNT);
END LOOP;
END;
/

In the above example, when the loop is end, the emp_tab
is only containing the last fetched row. How do we append
emp_tab by keeping the previous rows.

Thanks
Push...

Tom Kyte
October 31, 2006 - 4:20 pm UTC

you don't, select into replaces.


why are you looping when a single sql statement would obviously work ?

strange thing happening

Herbert, November 15, 2006 - 11:14 am UTC

Hi Tom,

I saw a strange thing happening so i tried to narrow (problem) down. I hope you have an idea.

SQL>  create or replace type myScalarType as object
  2     (   x     number(4,0))
  3  /

Type created.

SQL> create or replace type myTableType as table of myScalarType;
  2  /

Type created.

SQL>  create or replace function demo_proc( p_start_row in number,
  2                       p_end_row in number )
  3   return myTableType
  4   as
  5       l_data             myTableType := myTableType();
  6       l_cnt              number default 0;
  7   begin
  8       for r in ( select * from emp order by sal desc )
  9       loop
 10           l_cnt := l_cnt + 1;
 11           if ( l_cnt >= p_start_row )
 12           then
 13               l_data.extend;
 14               l_data(l_data.count).x := r.empno;
 15             --  l_data(l_data.count).y := r.hiredate;
 16              -- l_data(l_data.count).z := r.ename;
 17           end if;
 18           exit when l_cnt = p_end_row;
 19       end loop;
 20   
 21       return l_data;
 22   end;
 23  /

Function created.

SQL> select * from table(cast(demo_proc(2,6) as mytableType))
  2  /
select * from table(cast(demo_proc(2,6) as mytableType))
                         *
ERROR at line 1:
ORA-06530: Reference to uninitialized composite
ORA-06512: at "OWNER_RL.DEMO_PROC", line 14

Now if i change the to use a nuber(5,0) then
SQL> drop type myTableType
  2  /

Type dropped.

SQL> create or replace type myScalarType as object
  2    (   x     number(5,0))
  3  /

Type created.

SQL> create or replace type myTableType as table of myScalarType;
  2  /

Type created.

SQL> select * from table(cast(demo_proc(2,6) as mytableType))
  2  /

         X
----------



SQL> 

This time no error anymore but i don't see any values in the rows. Any idea what's happening? 
 

Tom Kyte
November 16, 2006 - 3:53 am UTC

12 then
13 l_data.extend;
l_data(l_data.count) := myScalarType();
14 l_data(l_data.count).x := r.empno;
15 -- l_data(l_data.count).y := r.hiredate;
16 -- l_data(l_data.count).z := r.ename;
17 end if;


you did not initialize the array element to anything.

Re: strange thing happening

Herbert, November 16, 2006 - 5:56 am UTC

Thanks for the quick answer Tom.

When it try your solution i get:

SQL>  create or replace function demo_proc( p_start_row in number,
  2                       p_end_row in number )
  3   return myTableType
  4   as
  5       l_data             myTableType := myTableType();
  6       l_cnt              number default 0;
  7   begin
  8       for r in ( select * from emp order by sal desc )
  9       loop
 10           l_cnt := l_cnt + 1;
 11           if ( l_cnt >= p_start_row )
 12           then
 13               l_data.extend;
 14               l_data(l_data.count) := myScalarType();
 15               l_data(l_data.count).x := r.empno;
 16             --  l_data(l_data.count).y := r.hiredate;
 17              -- l_data(l_data.count).z := r.ename;
 18           end if;
 19           exit when l_cnt = p_end_row;
 20       end loop;
 21   
 22       return l_data;
 23   end;
 24  /

Warning: Function created with compilation errors.

SQL> show err
Errors for FUNCTION DEMO_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
14/14    PL/SQL: Statement ignored
14/38    PLS-00306: wrong number or types of arguments in call to
         'MYSCALARTYPE'

When i use a pl/sql table type it works fine without the l_data(l_data.count) := myScalarType(); but then i can't select on it later. 

Tom Kyte
November 16, 2006 - 3:17 pm UTC

forgot, put null,null,null in there for each attribute, you need to assign an emptied object there before you can assign the attributes.

or, just code:

l_data(l_data.count) := myscalarType( r.empno, r.hiredate, r.ename );

instead of:

15 l_data(l_data.count).x := r.empno;
16 -- l_data(l_data.count).y := r.hiredate;
17 -- l_data(l_data.count).z := r.ename;

something more on this

reader, December 30, 2006 - 7:57 am UTC

Dear Sir,
as you have given an e.g. to use SQL object type to return data opening refcursor on it, if I have to return all the columns from a table in this way, is there any alternative way to create sql type without having to create one element for each column of the table.

Thanks,
reader.

Tom Kyte
December 30, 2006 - 9:46 am UTC

you need the type.

size limit

A reader, January 05, 2007 - 11:32 am UTC

Do we have any size limitation on data set we can pass from a stored procedure to client via ref cursor?
Tom Kyte
January 06, 2007 - 8:30 am UTC

nope, because a ref cursor itself does not "pass" any data. It is simply a cursor opened in the server and returned to a client. It doesn't contain the data.

Later the client will fetch from the cursor, using an array size of it's own choosing.

why schema type and not package type

Oracle beginner, January 15, 2007 - 12:01 pm UTC

Tom,

You wrote:

'if you want SQL to see the TYPE -- you need to create the TYPE in SQL -- not in plsql.

a plsql table type is just that - PLSQL only.

PLSQL is "above" SQL, PLSQL can see all things PLSQL and SQL.

SQL is "below" PLSQL. SQL can see all things SQL.'



1 create or replace package test_pk is
2 type test_type is record (x varchar2(10),
3 y varchar2(10));
4 type test_tab is table of test_type;
5* end;
SQL> /

Package created.



1 create or replace function test_fn return test_pk.test_tab
2 pipelined
3 is
4 m_row test_pk.test_type;
5 begin
6 for j in 1..2
7 loop
8 m_row.x := ('X'||j);
9 m_row.y := ('Y'||j);
10 pipe row ( m_row );
11 end loop;
12 return;
13* end;
SQL> /

Function created.

SQL> select * from table(test_fn());

X Y
---------- ----------
X1 Y1
X2 Y2



Tom,

This was written and run against in oracle 10g R2 with the TABLE function operating on a package type instead of an oracle schema type.

Is this new functionality introduced in 9i or 10G? Or is it the 'pipelined' function (9i onwards) that allows pl/sql types to be used here rather than oracle types.

Thanks


Passing Values to shell script.

Anil Tanwar, January 29, 2007 - 4:45 am UTC

Hello Tom,

I am writing a unix shell script in which i have to pass return value from pl/sql procedure to UNIX variables. Can you suggest me any way to do that.

I am presently declaring a binary variable and passing the same to pl/sql procedure which stores the value in bind variable. Then I spool the same and print it.

At the OS level I extract the same from the spool file thru grep.

------------------
sqlplus -s $ORACLE_USER/$ORACLE_PWD@$ORACLE_CONNECT << EOF
prompt Checking for valid argument;
var p_count number;
var reccount number;
exec SP_PARAMCHECK ( '$FEEDNM', :P_COUNT, :RECCOUNT );
commit;
set echo off
spool sp_$FEEDNAME`date +%m%d%Y`.txt
print p_count
spool off
set echo off
spool sp_$FEEDNAME`date +%m%d%Y`_1.txt
print reccount
spool off
EOF

v9=`cat $filenm | grep -v '^$' | grep -v 'P_COUNT'| tail -1 | tr -s ' ' | cut -d ' ' -f2`

v11=`cat $filenm | grep -v '^$' | grep -v 'RECCOUNT'| tail -1 | tr -s ' ' | cut -d ' ' -f2`

if [ "$v11" = "" ]
then
v11=0
fi

I know this is a very stupid way of doing things. Can u please help me out.

Thanks in advance
Tom Kyte
January 31, 2007 - 1:36 pm UTC

why is is "stupid" - it works

pretty much the way it is done when you are not using a language that can interact with the database.

Why does piplined return not require Schema Type?

Oracle Beginner, February 01, 2007 - 5:12 pm UTC

Hi Tom,

Please can you answer my question of 15th January as to why the piplined function can return a type declared in a package without having to make it return a schema type created through the
CREATE TYPE
command?

Sorry it I have misunderstood something.

Thanks
Tom Kyte
February 02, 2007 - 10:18 am UTC

look in your schema and you'll find types with ugly names created for you. The types exist - they are there - we just automagically created them.

ops$tkyte%ORA10GR2> select object_name, object_type from user_objects;

no rows selected

ops$tkyte%ORA10GR2> create or replace package test_pk
  2  as
  3          type test_type is record (x varchar2(10), y varchar2(10));
  4          type test_tab is table of test_type;
  5  end;
  6  /

Package created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace function test_fn return test_pk.test_tab
  2  pipelined
  3  as
  4  m_row test_pk.test_type;
  5  begin
  6  for j in 1..2
  7  loop
  8      m_row.x := ('X'||j);
  9      m_row.y := ('Y'||j);
 10     pipe row ( m_row );
 11   end loop;
 12   return;
 13  end;
 14  /

Function created.

ops$tkyte%ORA10GR2> select object_name, object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
TEST_PK                        PACKAGE
SYS_PLSQL_64288_26_1           TYPE
SYS_PLSQL_64288_DUMMY_1        TYPE
SYS_PLSQL_64288_9_1            TYPE
TEST_FN                        FUNCTION


REF CURSOR returning values..

Suvendu, February 07, 2007 - 3:54 am UTC

Hi Tom,

Could you please, look into below codes?

Thanks a lot for your kind help and time.

Regards,
Suvendu



SQL> desc METAOBJECT_M
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 METAOBJECT_N                                       VARCHAR2(100)
 METAOBJECT_C                                       NUMBER

SQL> desc E_HARI10005300050005SDM
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 ISSUERID                                           VARCHAR2(20)
 ABCD                                               VARCHAR2(20)

SQL> col METAOBJECT_N for a20
SQL> select * from  METAOBJECT_M;

METAOBJECT_N         METAOBJECT_C
-------------------- ------------
ISSUERID                        3
ABCD                            2

SQL> select * from E_HARI10005300050005SDM;

ISSUERID             ABCD
-------------------- --------------------
U29302               INDIA
M4752S               INDIA

SQL> SELECT METAOBJECT_N FROM   METAOBJECT_M WHERE  METAOBJECT_C =3;

METAOBJECT_N
--------------------
ISSUERID

SQL> SELECT ISSUERID from E_HARI10005300050005SDM;

ISSUERID
--------------------
U29302
M4752S

SQL> create or replace procedure show (p_metaobject_id number,p_cursor out sys_refcursor)
  2   is
  3      v_column_name varchar2(100);
  4      v_sql         varchar2(200);
  5      v_cursor      sys_refcursor;
  6   begin
  7      SELECT METAOBJECT_N into v_column_name
  8      FROM   METAOBJECT_M
  9      WHERE  METAOBJECT_C = p_metaobject_id;
 10      OPEN v_cursor FOR 'select '||v_column_name||' from E_HARI10005300050005SDM';
 11      fetch v_cursor INTO p_cursor;
 12   end;
 13  /

Procedure created.

SQL> var l_cursor refcursor
SQL> set autoprint on
SQL> exec show(3,:l_cursor);
BEGIN show(3,:l_cursor); END;

*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CURSER got CHAR
ORA-06512: at "SCOTT.SHOW", line 11
ORA-06512: at line 1


ERROR:
ORA-24338: statement handle not executed


SP2-0625: Error printing variable "l_cursor"
SQL>

Tom Kyte
February 07, 2007 - 5:31 pm UTC

I don't know what you were doing with the fetch there - just open p_cursor, not v_cursor

I have to say, this code scares me, I see poor performance and un-maintainability in your future - I forecast that with 100% accuracy.

ops$tkyte%ORA10GR2> create or replace procedure show (p_metaobject_id number,p_cursor out sys_refcursor)
  2   is
  3      v_column_name varchar2(100);
  4      v_sql         varchar2(200);
  5   begin
  6      SELECT METAOBJECT_N into v_column_name
  7      FROM   METAOBJECT_M
  8      WHERE  METAOBJECT_C = p_metaobject_id;
  9      OPEN p_cursor FOR 'select '||v_column_name||' from E_HARI10005300050005SDM';
 10   end;
 11  /

Procedure created.

It works...

Suvendu, February 09, 2007 - 10:50 am UTC

Thanks a lot for answering me.

There was my misconcept. Always a mentor can only noticed the falult of students.. :-)

Regards,
Suvendu

different behavior of ref cursor in 9i and 10g

sean, March 21, 2007 - 3:39 pm UTC

Hi Tom,

We have a program using refcursor which works in 9i, but not 10g. In 9i, it won't complain if there are less parameters, so we could create multiple cursors with different parameters. Thanks so much for your help. -- Sean

create table t1 ( c1 number, c2 number);

declare
s varchar2(32676) :='select c1, c2 from t1';
p1 t1.c1%type;
p2 t1.c2%type;
c sys_refcursor;
begin
open c for s;
loop
fetch c into
p1;
EXIT WHEN c%NOTFOUND;
end loop;
end;
/


-- In 9i

PL/SQL procedure successfully completed.

-- In 10g

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 9


Tom Kyte
March 21, 2007 - 7:38 pm UTC

you'll want to fix that, they fixed the bug in 9i that permitted this to work by accident.

Cursors with WHERE value IN (..)

Pretaish, April 03, 2007 - 11:41 am UTC

Hi Tom,

I have a code which resembles the below closely-

Declare
Cursor c1 is
select col1, col2
from my_tab
where col_3 IN ('val_1', 'val_2'....'val_10')
..

The issue is that instead of using the hardcoded values ('val_1', 'val_2'....'val_10'), I want to pick them up from a static data table. One of the columns of this table would hold the value 'val_1', 'val_2'....'val_10'.

Please suggest the best way to do this.

Tom Kyte
April 04, 2007 - 9:48 am UTC

where col_3 in (select val_1 from t
                union all 
                select val_2 from t
                union all
                .....)

Cursors with WHERE value IN (..)

pretaish, April 04, 2007 - 11:57 am UTC

Hi Tom,

Apologies for not communicating the problem clearly! Please take a minute to evaluate this-

create table test_tab (col_1 number, col_2 varchar2(2000));

INSERT INTO test_tab VALUES(1, '''v1'', ''v2'', ''v3'', ''v4''');
INSERT INTO test_tab VALUES(2, '''v5'', ''v6'', ''v7'', ''v8''');
INSERT INTO test_tab VALUES(3, '''v9'', ''v10'', ''v11'', ''v12''');

Declare
Cursor c1 IS
SELECT col1, col2
FROM big_tab bt
WHERE bt.value_range IN ('v1', 'v2'....'v10' );

I wish to get rid of the hardcoding in the cursor definition.
Instead, i wish to query teat_tab, and fetch it from there.
Tom Kyte
April 04, 2007 - 12:18 pm UTC

http://asktom.oracle.com/Misc/varying-in-lists.html

you need to turn that string into a set


you really want to fix that long term, it should be:

insert into test_tab values ( 1, 'v1' );
insert into test_tab values ( 1, 'v2' );
...

so you can just

where col in (select col_2 from test_tab where col_1 = 1 );


How to exit the block

Mike, August 23, 2007 - 12:32 am UTC


Tom, this may be a new plsql dev question. but i looked up doc and could not find the answer yet.
I want the following plsql to do the check on the status of a process:
if completed ='Y' we insert one record with 'Y' and exit out. I do not have problem with this.

if completed <>'Y' we insert one record with 'N' then goto the beginig and try it again.
we only want to try 4 times for completed <>'Y'. at the 5th completed <>'Y' we want to abort.
i used 'return' and it does not work.
Please help.


declare
completed VARCHAR2(1);
cnt number :=0;
begin
<< rck >>
cnt := cnt +1;
select my.success('ETL',4)
into completed from dual;
if completed ='Y'
then
INSERT INTO temp VALUES (cnt,'Y',sysdate);
commit;
else
INSERT INTO temp VALUES (cnt,'N',sysdate);
commit;
DBMS_LOCK.SLEEP (20);
if cnt>4 then return; end if;
goto rck;
end if;
end;
/


Tom Kyte
August 23, 2007 - 11:23 am UTC

return does work - but this is some whacky code. Using "goto" to loop is considered "not good"


this shows the existing code "works" (but does it five times because you used cnt >4, not cnt >= 4)

ops$tkyte%ORA10GR2> create table temp( x number, y varchar2(1), dt date );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2     completed VARCHAR2(1);
  3     cnt number :=0;
  4  begin
  5  << rck >>
  6     cnt := cnt +1;
  7     select 'N'
  8       into completed
  9           from dual;
 10     if completed ='Y'
 11     then
 12        INSERT INTO temp VALUES (cnt,'Y',sysdate);
 13        commit;
 14     else
 15        INSERT INTO temp VALUES (cnt,'N',sysdate);
 16        commit;
 17        -- DBMS_LOCK.SLEEP (20);
 18        if cnt>4 then return;  end if;
 19        goto rck;
 20     end if;
 21  end;
 22  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from temp;

         X Y DT
---------- - ---------
         1 N 23-AUG-07
         2 N 23-AUG-07
         3 N 23-AUG-07
         4 N 23-AUG-07
         5 N 23-AUG-07


However, using better coding practices, this would just really be:

ops$tkyte%ORA10GR2> begin
  2      if ( 'N' /* my.success('ETL',4) */ = 'Y' )
  3      then
  4          insert into temp values ( 1, 'Y', sysdate );
  5          commit;
  6      else
  7          for i in 1 .. 4
  8          loop
  9              insert into temp values ( i, 'N', sysdate );
 10              commit;
 11              -- dbms_lock.sleep(20);
 12          end loop;
 13      end if;
 14  end;
 15  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from temp;

         X Y DT
---------- - ---------
         4 N 23-AUG-07
         1 N 23-AUG-07
         2 N 23-AUG-07
         3 N 23-AUG-07




a) do NOT select f(x) into variable from dual; JUST USE AN ASSIGMENT, call the function, period.

b) use for to look - not goto.

c) I am worried about the commits in here, I stress that you should not commit in PLSQL in general, the client invoking this should commit - if your goal is to monitor this from another routine - read about dbms_application_info which lets you put information in v$session and v$session_longops that is immediately visible to other sessions without committing.

d) actually, I think your ETL process should DEFINITELY be calling dbms_application_info to set session longops (search on this site for examples of doing that)

thanks! you are the best!

A reader, August 23, 2007 - 11:36 pm UTC


How to get Primary Key without commiting?

Mohan, September 04, 2007 - 10:50 am UTC

Hi Tom,

In your last followup you gave this...
~~~~~~~~~
I am worried about the commits in here, I stress that you should not commit in PLSQL in general, the client invoking this should commit - if your goal is to monitor this from another routine - read about dbms_application_info which lets you put information in v$session and v$session_longops that is immediately visible to other sessions without committing
~~~~~~~~~

Here is my Situation. Im Using Oracle 10g with JDeveloper 10.1.3.1

In our tables Primary Keys are genereted by Triggers.

So , i will not insert PK through Sequence.

Here is the code

FUNCTION insertitem (itemcode VARCHAR2, itemdesc VARCHAR2)
RETURN NUMBER
IS
itemid NUMBER;
BEGIN
INSERT INTO items
VALUES (itemcode, itemdesc); -- Item code is Unique

COMMIT; ------- Commit to get the Primary Key

SELECT ID
INTO itemid
FROM items
WHERE code = itemcode;

RETURN itemid;
END insertitem;

In the above case , how can i get Primary Key of the New ItemCode row inserted in the ITEMS table, without Commit inside the function.

Thanks
Mohan
Tom Kyte
September 05, 2007 - 1:50 pm UTC

begin
   insert into items (a,b,c) values (x,y,z) returning id into itemid;
   return itemid;
end;

does that

but - YOU NEVER EVER NEEDED THE COMMIT in the first place, a transaction will be able to see its own changes, the commit was never supposed to be there.



Ignore the previous post

A reader, September 04, 2007 - 3:01 pm UTC

Hi Tom,

Ignore the above post. Without commit, im able to get the PK.

Tom Kyte
September 05, 2007 - 1:53 pm UTC

unignore that - do not do a select, just use the returning clause!

Returning multiple rows from pl/sql

Rita, November 23, 2007 - 4:34 pm UTC

Hi Tom,
I need some i/p on the following
1) If multiple rows are to be returned from a pl/sql that is being used as a web service, what is the way to return these rows ? I wrote a pipelined fn and then when I execute it from SQL using
select * from table(fas_vndr_qoh_pkg.Fas_get_vndr_qoh('0301865', 'USA', 'N'))
I get the desired results. But I dont understand how the function can be called from a web service. Is pipelined fn not the way to do this ?

2)As you can see the call in the example above has 3 parameters. However the first parameter can have multiple values and that needs to be changed into an IN when selecting from the table. I will probably have to define that parameter as a table type. What delimiter should be used for the different values of parameter 1 ?

Appreciate your help,

Thanks,
Rita


Tom Kyte
November 26, 2007 - 11:47 am UTC

1) web services typically return XML formatted data, so you would be returning an XML document instance. TONS of xml capabilities to turn your result set into xml.

2) choose one that you don't see happening in your input set of values

Returning multiple rows from pl/sql

Rita, November 26, 2007 - 4:39 pm UTC

Thanks for the reply Tom.
1)
I used JDeveloper to create and deploy a web service which used the pl/sql pipelined function. When I invoke the web service , below is the error I get.
Looks like pipelined fns are not supported by Web Service.
In that case how do I return multiple rows from the pl/sql.


<?xml version="1.0" encoding="UTF-8" ?>
- <SOAP-ENV:Envelope xmlns:SOAP-ENV=" http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd=" http://www.w3.org/2001/XMLSchema" >
- <SOAP-ENV:Body>
- <SOAP-ENV:Fault>
<faultcode>SOAP-ENV:Server.Exception:</faultcode>
<faultstring>java.sql.SQLException: ORA-06550: line 3, column 10: PLS-00653: aggregate/table functions are not allowed in PL/SQL scope</faultstring>
<faultactor>/Application1-Project-context-root/Vndr_qoh_pipe</faultactor>
</SOAP-ENV:Fault>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>


Thanks,
Rita

Small problem on my

Troy DesBarres, December 18, 2007 - 2:16 pm UTC

The example was great but I am having a small problem, here what I have....
TYPE "EXPORT_TABLE_ITEM" IS RECORD (
cd VARCHAR2(14),
);

TYPE EXPORT_TABLE_TYPE IS TABLE OF EXPORT_TABLE_ITEM;
type rc is ref cursor;

In the body I have the following:

PROCEDURE doWork(P_cursor in out rc)
IS
-- CREATE THE ITEM INSTANCE
table_item EXPORT_TABLE_ITEM;

-- INITIALIZE THE RESULTS TABLE
result_table EXPORT_TABLE_TYPE := EXPORT_TABLE_TYPE();

-- do some work to fill out the item.

result_table.extend();
result_table(var_count) := table_item;

open p_cursor for
select *
from TABLE(cast(result_table as EXPORT_TABLE_TYPE));
end doWork;

When I compile it I get the following error:
Error PL/SQL: ORA-00902: invalid datatype

When I click on the error it highlights the EXPORT_TABLE_TYPE in the line of code.

from TABLE(cast(result_table as EXPORT_TABLE_TYPE));

Any help would be greatly appreciated.

Thanks so much.
Tom Kyte
December 18, 2007 - 2:27 pm UTC

you use SQL types for this, not plsql types - SQL will never understand what a "plsql record is"


Object Type Necessary?

A reader, February 28, 2008 - 2:54 pm UTC

I have used this model many times, but I was trying to figure out what makes creating the scalar type necessary? Is the scalar type only for multiple columns?

In other words, couldn't one just use the SQL Table type object?

Thanks,
Tom Kyte
February 28, 2008 - 11:25 pm UTC

if you need just a table of an already existing type, you do not of course need "myScalarType" - that just shows how to do this in general.

if you just need a table of number, so be it, just use a table of number - there "yourScalarType" is number..

32767

A reader, February 29, 2008 - 10:18 am UTC

Thank you for the prompt response.

The documentation isn't clear on one point.

You mentioned earlier that a PL/SQL type, if defined as (for example) varchar2(32767), and it's subsequent constructor is defined as mytype(10), and only one of those 10 spots in they array is used, it will reside in the 10th position.

Does a SQL collection type behave the same if not declared with the scalar object type?

For example, I have:
CREATE TYPE mytype AS TABLE OF varchar2(32767);

To which I assign only 5 values including a null in the fourth position.

SQL> declare
2 n number;
3 begin
4 n := get_mytype;
5 end;
6 /
o
poo
shoo
goo

PL/SQL procedure successfully completed.

Are my five values allocated at the beginning of the array or the end?
Tom Kyte
March 01, 2008 - 10:40 am UTC

sorry, don't know what you mean.


If you assigned 5 values, they'll be N(1), N(2), ... N(5) - you would have put them there in slots 1..5.

I don't know where you pulled what you think I said from. But, I'm not following you at all here.

Thank you

A reader, March 03, 2008 - 8:57 am UTC

I must have misunderstood or misinterpreted something you wrote. If I can't clarify it on my own I'll re-post.

Return values from a PL/SQL table into a ref cursor

Ed from Dallas, March 05, 2008 - 6:39 pm UTC


Below I create 2 procedures (get_Emp_Dept_Strong and get_Emp_Dept_Weak) in the SCOTT schema and respectively, one uses a strong REF CURSOR and the other a WEAK one.

Both successfully return the proper data set via the REF CURSOR.

Could you please explain why one would use one method over the other (strong REF CURSOR versus weak REF CURSOR ) either as a best practice or whether the procedure would be used by another stored procedure or externally by Java or C# code?

Aside from the obvious (less coding), why would one even want to define a record type (get_Emp_Dept_recType) when they can use SYS_REFCURSOR like in this example?

Thanks so much...

P.S. Really wish I could hear you at the Hotsos Symposium tomorrow...


scott@dv2> CREATE OR REPLACE PACKAGE ref_Cursor_Lib_pkg IS
  2  
  3     -- Type Declarations
  4     TYPE get_Emp_Dept_recType IS RECORD (
  5        emp_name       emp.ename%TYPE,
  6        emp_title      emp.job%TYPE,
  7        dept_name      dept.dname%TYPE,
  8        emp_Hire_date  emp.hiredate%TYPE,
  9        salary         VARCHAR2(20),
 10        report_date    DATE);
 11     TYPE emp_Dept_curType IS REF CURSOR RETURN get_Emp_Dept_recType;
 12  
 13  END ref_Cursor_Lib_pkg;
 14  /

Package created.

scott@dv2> 
scott@dv2> CREATE OR REPLACE PACKAGE BODY ref_Cursor_Lib_pkg IS
  2  
  3  END ref_Cursor_Lib_pkg;
  4  /

Package body created.

scott@dv2> CREATE OR REPLACE PROCEDURE get_Emp_Dept_Strong(emp_Dept_curVar OUT ref_Cursor_Lib_pkg.emp_Dept_curType) 
  2  IS
  3  BEGIN
  4  
  5     OPEN emp_Dept_curVar FOR
  6        SELECT e.ename,
  7               e.job,
  8               d.dname,
  9               e.hiredate,
 10               to_char(e.sal, '$99,999.99') AS sal,
 11               SYSDATE AS today
 12        FROM   emp  e,
 13               dept d
 14        WHERE  e.deptno = d.deptno;
 15  
 16  END get_Emp_Dept_Strong;
 17  /

Procedure created.

scott@dv2> 
scott@dv2> var l_test_cursor refcursor
scott@dv2> set autoprint on
scott@dv2> 
scott@dv2> exec get_Emp_Dept_Strong(:l_test_cursor);

PL/SQL procedure successfully completed.


ENAME      JOB       DNAME          HIREDATE          SAL         TODAY
---------- --------- -------------- ----------------- ----------- -----------------
CLARK      MANAGER   ACCOUNTING     06/09/81 00:00:00   $2,450.00 03/05/08 16:59:59
KING       PRESIDENT ACCOUNTING     11/17/81 00:00:00   $5,000.00 03/05/08 16:59:59
MILLER     CLERK     ACCOUNTING     01/23/82 00:00:00   $1,300.00 03/05/08 16:59:59
JONES      MANAGER   RESEARCH       04/02/81 00:00:00   $2,975.00 03/05/08 16:59:59
FORD       ANALYST   RESEARCH       12/03/81 00:00:00   $3,000.00 03/05/08 16:59:59
ADAMS      CLERK     RESEARCH       05/23/87 00:00:00   $1,100.00 03/05/08 16:59:59
SMITH      CLERK     RESEARCH       12/17/80 00:00:00     $800.00 03/05/08 16:59:59
SCOTT      ANALYST   RESEARCH       04/19/87 00:00:00   $3,000.00 03/05/08 16:59:59
WARD       SALESMAN  SALES          02/22/81 00:00:00   $1,250.00 03/05/08 16:59:59
TURNER     SALESMAN  SALES          09/08/81 00:00:00   $1,500.00 03/05/08 16:59:59
ALLEN      SALESMAN  SALES          02/20/81 00:00:00   $1,600.00 03/05/08 16:59:59
JAMES      CLERK     SALES          12/03/81 00:00:00     $950.00 03/05/08 16:59:59
BLAKE      MANAGER   SALES          05/01/81 00:00:00   $2,850.00 03/05/08 16:59:59
MARTIN     SALESMAN  SALES          09/28/81 00:00:00   $1,250.00 03/05/08 16:59:59

14 rows selected.

scott@dv2> 
scott@dv2> 
scott@dv2> CREATE OR REPLACE PROCEDURE get_Emp_Dept_Weak(emp_Dept_curVar OUT SYS_REFCURSOR)
  2  IS
  3  BEGIN
  4  
  5     OPEN emp_Dept_curVar FOR
  6        SELECT e.ename,
  7               e.job,
  8               d.dname,
  9               e.hiredate,
 10               to_char(e.sal, '$99,999.99') AS sal,
 11               SYSDATE AS today
 12        FROM   emp  e,
 13               dept d
 14        WHERE  e.deptno = d.deptno;
 15  
 16  END get_Emp_Dept_Weak;
 17  /

Procedure created.

scott@dv2> 
scott@dv2> exec get_Emp_Dept_Weak(:l_test_cursor);

PL/SQL procedure successfully completed.


ENAME      JOB       DNAME          HIREDATE          SAL         TODAY
---------- --------- -------------- ----------------- ----------- -----------------
CLARK      MANAGER   ACCOUNTING     06/09/81 00:00:00   $2,450.00 03/05/08 17:00:12
KING       PRESIDENT ACCOUNTING     11/17/81 00:00:00   $5,000.00 03/05/08 17:00:12
MILLER     CLERK     ACCOUNTING     01/23/82 00:00:00   $1,300.00 03/05/08 17:00:12
JONES      MANAGER   RESEARCH       04/02/81 00:00:00   $2,975.00 03/05/08 17:00:12
FORD       ANALYST   RESEARCH       12/03/81 00:00:00   $3,000.00 03/05/08 17:00:12
ADAMS      CLERK     RESEARCH       05/23/87 00:00:00   $1,100.00 03/05/08 17:00:12
SMITH      CLERK     RESEARCH       12/17/80 00:00:00     $800.00 03/05/08 17:00:12
SCOTT      ANALYST   RESEARCH       04/19/87 00:00:00   $3,000.00 03/05/08 17:00:12
WARD       SALESMAN  SALES          02/22/81 00:00:00   $1,250.00 03/05/08 17:00:12
TURNER     SALESMAN  SALES          09/08/81 00:00:00   $1,500.00 03/05/08 17:00:12
ALLEN      SALESMAN  SALES          02/20/81 00:00:00   $1,600.00 03/05/08 17:00:12
JAMES      CLERK     SALES          12/03/81 00:00:00     $950.00 03/05/08 17:00:12
BLAKE      MANAGER   SALES          05/01/81 00:00:00   $2,850.00 03/05/08 17:00:12
MARTIN     SALESMAN  SALES          09/28/81 00:00:00   $1,250.00 03/05/08 17:00:12

14 rows selected.

scott@dv2> 



Tom Kyte
March 06, 2008 - 8:18 am UTC

describe the procedures and you'll see why you might do this.

ops$tkyte%ORA10GR2> create or replace package my_pkg
  2  as
  3          type my_cursor is ref cursor return emp%rowtype;
  4          procedure p1( x in out sys_refcursor );
  5          procedure p2( x in out my_cursor );
  6  end;
  7  /

Package created.

ops$tkyte%ORA10GR2> desc my_pkg
PROCEDURE P1
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 X                              REF CURSOR              IN/OUT
PROCEDURE P2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 X                              REF CURSOR              IN/OUT
                                RECORD                  IN/OUT
     EMPNO                      NUMBER(4)               IN/OUT
     ENAME                      VARCHAR2(10)            IN/OUT
     JOB                        VARCHAR2(9)             IN/OUT
     MGR                        NUMBER(4)               IN/OUT
     HIREDATE                   DATE                    IN/OUT
     SAL                        NUMBER(7,2)             IN/OUT
     COMM                       NUMBER(7,2)             IN/OUT
     DEPTNO                     NUMBER(2)               IN/OUT


Strong-typed versus Weak-typed REF CURSOR approach

Edward Girard, March 06, 2008 - 3:23 pm UTC


Thank you Tom,


scott@dv2> desc get_Emp_Dept_Weak
PROCEDURE get_Emp_Dept_Weak
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 EMP_DEPT_CURVAR                REF CURSOR              OUT

scott@dv2> desc get_Emp_Dept_Strong
PROCEDURE get_Emp_Dept_Strong
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 EMP_DEPT_CURVAR                REF CURSOR              OUT
                                RECORD                  OUT
     EMP_NAME                   VARCHAR2(10)            OUT
     EMP_TITLE                  VARCHAR2(9)             OUT
     DEPT_NAME                  VARCHAR2(14)            OUT
     EMP_HIRE_DATE              DATE                    OUT
     SALARY                     VARCHAR2(20)            OUT
     REPORT_DATE                DATE                    OUT

scott@dv2> 



Certainly I can now see the full definition of the Record to be returned by the REF CURSOR but what does this buy me. Maybe I "can't see the forest for the trees" but what is the advantage of one implementation over the other.

I've read through all comments in this topic and while it's alluded to, the pros and cons for the strong-typed (plsql table Type/SQL Type) versus weak-typed REF CURSOR approach are never fully discussed; but specifically when the REF CURSOR is passed as a parameter - as outlined in my previous example.

As always, thanks and best regards
Tom Kyte
March 07, 2008 - 5:26 pm UTC

It provides more information (that is relevant)

It allows the client and this routine to be strongly bound, type checked, at compile time (reduces risk of run time error).

It is the same as any strongly typed language. What if plsql didn't have the NUMBER type, only the TO_NUMBER() function and you had to use a string for everything.

Can you imagine how often you would get:

ORA-01722: invalid number


So, we use strong typing in most all of our languages in order to avoid run time mess ups.

The strongly typed cursor says "I am expecting this specific structure as an input" or "I will give you this specific structure as an output", I will never send/give you anything else.

A weak ref cursor says "I'm expecting a result set, it might have 1 or a million columns - I don't know until runtime, I don't know their names, their types - nothing"


Strongly typed - I can have:

declare
l_record := that_cursor%rowtype;


to fetch into...


and so on.

Strong-typed versus Weak-typed REF CURSOR approach

Edward Girard, March 08, 2008 - 9:05 pm UTC


This is precisely the information I was looking for - thank you so much.

I've always used strongly-typed REF CURSORs and prefer them for the reasons you mentioned. As an expert in Oracle, your opinion and insight validates what I needed to settle a crucial design discussion. As is generally the case, what's faster and easier to do isn't always best.

weak typed REF CURSOR w/ Multiple Tables

Dave LeJeune, March 16, 2008 - 7:42 am UTC

Tom -

I've read through some of the thrads, and this particular one was recent and seemed to touch on what I am trying to solve. I calling a procedure that returns a REF CURSOR (of type sys_refcursor).

My dilemna is this cursor is a join across multiple tables and includes columns from those tables (table_a.*, table_b.*, table_c.*). When I attempt to iterate through this cursor in the calling procedure, I cannot of course use the %ROWTYPE

i.e., fetch O_RC into O_RC_ROW;

Where O_RC is my ref cursor returned from my procedure and O_RC_ROW is %ROWTYPE for table_a where I am trying to do something with it (in my calling procedure). I know you've mentioned that this wasn't supported in earlier versions of Oracle (we are on 10gR2).

With the research I've done, I've seen people have suggested creating a view - that would let me use %ROWTYPE I believe (though I haven't tested it yet).

Thanks - and I'll take by beating if you have already addressed this exact issue in another thread :)

=-Dave


Tom Kyte
March 24, 2008 - 8:20 am UTC

... I cannot of course use the
%ROWTYPE
...

that is not true. You'd have to set up with I call a "template cursor" that selects out the same types of columns - but needs no join or predicates.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:948277183607

weak typed REF CURSOR w/ Multiple Tables

Edward Girard, March 20, 2008 - 4:59 pm UTC


Dave,

The %ROWTYPE attribute will not work when joining multiple tables as %ROWTYPE represents a row in a database table.

I believe the solution to your question is in my previous post dated March 5, 2008. I used a (strong) Record Type Declaration (see: get_Emp_Dept_recType). This solution implements 2 tables; you'll have to add the 3rd.

A similar approach for you would be this:

scott@dv2> CREATE OR REPLACE PROCEDURE get_Emp_Dept_Strong2 IS
  2  
  3     -- Record Type Declarations
  4     TYPE get_Emp_Dept_recType IS RECORD(
  5        emp_name      emp.ename%TYPE,
  6        emp_title     emp.job%TYPE,
  7        dept_name     dept.dname%TYPE,
  8        emp_Hire_date emp.hiredate%TYPE,
  9        salary        VARCHAR2(20),
 10        report_date   DATE);
 11     TYPE emp_Dept_curType IS REF CURSOR RETURN get_Emp_Dept_recTy
 12  
 13     -- Cursor Variable Declaration
 14     emp_Dept_curVar emp_Dept_curType;
 15  
 16     -- Cursor Record Type Declaration
 17     emp_Dept_curRec get_Emp_Dept_recType;
 18  
 19  BEGIN
 20  
 21     DBMS_OUTPUT.enable(1000000);
 22  
 23     OPEN emp_Dept_curVar FOR
 24        SELECT e.ename,
 25               e.job,
 26               d.dname,
 27               e.hiredate,
 28               to_char(e.sal, '$99,999.99') AS sal,
 29               SYSDATE AS today
 30        FROM   emp  e,
 31               dept d
 32        WHERE  e.deptno = d.deptno;
 33  
 34     LOOP
 35        FETCH emp_Dept_curVar
 36           INTO emp_Dept_curRec;
 37        EXIT WHEN emp_Dept_curVar%NOTFOUND;
 38        dbms_output.put_line(emp_Dept_curRec.emp_name || ' is ' ||
 39                             emp_Dept_curRec.emp_title);
 40     END LOOP;
 41     CLOSE emp_Dept_curVar;
 42  
 43  END get_Emp_Dept_Strong2;
 44  /

Procedure created.


scott@dv2> set autoprint on

scott@dv2> exec get_Emp_Dept_Strong2
CLARK is MANAGER
KING is PRESIDENT
MILLER is CLERK
JONES is MANAGER
FORD is ANALYST
ADAMS is CLERK
SMITH is CLERK
SCOTT is ANALYST
WARD is SALESMAN
TURNER is SALESMAN
ALLEN is SALESMAN
JAMES is CLERK
BLAKE is MANAGER
MARTIN is SALESMAN

PL/SQL procedure successfully completed.



Ed

Processing a ref cursor in another procedure

Rick, May 08, 2008 - 6:04 pm UTC

I've got a package with stored procedures for reporting. In one of the procedures, I want to run another report and process that data. I'm running into a compiling problem when I do.
create type my_rec is object
(
  column_a varchar2(50),
  column_b varchar2(50),
  ...
  column_z varchar2(50)
)
;

create type my_table as table of my_rec;

create or replace package report_test is
  procedure Report1(r_cursor IN OUT sys_refcursor);
  procedure Report2(r_cursor IN OUT sys_refcursor);
end report_test;

create or replace package body report_test is

  procedure Report1(r_cursor IN OUT sys_refcursor) is
    rec my_rec;
    tbl my_table;
  begin
    tbl := my_table();
    tbl.extend;
    tbl(1) := my_rec('A', 'B', ... , 'Z');
    open r_cursor for
      select *
      from table(cast(tbl as my_table));
  end Report1;

  procedure Report2(r_cursor IN OUT sys_refcursor) is
    rec my_rec;
    tbl my_table;
    r_report1 sys_refcursor;
    r1_rec my_rec;
    recnum integer := 0;
  begin
    Report1(r_report1);
    loop
      fetch r_report1 into r1_rec;
      exit when r_report1%NOTFOUND;
      recnum := recnum + 1;
      tbl.extend;
      tbl(recnum).column_a := r1_rec.column_a || '01';
      tbl(recnum).column_b := r1_rec.column_b || '02';
      ...
      tbl(recnum).column_z := r1_rec.column_z || '26';
    end loop;
    close r_report1;

    open r_cursor for
      select *
      from table(cast(tbl as my_table));
  end Report2;
end Report_Test;

I get a PLS-00382 error, which I understand from other questions is because the ref cursor returns 26 scalars, not a type. However, it does not seem practical to define scalar variables to fetch into.
declare
  v_column_a varchar2(50);
  v_column_b varchar2(50);
  ...
  v_column_z varchar2(50);
begin
  ...
  fetch r_report1 into v_column_a, v_column_b, ..., v_column_z;

Also, if I need to change the my_rec type later, I have to remember to change the scalar variables and the fetch statement. It seems like there ought to be a way for me to link the my_rec type programatically to this fetch statement so that it's always in sync. Do I have a better option?
Tom Kyte
May 12, 2008 - 10:06 am UTC

select yourType( .... ) 
  from table(cast(tbl as my_table));



so, cast your scalars as a type when you fetch them, you get what you select....

Return values from a PL/SQL table into a ref cursor

Manish, May 12, 2008 - 6:14 pm UTC

PL/SQL procedure executes successfully but when we want to print the result (the REF CURSOR) SQL Plus hangs.
Tom Kyte
May 13, 2008 - 10:21 am UTC

sigh, not an example in site to try....

Well, I can prove you are not always right, using your code (modified to handle the TYPE)

ops$tkyte%ORA10GR2> create type my_rec is object
  2  (
  3    column_a varchar2(50),
  4    column_b varchar2(50),
  5    column_z varchar2(50)
  6  )
  7  /

Type created.

ops$tkyte%ORA10GR2> create type my_table as table of my_rec
  2  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package report_test is
  2    procedure Report1(r_cursor IN OUT sys_refcursor);
  3    procedure Report2(r_cursor IN OUT sys_refcursor);
  4  end report_test;
  5  /

Package created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package body report_test is
  2
  3    procedure Report1(r_cursor IN OUT sys_refcursor) is
  4      rec my_rec;
  5      tbl my_table;
  6    begin
  7      tbl := my_table();
  8      tbl.extend;
  9      tbl(1) := my_rec('A', 'B', 'Z');
 10      open r_cursor for
 11        select my_rec( column_a, column_b, column_z )
 12        from table(cast(tbl as my_table));
 13    end Report1;
 14
 15    procedure Report2(r_cursor IN OUT sys_refcursor) is
 16      rec my_rec;
 17      tbl my_table := my_table();
 18      r_report1 sys_refcursor;
 19      r1_rec my_rec;
 20      recnum integer := 0;
 21    begin
 22      Report1(r_report1);
 23      loop
 24        fetch r_report1 into r1_rec;
 25        exit when r_report1%NOTFOUND;
 26        recnum := recnum + 1;
 27        tbl.extend;
 28        tbl(recnum) := my_rec( r1_rec.column_a || '01',
 29                               r1_rec.column_b || '02',
 30                               r1_rec.column_z || '26' );
 31      end loop;
 32      close r_report1;
 33
 34      open r_cursor for
 35        select *
 36        from table(cast(tbl as my_table));
 37    end Report2;
 38  end Report_Test;
 39  /

Package body created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable x refcursor
ops$tkyte%ORA10GR2> exec report_test.report1(:x)

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print x

MY_REC(COLUMN_A,COLUMN_B,COLUMN_Z)(COLUMN_A, COLUMN_B, COLUMN_Z)
-------------------------------------------------------------------------------
MY_REC('A', 'B', 'Z')

ops$tkyte%ORA10GR2> exec report_test.report2(:x)

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print x

COLUMN_A
--------------------------------------------------
COLUMN_B
--------------------------------------------------
COLUMN_Z
--------------------------------------------------
A01
B02
Z26


object values over a dblink... ?

Remi Dumont, May 23, 2008 - 2:13 pm UTC

To follow on the same topic, how would it be done through a dblink...


I work with 2 oracle instances on two different servers.
I'll like to query over the first instance from the second instance using some package functions and retrieve objects and collection of objects values.
I found many articles about this but none are giving a clear working solution.
Having it working locally is pretty easy.
Doing it over a dblink seems to be a different challenge...


Here is what I have done so far:

SQL_USER_A_SRV_1> show rel
release 1002000300
SQL_USER_A_SRV_1> SELECT SYS_OP_GUID() FROM DUAL;

SYS_OP_GUID()
--------------------------------
4DE61079BABE8848E040A8C03F01767E

SQL_USER_A_SRV_1> CREATE or replace type T_TESTOBJ 
  2  OID '4DE61079BABE8848E040A8C03F01767E' 
  3  as object
  4  (
  5      testobjno number,
  6      testobjname varchar2(32)
  7  );
  8  /

Type created.

SQL_USER_A_SRV_1> SELECT SYS_OP_GUID() FROM DUAL;

SYS_OP_GUID()
--------------------------------
4DE61079BAC78848E040A8C03F01767E

SQL_USER_A_SRV_1> CREATE OR REPLACE type T_TESTOBJ_TAB 
  2  OID '4DE61079BAC78848E040A8C03F01767E'
  3  as table of T_TESTOBJ;
  4  /

Type created.

SQL_USER_A_SRV_1> create or replace package testobjpck as
  2  
  3      function testobj( p_param number ) return T_TESTOBJ_TAB;
  4  
  5      function test_one_obj( p_param number ) return t_testobj;
  6  
  7      function testnum return number;
  8  
  9  end;
 10  /

Package created.

SQL_USER_A_SRV_1> create or replace package body testobjpck as
  2  
  3      function testobj( p_param number ) return T_TESTOBJ_TAB is
  4      
  5          l_collection t_testobj_tab;
  6      
  7      begin
  8  
  9          select 
 10            cast (
 11        multiset (
 12                  select rownum as testobjno,
 13                         substr( 'abcdefghijklmnopqrstuvwxyz', rownum, 1 ) as testobjname
 14                    from all_objects
 15                   where rownum <= p_param
 16                 ) as t_testobj_tab
 17                 )
 18            into l_collection
 19            from dual;
 20            
 21          return l_collection; 
 22      
 23      end;
 24  
 25      function test_one_obj( p_param number ) return t_testobj is
 26      begin
 27  
 28          return t_testobj( p_param, substr( 'abcdefghijklmnopqrstuvwxyz', p_param, 1 ) );
 29      
 30      end;
 31  
 32      function testnum return number is
 33      begin
 34          return 8;
 35      end;
 36      
 37  end;
 38  /

Package body created.

SQL_USER_A_SRV_1> select testobjpck.testnum from dual;

   TESTNUM
----------
         8

SQL_USER_A_SRV_1> select testobjpck.test_one_obj(10) from dual;

TESTOBJPCK.TEST_ONE_OBJ(10)(TESTOBJNO, TESTOBJNAME)
--------------------------------------------------------------------------------
T_TESTOBJ(10, 'j')

SQL_USER_A_SRV_1> select testobjpck.testobj(10) from dual;

TESTOBJPCK.TESTOBJ(10)(TESTOBJNO, TESTOBJNAME)
--------------------------------------------------------------------------------
T_TESTOBJ_TAB(T_TESTOBJ(1, 'a'), T_TESTOBJ(2, 'b'), T_TESTOBJ(3, 'c'), T_TESTOBJ
(4, 'd'), T_TESTOBJ(5, 'e'), T_TESTOBJ(6, 'f'), T_TESTOBJ(7, 'g'), T_TESTOBJ(8,
'h'), T_TESTOBJ(9, 'i'), T_TESTOBJ(10, 'j'))

SQL_USER_A_SRV_1> select * from TABLE ( cast( testobjpck.testobj(10) as t_testobj_tab ) );

 TESTOBJNO TESTOBJNAME
---------- --------------------------------
         1 a
         2 b
         3 c
         4 d
         5 e
         6 f
         7 g
         8 h
         9 i
        10 j

10 rows selected.

SQL_USER_A_SRV_1> grant execute on testobjpck to user_b;

Grant succeeded.

SQL_USER_A_SRV_1> 



Now, that I have something working on the first instance, let's move on the second one...



SQL_USER_B_SRV_2> show rel
release 902000700
SQL_USER_B_SRV_2> create synonym testobjpck for user_a.testobjpck@srv_1;

Synonym created.

SQL_USER_B_SRV_2> select testobjpck.testnum from dual;

   TESTNUM
----------
         8



Communication is fine... So, let's continue...





SQL_USER_B_SRV_2> select testobjpck.test_one_obj(10) from dual;
select testobjpck.test_one_obj(10) from dual
*
ERROR at line 1:
ORA-30626: function/procedure parameters of remote object types are not
supported


SQL_USER_B_SRV_2> select * from TABLE ( cast( testobjpck.testobj(10) as t_testobj_tab ) );
select * from TABLE ( cast( testobjpck.testobj(10) as t_testobj_tab ) )
*
ERROR at line 1:
ORA-30626: function/procedure parameters of remote object types are not
supported



Let's try to create the same types on the second instance...



SQL_USER_B_SRV_2> CREATE or replace type T_TESTOBJ 
  2  OID '4DE61079BABE8848E040A8C03F01767E'
  3  as object
  4  (
  5      testobjno number,
  6      testobjname varchar2(32)
  7  );
  8  /

Type created.

SQL_USER_B_SRV_2> CREATE OR REPLACE type T_TESTOBJ_TAB 
  2  OID '4DE61079BAC78848E040A8C03F01767E'
  3  as table of T_TESTOBJ;
  4  /

Type created.



Now, how these types (with same oid) work ?  
Like this ?  
How can we make these two queries work remotely ?
I read at lot of articles from different sources on the internet.
A few persons say that it is possible when object types are using the same oid.
But I still haven't found any code showing it.
Can you help...

SQL_USER_B_SRV_2> select testobjpck.test_one_obj(10) from dual;
select testobjpck.test_one_obj(10) from dual
       *
ERROR at line 1:
ORA-30626: function/procedure parameters of remote object types are not
supported


SQL_USER_B_SRV_2> select * from TABLE ( cast( testobjpck.testobj(10) as t_testobj_tab ) );
select * from TABLE ( cast( testobjpck.testobj(10) as t_testobj_tab ) )
*
ERROR at line 1:
ORA-30626: function/procedure parameters of remote object types are not
supported


SQL_USER_B_SRV_2>

You are true Oracle Guru

Balvant Chhasiya, June 27, 2008 - 1:14 pm UTC

Dear Tom,

You are true Oracle Guru. Your knowledge in Oracle domain is very vast and superb.

Thanks a ton for sharing knowlege.

As usual this thread is very useful.

Regards,
Balvant.


Any way to do this in Reports?

Jeff, April 16, 2010 - 3:15 pm UTC

I'm hoping you know how to get reports to use this.

database developer

dave rincon, December 13, 2010 - 6:00 pm UTC

hi guys, i have a problem, i can't create objects just i declare.
i need use a function but when i use have a error...
"invalida datatype"

select *
from the ( select cast( lr_datos_personales('C',1656174) as lr_datos_personales )
from dual ) a;

or "function datos_personales may not be used in SQL"

select * from table(datos_personales(entsex, entnac));

this is the all script...

declare
entnac number:= 1656174;
entsex varchar(1):= 'C';

type lr_datos_personales is table of cart_clientes%rowtype;
datos lr_datos_personales;

function datos_personales (nac in varchar, crif in number)
return lr_datos_personales
is
begin

select *
bulk collect into datos
from cart_clientes cc
where cc.cacn_cd_nacionalidad = nac
and cc.cacn_nu_cedula_rif = crif;
return datos;

end datos_personales;

begin
select * from table(datos_personales(entsex, entnac));
------------------------------------------------------
dbms_output.put_line(datos_personales(entsex, entnac)(1).cacn_cd_sexo);
dbms_output.put_line(datos_personales(entsex, entnac)(1).cacn_fe_nacimiento);
end;
-- cuando la leo directo no me marca error
-------------------------------------------------------
sorry whit my english isn't very good
Tom Kyte
December 14, 2010 - 7:15 am UTC

you have to declare the object type at the SQL level - outside of plsql - if you want sql to be able to see it.


create type cart_clientes_type as object ( .... );

create type lr_datos_personales as table of cart_clientes_type;

begin
... your code here ....

Function

dave rincon, December 14, 2010 - 8:45 am UTC

Hello and tanks for your answered,
my problem is i can't create object, noting
and the type object can't be declared
when i declared the object the message is ...

object not soported in this context ....
Tom Kyte
December 14, 2010 - 1:03 pm UTC

you have to CREATE THE OBJECT in sql - outside of plsql

and then you can use it. anything you want SQL to see has to appear in the data dictionary, it cannot be buried, cannot be hidden in plsql

if you "cannot" then you "will not" be doing this. It is the way it works. the function has to be visible, the types as well.

ops$tkyte%ORA11GR2> create table cart_clientes
  2  (  x number,
  3     y date,
  4     z varchar2(30)
  5  );

Table created.

ops$tkyte%ORA11GR2> insert into cart_clientes select user_id, created, username from all_users;

38 rows created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE TYPE CART_CLIENTES_TYPE AS OBJECT
  2  ( X NUMBER, Y DATE, Z VARCHAR2(30) );
  3  /

Type created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE TYPE LR_DATOS_PERSONALES AS TABLE OF CART_CLIENTES_TYPE;
  2  /

Type created.

ops$tkyte%ORA11GR2> create or replace
  2    function datos_personales (nac in varchar, crif in number)
  3             return lr_datos_personales
  4    is
  5          datos LR_DATOS_PERSONALES;
  6    begin
  7  
  8      select cart_clientes_type( x,y,z)
  9      bulk collect into datos
 10      from cart_clientes cc;
 11      return datos;
 12  
 13    end datos_personales;
 14  /

Function created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2    entnac number:= 1656174;
  3    entsex varchar(1):= 'C';
  4  begin
  5    for x in ( select * from table(datos_personales(entsex, entnac)) )
  6    LOOP
  7          dbms_output.put_line( x.x );
  8          dbms_output.put_line( x.y );
  9          dbms_output.put_line( x.z );
 10          exit;
 11    end loop;
 12  end;
 13  /
95
07-DEC-10
OPS$ORA11GR2

PL/SQL procedure successfully completed.

using plsql table as out parameter in a function

Bhaskar, June 01, 2012 - 6:27 am UTC

Hi Tom,

I'm doing the below code.
create package pkg

type t_rec is recod (id number,id_name varchar2(10));


type t_data is table of t_rec index by binary_integer;



procedure return_data is (o_rec out t_data,dept number,region number) ;


create package body pkg


procedure return_data is (o_rec out t_data,dept number,region number);




p_cur sys_refcursor;
l_text varchar2(100);
indx number:=0;
l_text varchar2(1000):=null; -- i have built some dynamic where clause in this variable
begin


t_data:=&t_data -- i passed 10
dept :&dept -- ipassd 11



open p_cur for 'select id,id_name from tab1'||l_text';
loop
fetch p_cur into o_rec(indx).id,o_rec(indx).id_name;
exit when p_cur%notfound;
dbms_output.put_line('id is '||o_rec(indx).id); ---- 4
indx:=indx+1;
end loop;
end return_data;


..
exception ....
end pkg;

Now --4 is giving me data here.
But when i wrote the below code to take the data it's not giving me any data.



---1
DECLARE
bk_data PKG.T_DATA;
BEGIN
PKG.RETURN_DATA(bk_data, 10, 11);
dbms_output.put_line('id is '||bk_data(1).id); -- No data
end;

I'm not sure why it is not giving me any data in ----1.
I haven't closed the ref cursor there.

Can you please help me .
Thanks in advance.



Tom Kyte
June 02, 2012 - 2:34 am UTC

there is no code here as far as I can tell. snippets of code, but no code.

present the entire example, the one that actually compiles.

passing a pl/sql table from a procedure

Bhaskar, June 02, 2012 - 10:08 am UTC

Hi Tom,

Actually i can't post the full code.
But i need to achieve the below thing.

I need to create a record in a package.
I need to populate this record type variable from a ref cursor.Because on the basis of the in parameter value i need to build a algorithm to build a dynami where clause.
I need to pass this record from a out parameter of a
package procedure.

Now when i did this above thing in the code i posted i could see this record type variable is getting populated(a per o/p from dbms_output).

But when i tried to call this packaged function by passing sae values(as i posted above) it's not givin me data.

Please help me in this.

Thanks in advance.
Tom Kyte
June 04, 2012 - 8:44 am UTC

and actually, i clearly asked for "the code"

I don't know why developers today cannot do this, cannot frame a small teeny tiny example to demonstrate with, very frustrating. Leaves a LOT of guessing which is just a waste of time in general.


It is hard to read your code, I have no CLUE what your data is, I have no idea what output you are seeing.

You have made this really *hard*, something that should be so simple - really hard - because you cannot be bothered to put together a small, simple test case that is complete.

I believe the bug here is in your indx variables. YOU started subscripting from ZERO, but in your display routine, you ask for subscript 1.

Here is my working example. I will say this - I DOUBT you need dynamic sql, I seriously 100% doubt it. And it looks like you are going to do dynamic sql by string concatenation without using bind variables!!!! that is the worst of the worst. Why don't you post your full example demonstrating WHY you need dynamic sql and show us how you intend to do it - so we can fix that for you too...

ops$tkyte%ORA11GR2> create or replace package my_pkg
  2  as
  3      type t_rec is record( id number, id_name varchar2(30) );
  4      type t_data is table of t_rec index by binary_integer;
  5  
  6      procedure get_data( p_rec out t_data, p_deptno in number, p_region in number );
  7  end;
  8  /

Package created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace package body my_pkg
  2  as
  3  
  4      procedure get_data( p_rec out t_data, p_deptno in number, p_region in number)
  5      is
  6          l_cur sys_refcursor;
  7      begin
  8          open l_cur for 'select user_id, username from all_users where rownum <= 5';
  9          fetch l_cur bulk collect into p_rec;
 10          close l_cur;
 11      end;
 12  
 13  end;
 14  /

Package body created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2      l_data my_pkg.t_data;
  3  begin
  4      my_pkg.get_data( l_data, null, null );
  5      for i in 1 .. l_data.count
  6      loop
  7          dbms_output.put_line( l_data(i).id || ', ' || l_data(i).id_name );
  8      end loop;
  9  end;
 10  /
0, SYS
5, SYSTEM
9, OUTLN
21, ORACLE_OCM
30, DBSNMP

PL/SQL procedure successfully completed.


Best way to do the opposite?

A reader, August 09, 2012 - 12:47 pm UTC

In the first example, you pull rows from a table, stick them in a table object and pass it back as a ref cursor; what would be the best option to do the opposite?

In the particular project I am working on, the developers want to pass me a type similar to the one you defined in the first post with X records, blow away all the records in the table and insert all of the new records they passed in. In the future, I am expecting that they will only want to blow away a certain set of records, like those associated with a particular customer.

1) With performance and concurrency being the biggest concerns, what would be the best way to do this?

2) Is there a good way to bulk load the data so I don't loop through each one and run an insert for each record?

3) Is there a better way than deleting all of the records and reinserting everything? In this particular scenario, it is likely that a very small percentage of the data will actually be different.

4) This table is going to be quite small (less than 100 records), but other tables that need this implementation may have millions of rows. Is there a different approach I would want to take for each based on the size of the table?
Tom Kyte
August 17, 2012 - 1:38 pm UTC

you can simply


select * from table( their_object_type );


ops$tkyte%ORA11GR2> create or replace type myScalarType as object
  2  (   x     int,
  3      y    date,
  4      z    varchar2(25)
  5  )
  6  /

Type created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace type myTableType as table of myScalarType;
  2  /

Type created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace procedure p( p_data in myTableType )
  2  as
  3  begin
  4          for x in (select * from table(p_data) )
  5          loop
  6                  dbms_output.put_line( x.z );
  7          end loop;
  8  end;
  9  /

Procedure created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2          l_data myTableType := myTableType( myScalarType( 1, sysdate, 'hello' ),
  3                                             myScalarType( 2, sysdate, 'world' ) );
  4  begin
  5          p( l_data );
  6  end;
  7  /
hello
world

PL/SQL procedure successfully completed.




so, all you need to do is

a) figure out whether this table needs to exist over sessions - to determine if you are using a global temporary table or a real one

b) if real, then delete from table, insert as select into table - done.



if you are doing this for millions of rows, STOP STOP STOP. stop right now. You do not send millions of rows as a single object instance , that would be nuts!

Can we make column names dynamic

Raghu, January 07, 2013 - 7:58 am UTC

Tom,

Thanks for all your valuable inputs.
I am using the above method to return data in a table array, so that i can query as a table. (select * from table(my_pkg.pivot))

But for my reporting query, column names are dynamic, using the above method, the column names will be as defined in object. How can we make column names dynamic.

Below is small demo:

I am pivoting the data based on a DATE Column, so my output should look like:

CODE 1-Jan-2012 2-Jan-2012 3-Jan-2012
ABC 1.2 2.3 3.4
PQR 3.4 4.5 6.6

If i define object as

CREATE TYPE PIVOT_OBJ AS OBJECT(CODE VARCHAR2(100,DATE_1 DATE,DATE_2 DATE,DATE_3 DATE);

My ouptut will be:

CODE DATE_1 DATE_2 DATE_3
ABC 1.2 2.3 3.4
PQR 3.4 4.5 6.6

So DATE_1 will not have any meaning in this case.
Bascially i am using your my_pkg.pivot procedure to pivot the data.

The above output will be used by a Java program to generate a excel report.
I don't have option of using ref cursor, since we dont want to change the framework.

Can you please guide me how this can be achieved.

Thanks.













Tom Kyte
January 14, 2013 - 11:28 am UTC

if you need different column names for each query execution...

you need a different query


that is, you'd need a query for each query with a different set of names. Identifiers are compiled into the statement.

this is like asking for a Java method that is not overloaded to be able to decide what parameter names it wanted to use for each call as the call was made.


I don't have option of using ref cursor, since we dont want to change the
framework


I take exception to the way you said that. You should say in the future:

we have purposely decided to not explore paths that might get us to where we want to go because we have decided that changing SOFTWARE is something we won't do. We agree to live with all of the complications associated with NOT CHANGE *SOFT*WARE


(remember why it is called SOFT ware and not HARD ware) You have a framework that doesn't do what you need and you refuse to consider changing it??? very frustrating....

Super useful

Victor, May 02, 2021 - 10:37 am UTC

Thanks a lot!! This was exactly what i needed to import remote tables having LOB data types (you need to cast the LOB as varchar first though)

Thanks!!

Lucas Celestrino, December 05, 2023 - 2:47 pm UTC

Excelent, thanks for your help, with an extremely simple example!
Chris Saxon
December 06, 2023 - 1:32 pm UTC

You're welcome

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