Re: Validating REf Cursor

From: Nigel Thomas <nigel_at_preferisco.com>
Date: Sun, 23 Apr 2006 09:19:29 -0700 (PDT)
Message-ID: <20060423161929.9899.qmail@web54706.mail.yahoo.com>

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:19:29 CDT