RE: Validating REf Cursor

From: Gabriel Aragon <gabriel.aragon_at_softtek.com>
Date: Sun, 23 Apr 2006 11:52:00 -0500
Message-ID: <03a301c666f6$3e4f2500$0d4210ac@STKAG5729>

Hi Nigel, tks for your answer,

the quotes are ended, it was a typo when writing the mail =)

unfortunately my ref cursor is using a very complex and extra long dynamic query that is the result of many conditions for every section (select, from, where, most of all on the select clause) so trying to build a fetch would be very complex since I would have to repeat all the conditions that made the final statement executed by the ref cursor (some kind of dynamic fetch), that is what I'm trying to avoid by checking if if the resulting cursor is empty or not, I think it would be something like:

if c is null then
..
else
..
end if;

obviously this won't work, any ideas?

-----Original Message-----
From: Nigel Thomas [mailto:nigel_at_preferisco.com] Sent: Domingo, 23 de Abril de 2006 11:19 a.m. To: gabriel.aragon_at_softtek.com
Cc: oracle-l_at_freelists.org
Subject: Re: Validating REf Cursor

Gabriel

You need to FETCH as well as OPEN
You need to end the quotes in the dbms_output('Print') / ('No Print')

Here's a working version, using DUAL and DUMMY instead:

declare
TYPE lista IS REF CURSOR;
c lista;
x varchar2(100);

y varchar2(100);                -- ADD THIS LINE

begin
  dbms_output.enable;
  x := 'select dummy from dual where dummy=''X'''; -- valid query, returns 1 row
  open c for x;

  fetch c into y;                -- ADD THIS LINE

  IF c%rowcount >0 then
    dbms_output.put_line('data'); -- ADD END QUOTE   ELSE
    dbms_output.put_line('NO data'); -- ADD END QUOTE   END IF; end;
/

Cheers Nigel

Original Message ---- From: Gabriel Aragon <gabriel.aragon_at_softtek.com> To: oracle-l_at_freelists.org Sent: Sunday, April 23, 2006 4:56:28 PM Subject: Validating REf Cursor

Hi list,

(Ora 9i-Win2k) I'm trying to validate a REF CURSOR to verify if it is empty or not. Basically the code is like this:

declare

TYPE lista IS REF CURSOR;
c lista;
x varchar2(100);

begin
x := 'select valuedata from t where x=1'; -- valid query, returns 1 row open c for x;

  IF c%rowcount >0 then
    dbms_output.put_line('data);
  ELSE
    dbms_output.put_line('NO data); -- does not detect rowcount and always print 'NO data'
  END IF; end;

I've changed c%rowcount for sql%rowcount with same results dont matter if the query returns rows or not, even using a NULL comparison (if c is null). What am I doing wrong? is there any other simpler way to do this?

TIA
Gabriel

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Apr 23 2006 - 11:52:00 CDT