Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, angira .

Asked: May 02, 2000 - 1:21 pm UTC

Answered by: Tom Kyte - Last updated: January 30, 2013 - 2:19 pm UTC

Category: - Version:

Viewed 50K+ times! This question is

Whilst you are here, check out some content from the AskTom team: Shameless, I know. But it feels so good...

You Asked

WHY CANT I USE A CURSOR ATTRIBUTE
WITH REFCURSOR DATATYPE WHEN MY QUERY IS RETURNING NO ROWS SELECTED .
I WANT TO DO A CHECK ON %ROWCOUNT = 0
AND TELL MY REF CURSOR TO POINT TO
ANOTHER QUERY WORK AREA
I AM USING ORACLE 7



and we said...


You use %rowcount only with implicit cursors that do insert/update/delete.

You use %notfound with SELECTs to see if they return data or not. Here is an example:

ops$tkyte@8i> declare
2 type refCur is ref cursor;
3
4 l_cursor refCur;
5 l_rec dual%rowtype;
6 begin
7 open l_cursor for select * from dual where 1 = 0;
8
9 fetch l_cursor into l_rec;
10
11 if ( l_cursor%notfound )
12 then
13 dbms_output.put_line( 'We go NO Data' );
14 end if;
15 end;
16 /
We go NO Data

PL/SQL procedure successfully completed.


The "if ( l_cursor%notfound )" is what you use to test for no more data with a cursor of any type.



and you rated our response

  (10 ratings)

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

Reviews

not fatching ??

November 12, 2002 - 4:11 pm UTC

Reviewer: A reader

Hi tom,

I am returning the refcursor,
and returning thro' the procedure parameter
so I don't want to fatch inthe procedure,
-- I just want to dynamically open the cursor
=====> check if there is atleast 1 record in the cursor
--- return as refcursor

set serveroutput on size 100000

declare
type refCur is ref cursor;
l_cursor refCur;
l_rec dual%rowtype;
begin
open l_cursor for select * from dual where 1 = 0;
-- fetch l_cursor into l_rec;
if ( l_cursor%notfound )
then
dbms_output.put_line( 'We go NO Data' );
end if;
end;
/

PL/SQL procedure successfully completed.

-- so how can I find # of rows in the cursor ? with out fatching ?

Thanks,


Tom Kyte

Followup  

November 13, 2002 - 12:41 pm UTC

You cannot -- not possible.

It is like asking "how can I find out if the box is empty without opening it?" You have to open it to find out. You have to fetch to find out.

The CLIENT is the one that should deal with this particular detail OR you want to have a function that returns a value, not a cursor.

ref cursor the # of column is known at run time ??

January 13, 2003 - 2:04 pm UTC

Reviewer: A reader

Hi tom,

I have this pl/sql block..
/********************************************/
declare
vrc_entity p_entity.rc_entity;
rec_entiy entity%ROWTYPE;
msg varchar2(2000);
errcd varchar2(2000);
begin

p_entity.GET_entity(null,null,null,vrc_entity, msg,errcd);

IF msg = 'DB-SUCCESS' THEN
LOOP
FETCH vrc_entity INTO rec_entity; <<-----
EXIT WHEN vrc_entity%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(' entity Code: ' || rec_entity.entity_code || 'entity Name: ' || rec_entity.entity_name);
END LOOP;
END IF;
end;
/

/**********************************************/

The above proceudre does not work
*
ERROR at line 171:
ORA-06550: line 171, column 23:
PLS-00597: expression 'REC_ENTITY' in the INTO list is of wrong type
ORA-06550: line 171, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 173, column 51:
PLS-00487: Invalid reference to variable 'REC_ENTITY'
ORA-06550: line 173, column 3:
PL/SQL: Statement ignored

because the column returned and columns in the fatch into stmt are not same,

now,
procedure p_entity.GET_entity returns a refcursor
and it is dynamic so I don't know what columns will be returned untill run time

how can I declare a %ROWTYPE of this ref-cursor so that
it will be able to fatch,hold and display the information ?



Tom Kyte

Followup  

January 13, 2003 - 2:33 pm UTC

If the output varies from run to run (eg: different number and types of columns) you cannot use a ref cursor in plsql. It has to have a well known structure at compile time


You can (and must if you have a need to do this) use DBMS_SQL which gives you procedural methods to access each column, describe the query result set and so on.

over head ??

January 13, 2003 - 4:46 pm UTC

Reviewer: A reader

Thanks Tom for your reply....

but it will be a big procedure. I will have to
parse each column and then move/copy the values.

but can't we do it dynamically as soon as the
dynamic query is build, the recored type is known !!

or even latter, as soon as I open the refcursor
can't we bind the recordtype using %rowtype so some
psudo variable because I need to know which columns
are participating when I fatch the record not before then


Thanks,

Tom Kyte

Followup  

January 13, 2003 - 7:06 pm UTC

the record is not known at COMPILE TIME -- hence plsql cannot do it.

DBMS_SQL is the only way. I don't know what you mean by "a big procedure". See

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

that procedure fits on a cocktail napkin and dumps most queries (doesn't do blobs and clobs for example) do a flat file. Its not big.

It is only big if you make it big.

Bottom line though -- you WILL NOT be using a ref cursor to do this as it is quite impossible. Period. DBMS_SQL is the only way to handle a result set with an unknown (until runtime unknown) number and type of columns in PLSQL.

(overhead = something avoidable that you do not technically need. this therefore is by defintion NOT overhead as there is quite simply no other way to do it. it need not be big as demonstrated -- in fact - if you play with a PLSQL index by table -- you might find it can be quite small -- think about it, fetch the i'th column into the i'th array element in a loop -- three lines of code to "fetch a row" regardless of the number of columns in the query. Code is only BIG when you write alot of it.)

Thanks,

January 14, 2003 - 5:38 pm UTC

Reviewer: A reader

Thanks tom :)

Excellent

December 24, 2003 - 1:13 am UTC

Reviewer: Sam from CT,USA

Hello sir,
I need a query that returns the output in the following
format
table_name row_count
Emp 14
Dept 4
for this purpose I analyzed the schema and issued a statement like
sql> select table_name,num_rows from user_tables where
table_name in (select table_name from user_tab_columns);
what I would like to ask is:
Is there any other trick to do the same?
TIA



Tom Kyte

Followup  

December 24, 2003 - 10:03 am UTC

well, you might just

select table_name, num_rows from user_tables;


the subquery is sort of redundant there.


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



September 02, 2008 - 6:41 am UTC

Reviewer: Girish from India

Hi Tom,

This question is related to rowcount in a refcursor.Following is my code

declare
type t is refcursor;
vc t;
v_sql varchar2(1000);
v_cnt number;
begin

-- I am just returning results from below refcursor whcih are
-- used by Front End Java application to display the results
v_sql:='select a.col1,b,col3,c.col6
from t1 a,t2 b,t3 copen vc for
where
a.col2=b.col1
b.col3=c.col3' ;

open vc for v_sql;

--to get total number of records in refcursor I am executing below statement

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ('||v_sql||')' INTO v_cnt;

--This will re-execute above query second time and if data is more it willl be performance issue

end;

Is there a better way to do for improving performance ?

Thanks,
Girish
Tom Kyte

Followup  

September 02, 2008 - 11:52 am UTC

yeah - SKIP THE COUNTING.

you do understand already (clearly you do) that in order to count the rows, you will have to get the LAST ROW - you have to answer the entire query before getting a single record back...

And do you understand that in general if you

a) count rows
b) then run a query to get rows

by the time B) happens, the answer from A) is OUT OF DATE.


I hate this concept - the concept of counting rows. It is such an utter and complete waste of resources. It does *nothing*

All someone needs to know is that they are "looking at 1-10 of more than 10 rows". Hit next and tell them "you are looking at 11-20 of more than 20" and so on. Until you run out of rows to show them.


Look at google - it totally guesses as to the row count and if you try to go to page 100, they laugh at you and say "no, that is just getting silly".

Human beings cannot deal with a result set numbering in the many hundreds or thousands - they just need to know "you have 1-10 here, and there are more"

period.


So, stop counting and start building infinitely more resource friendly applications.

November 05, 2012 - 5:43 am UTC

Reviewer: Jitendra Jalota from India

Hi Tom,

for the first question in this page as you said that for taking the count from ref cursor that if it is having any data or not we will have to open it and check with the help of found% or notfound% attributes.

I am doing the same.
opening the ref cursor and if the data is found at the first fetch i am doing exit and the same set of data of ref cursor has been passed to Java the Front End.

but while fetching it from FE it is missing the first row which i fetched in Backend for checking found%.

could you please suggest on this.

Thanks.

Tom Kyte

Followup  

November 05, 2012 - 9:50 am UTC

of course it is "missing" the first row - you fetched it already, it is gone.


why cannot you just return the cursor to the client, if there is no data - they get told that on the first fetch. this is pretty "standard sql result set processing" - it is how it is done billions of times in billions of pieces of code. why cannot the client deal with a result set that is empty???

You aren't going to know if a result set is empty until you fetch from it - and once you do - that is it, game over - that row is fetched.

November 08, 2012 - 5:12 am UTC

Reviewer: Jitendra Jalota from India

Hi Tom,

Acutally idea behind fetching the row from ref cursor is that if there is not data in it, we will have to return the default set of data from a default query.

ok will look to implement the logic through FE.

Thanks for your suggestions.

Tom Kyte

Followup  

November 08, 2012 - 8:33 am UTC

Ok, you can do this:


with data as ( select 3 oc, ..... your query goes here )
select * from data
union all
select 1 oc, null, null, null, .... from dual where exists (select * from data)
union all
select 2 oc, null, null, null, .... from dual where not exists (select * from data
order by oc, .....



the first row will be either 1 or 2 - 1 means 'data is there', 2 means 'no data coming'



January 25, 2013 - 9:26 am UTC

Reviewer: A reader

I always find interesting, informative things to read here. Very grateful to have this resource.

If we can't count rows on a cursor, and have a requirement for a stored procedure of "If the query would return more than X results, raise an error. Otherwise, just return the cursor" how would we do that? We could limit the query to only fetch up to X+1 results, but without rifling through the results (and consuming them) to see if that X+1st result exists, how will we know to raise the error?
Tom Kyte

Followup  

January 30, 2013 - 2:19 pm UTC

it is time to rethink the requirement. Requirements need to have some basis in reality - what the products you are using actually do.

I could return a query that would fail with an error on the first fetch if there were more than X rows in it. would that suffice?


select q.*, case when count(*) over () > X then 1/0 end
from (your_query) q
where rownum <= (X+1)


but if the routine call must fail, you'll need to

a) count rows
b) fail or return refcursor


using a read only transaction or a serializable transaction or flashback query to make (A) and (B) consistent with respect to each other.

incase if we have to run the stored Proc and get the output in refcursor

February 03, 2017 - 1:19 pm UTC

Reviewer: Sid from Zurich, Switzerland

In case if we have to run the stored Proc with certain input parameters and get the output in ref cursor. Here we want to know that how many rows got in output.

set serveroutput on
DECLARE
P_RS SYS_REFCURSOR;
L_CURS INTEGER;
L_ROWS NUMBER :=0;
P_input1 varchar2(10);
p_input2 varchar2(10);
BEGIN
<Stored_Proc > (<P_Return_Refcursor> , P_input1, p_input2 );

L_CURS:=dbms_sql.open_cursor;
L_CURS := DBMS_SQL.TO_CURSOR_NUMBER(P_Return_Refcursor);
LOOP
EXIT
WHEN dbms_sql.fetch_rows(L_CURS)=0;
L_ROWS:=L_ROWS+1;
END LOOP;
Dbms_sql.close_cursor(L_CURS);
dbms_output.put_line(L_ROWS);

END;



More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here