Skip to Main Content
  • Questions
  • PL/SQL API for Java/VB - Return Data Arrays or Result Sets

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Michael.

Asked: December 24, 2004 - 4:17 pm UTC

Last updated: June 15, 2005 - 9:56 am UTC

Version: 10.1.0.3

Viewed 1000+ times

You Asked

Tom,

I working on generating an API to open up our database for any client to access. We are finally starting to use the database as a database and not just a bit bucket with access being restricted.

To provide data to these clients in a variety of languages we've been discussing whether its more flexible and/or performant to result either a resultset/dynaset and let the client fetch data into arrays for display or do bulk collects in pl/sql and return filled data arrays. The resultset seems cleaner as the client can retrieve rows and fill its own data arrays at whatever batchsize is appropriate. But just returning the data arrays would seem to make less work for each client.

I've seen you use both methods in your examples. Do you have a general preference based on your experiences?

Thanks

and Tom said...

This is an excerpt from my book "Effective Oracle by Design" (to say I have a general preference would be an understatment :)

<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:

o Ease of programming Every language can deal with a result set—a cursor.
o Flexibility 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.
o Performance 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
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>

Rating

  (11 ratings)

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

Comments

Confirms my thoughts -- Thanks

Michael Smith, December 25, 2004 - 1:44 pm UTC

select appropriate_greeting from holiday_cheer_tbl where recipient = 'Tom Kyte';

just a bit more light!

aman, December 27, 2004 - 8:54 am UTC

sir
i have been asked by a VB developer this.
If we have a Grid of 7 rows in the form of VB accepting,three diff column values like Prod.Code,Prod.Description and Price then when the Grid will fill and we will press the Save Button(Build by us(this developer)),VB will send then each row one by one to oracle and then if there is a Procedure or Function accepting this value and inserting them in some table in DB,then Oracle will respod after each insert and then that means after all the 7 inserts succeeded ,then only will the user get a Records Successfully Saved message or something like this.this wil take ,for each row if it takes 1 second to insert ,then after 21 seconds(7 rows*3 Columns) this end message will confirm the commit.I want to send this data in one bulk to Oracle wht should i do?
Sir
(1)is Oracle really will take 21 seconds to insert the results into the table?i am sure that this is not the case as this matrix 21(7*3) is not correct at all.But according to this developer ,Oracle is taking too much time if he is using a procedure to insert the values in the table?Please Explain in both waysas you always do.
(2)the Answer to the 2nd approach of his doubt will be The approach you have just shown to collect the Bulk array from the front-end of an Java and pass it to the Oracle Procedure?
Sir please do not getangry if by mstake i have asked any new queestion here.
Thanx a Bunch sir !You are the best among the bests


Tom Kyte
December 27, 2004 - 10:23 am UTC

1) no, is sounds like "will VB really take 21 seconds". slow by slow is slow by slow. If VB is doing things row by row, we can only go as fast as VB gives us the data.



Stored procedure PL/SQL table Vs Result sets

KEN, January 10, 2005 - 5:28 pm UTC

I think this is quite elaborative and with given examples, it serves the purpose completly.

But this has just lead me to a thinking that if oracle is keeping it in memory and client is fetching it bunch by bunch, What would happen if data is really large say 20 Million rows or more.

Wouldnt Oracle will crumble down to keep all this in memory for every such query. say what if i have 30 such SP. dealing with different tables.

Tom Kyte
January 10, 2005 - 6:09 pm UTC

cursors are not "kept in memory"? not sure what you mean?

you can have thousands of users retrieving billions of rows at the same time. won't bother Oracle (might keep the IO system busy though)

Great,, but....how to return a static cursor?

Bipin, February 15, 2005 - 7:53 pm UTC

I have been using and reading a lot about ref cursors. Sometimes I wonder I am using ref cursors when it is not called for? I just thought I would take your help in refining my understanding about this issue.

Understanding:

We must use REF CURSOR whenever we want to return a 'cursor' like result set and not a 'collection' like result set.

Is my understanding correct? The reason I am asking is as you have been always saying ref cursors are not as good as static cursors in terms of parsing costs. I am weighing my options of using static cursor where I really do not need (?) ref cursor because of non-dynamic nature of SQL but I am unable to find a way to return a 'cursor' like result set to the calling program when I use a static cursor.

BTW, I recently bought your book. Thanks a bunch for authoring and making available such a great book!

Tom Kyte
February 16, 2005 - 7:40 am UTC

you use ref cursors to return result sets to clients.


if you do not *need* to use a ref cursor, you should not (eg: if plsql opens the cursor, plsql processes the rows -- do not use a ref cursor unless you absolutely HAVE to use dynamic sql)



Are ref cursors a good "best practice"?

Matt, February 16, 2005 - 9:07 am UTC

I have been thinking a little about this approach. Basically, I have been trying to come up with a best practice for system development. I had imagined that I might be able to design a system with a data access layer. This layer would be a set of wrappers to SQL and would return result sets. These would be called from clients (java, or whatever), but also re-used as necessary by the PL/SQL (say if there was a number crunching component). Problems taht I see are:

* By using these interfaces within PL/SQL, I am off setting code re-use and maintainability against reduced performance
* A ref cursor gets parsed on each call (although correctly setting session_cached_cursors should offset this)

Is this a fair best practice, given my understanding of the trade-offs?

Best Regards,


Tom Kyte
February 16, 2005 - 9:27 am UTC

I have a theory.

The best database programs on the planet do not contain the words "select", "insert", "update", "delete", and "merge" in them.

they contain "call" or "begin/end"

and performance is massively improved in such a system -- sure, maybe they soft parse more than they should be at least they are NOT hard parsing like mad (assuming we can get the coders to bind the calls to the stored procedures -- and even if they don't the procedures they call will do 5-10 sql's perhaps that are nicely bound so instead of hard parsing 5-10 sql's for each transaction, they only do 1 -- that is a start)


it is like instrumentation of code. People say 'but that is a performance hit, we don't want that in production'

Me -- I think the code has to be heavily instrumented before going into production, the best code in production has every other line being "debug, trace, instrumentation". And that is the only thing that lets the code run fast. You need proof for that? Ok, imagine Oracle in production with:

a) no sql_trace=true ability
b) no v$ tables -- NONE, no v$waitstat, no statspack, no v$sql, no v$sql_plan, nothing
c) no events for tracking down that spurious error that doesn't reproduce in test, nothing


You would be blind, you would have NO CHANCE of fixing or even figuring out what the problem is on such a system. Oracle is heavily instrumented -- excessively instruemented. Would the binaries run faster without this instrumentation? I say NO, resoundingly NO! Why? Because we would have no abilities to find out "what is wrong"


So, same thing applies here. I would take most definitely the lesser evil of some soft parsing that I could avoid simply to have the code in the database so I can actually

a) make sure it uses binds

b) is implemented transactionally correct (GUI programmers do not make the best OLTP programmers, they do really good GUI's -- but they are not database, transactional programmers -- many of them think "autocommit" in jdbc/odbc is a "good thing" (it is HORRIBLE)

c) when it "goes slow", we can actually find out why really fast and then fix it. How many systems have I seen where I point to a query in v$sql and say "who is running that query, we need to fix it now" and the answer is "gee, I don't know, not mine, nope -- i didn't write that, not me, me neither..." and you cannot even grep the code for it since the query is a string lovingly put together a bit at a time or even worse, constructed by some "database independent layer whose intention is to hide this complex thing called a database from the coders" (funny, j2ee isn't deemed horribly complex but SELECT is, oh well)

d) it maximizes code reuse far beyond any other technology I've ever seen. For you see, if something can connect to the database and run "select", they can connect and run "stored procedure" meaning all of a sudden, anything on the planet can run my well formed, tested, performant tranactions -- incredible code reuse.



so don't even call it a trade-off, call is "the safest thing to do"

Exactly what I was looking for...

Bipin, February 17, 2005 - 1:32 pm UTC

It clarified a lot especially answer to Matt's question was what I was looking for. Thanks Matt for asking correct question! I can use data access layer built around ref cursors without any guilt now!

Thanks for the answers Tom!

ORA-06513

Harshad, May 07, 2005 - 2:00 am UTC

Hi Tom,


We need your help, We are facing problem related to PL-SQL ORA-06513 on our production database, It was occuring very frequently (weekly). We are not getting any alert in the alert log , but when we rebounce our application , our all transactions are starts moving.


Looking forward a prompt response from you.


Regards,
Harshad




Tom Kyte
May 07, 2005 - 8:05 am UTC

[tkyte@localhost tkyte]$ oerr ora 6513
06513, 00000, "PL/SQL: index for PL/SQL table out of range for host language array"
// *Cause: An attempt is being made to copy a PL/SQL table to a host language
// array. But an index in the table is either less than one or
// greater than the maximum size of the host language array.
// When copying PL/SQL tables to host language arrays, the table
// entry at index 1 is placed in the first element of the array, the
// entry at index 2 is placed in the second element of the array, etc.
// If an table entry has not been assigned then the corresponding
// element in the host language array is set to null.
// *Action: Increase size of host language array, or decrease size of PL/SQL
// table. Also make sure that you don't use index values less than 1.
//


could it be that by bouncing your database, you are bouncing your application and because of that, your application is working OK until it hits an error in it's logic?

This looks like it could likely be an application issue more than anything else.

as for "are ref cursors a good 'best practice'?

A reader, May 23, 2005 - 12:31 pm UTC

(why) would you prefer stored procedures providing ref cursors to having those queries stored within say definition files, separated from the application code as well?

are there any differences between those two approaches in terms of performance, resource consumption, ...?

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

I have control over them in the database.

I have the dependencies setup (in the database, I can tell you "API/Transaction X relies on tables T1, T2, and T3)

I have ease of modification in the database (change the size of that column from 3 to 5 -- all of the plsql "fixes" itself)

I have centralization.

I have backup and recovery

I have everything a database provides.

ref cursors and memory

GMA, June 13, 2005 - 1:13 pm UTC

A ref cursor is a pointer, isn't it? So say I have a PLSQL procedure that opens a refcursor. A Java program invokes the PLSQL procedure, processes data and is done. Wouldn't the cursor need to be closed? How is this done?

I have tried to find documentations that explains the innards of ref cursors versus collections and how they both work in terms of memory, data retrieval and so on, but haven't found much. any pointers?

Regards

Tom Kyte
June 13, 2005 - 1:33 pm UTC

a ref cursor is a "pointer"
a cursor is a "pointer"

The cursor is always closed by the client. Java would call plsql, plsql would return the cursor, java would close the cursor.


a ref cursor is just the ability to pass a cursor back and forth. It is not very much different physically from a cursor opened directly by java itself.

It is just more secure, manageable, tunable than a cursor opened directly by java itself.

Considering other concerns...

Jonathan Wallace, June 15, 2005 - 7:36 am UTC

Tom,

In your response, I did not see where you considered the following:

A. Using Oracle Object Types with or without Pipelining
B. Do REF Cursors require a dedicated connection, or can other users reuse this connection, while the REF CURSOR is open. Does this even matter?

The reason for asking question B is that I want to understand if there is a system load detriment caused by the REF Cursor being open and not being able to reuse that connection until the REF Cursor is closed.

Does this make sense?

Thank you for your response in advance.

Tom Kyte
June 15, 2005 - 9:56 am UTC

1) using them with pipeling would be returning a ref cursor, so it was in fact "covered".

Using them without pipeling would have lots of the side effects of plsql table types and would be "not the right way to go" again. Why pack up the equivalent of an array fed from a query -- when you can in fact return just the query???

2) ref cursors would have to be fetched from in the session that opened it. but again, why would you have

a) plsql fetch the data
b) plsql allocate memory in the server to put into an object
c) write all of the code in the client to deal with the object type
d) allocate all of the memory in the client to hold the objects

when you could

a) plsql opens ref cursor
b) client fetches and puts data into an array if that is what it wants.



Pipelined Table Function is faster than Store Procedure return Ref Cursor, and use less Latches

Charlie Zhu, May 17, 2006 - 7:18 pm UTC

I just wonder why I got 50% more logical reads when call the PL/SQL Store Procedure to return Ref Cursor.

Run1 is Store Procedure, Run2 is Table Function.

Run1 ran in 341 hsecs
Run2 ran in 172 hsecs
run 1 ran in 198.26% of the run2 time

STAT...consistent gets 90,010 60,012 -29,998
STAT...consistent gets from ca 90,010 60,012 -29,998
STAT...no work - consistent re 90,000 60,000 -30,000
STAT...cluster key scan block 90,000 60,000 -30,000
STAT...buffer is not pinned co 90,000 60,000 -30,000
STAT...session logical reads 90,072 60,065 -30,007
STAT...recursive calls 64,003 4,004 -59,999
LATCH.cache buffers chains 180,277 120,266 -60,011
STAT...session pga memory max 196,608 65,536 -131,072
STAT...session uga memory max 196,500 61,996 -134,504

Run1 latches total versus Run2 -- difference and pct
Run1 Run2 Diff Pct
202,749 146,812 -55,937 138.10%

PL/SQL procedure successfully completed.


Here is the objects creation SQL scripts:
== == ===
CREATE CLUSTER abelisting.book_clus(listingsid NUMBER(20,0))
SIZE 23 SINGLE TABLE
HASH IS listingsid
pctfree 5
HASHKEYS 5000
tablespace data_auto
--HASHKEYS 20000000
--tablespace abe_data_8m;

CREATE TABLE abelisting.book_del_check (
listingsid NUMBER(20,0), --PRIMARY KEY,
upd_dd NUMBER(2,0)
)
CLUSTER abelisting.book_clus (listingsid);

CREATE OR REPLACE PACKAGE ABELISTING.sp_sql_query AS
/******************************************************************************
NAME: sp_sql_query
PURPOSE:

REVISIONS:
Ver Date Author Description
--------- ----------- --------------- ------------------------------------
1.0 11/May/2006 Charlie Zhu1 1. Create the package.
******************************************************************************/

TYPE nt_tab IS TABLE OF NUMBER;
TYPE ListIDRecTyp IS RECORD (listingid number(20));
TYPE listidcurtyp IS REF CURSOR RETURN ListIDRecTyp;
--SYS_REFCURSOR
--TYPE listidcurtyp IS REF CURSOR RETURN abelisting.book_del_check%ROWTYPE;

PROCEDURE list_df(p_cursor in out listidcurtyp, p_string VARCHAR2);
FUNCTION list_df_tabf(p_string VARCHAR2) return nt_type PIPELINED DETERMINISTIC;

END;
/


CREATE OR REPLACE PACKAGE BODY ABELISTING.sp_sql_query
AS
/* Java programmer utilities
*/
PROCEDURE list_df(p_cursor in out listidcurtyp, p_string VARCHAR2)
as
i pls_integer;
begin
open p_cursor for
WITH sq
as
(
SELECT substr(x, instr(x,',',1,level)+1, instr(x,',',1,level+1) - instr(x,',',1,level) -1) token
FROM (select ','||l_str||',' x,l_str from (select p_string l_str from dual))
CONNECT BY level <= length(l_str)-length(replace(l_str,',',''))+1
)
select a.listingsid
from abelisting.book_del_check a, sq
where a.listingsid = To_Number(sq.token);

END;

Function list_df_tabf(p_string VARCHAR2)
return nt_type
PIPELINED DETERMINISTIC
as
l_rec number;
begin
for c in (WITH sq
as
(
SELECT substr(x, instr(x,',',1,level)+1, instr(x,',',1,level+1) - instr(x,',',1,level) -1) token
FROM (select ','||l_str||',' x,l_str from (select p_string l_str from dual))
CONNECT BY level <= length(l_str)-length(replace(l_str,',',''))+1
)
select a.listingsid listingsid
from abelisting.book_del_check a, sq
where a.listingsid = To_Number(sq.token))
loop
pipe row( c.listingsid);
end loop;

return;
End;

END;
/

Declare
l_string varchar2(4000);
--c_sp ABELISTING.sp_sql_query.listidcurtyp;
c_sp SYS_REFCURSOR;
l_i number;
Begin
--l_string := '6536119,6857511,410567,410698,696183,12740420,3742216,1198278,513323,151440,10214888,8387745,3212870,808131,798430,6273692,409569,1395308,3988346,4737101,3377464,95642';
l_string := '700000173,680000143,680000221,700000135,180000118,680000265,720000215,700000112,700000151,680000251,600000120,700000142,720000158,700000262,660000202,600000115,600000123,600000126,600000139,600000141,600000145,600000148,600000152,600000159,600000188,600000244,600000253,660000246,680000200,700000136';

runStats_pkg.rs_start;

For i in 1 .. 2000 Loop
ABELISTING.sp_sql_query.list_df(c_sp,l_string );
Loop
fetch c_sp into l_i;
exit when c_sp%notfound;
End loop;
End Loop;

runStats_pkg.rs_middle;

For i in 1 .. 2000 Loop
For c in(select column_value listingsid from table(cast(abelisting.sp_sql_query.list_df_tabf(l_string) as nt_type)))
Loop
Null;
End loop;
End Loop;

runStats_pkg.rs_stop;

End;
/


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