Home » SQL & PL/SQL » SQL & PL/SQL » Cursor operation, checking return result
Cursor operation, checking return result [message #265469] Thu, 06 September 2007 07:03 Go to next message
nashrul
Messages: 7
Registered: August 2007
Location: Indonesia
Junior Member
I have this code snippet below,

CURSOR MCUR_CHECKEXIST(SC IN VARCHAR2, DT IN DATE) 
    IS 
    SELECT 
        BASIC_AMOUNT 
    FROM 
        AL_DS_BASIC 
    WHERE 
        SALES_CODE= SC 
        AND 
        to_char(REPORT_PERIOD,'MMYYYY')= to_char(DT,'MMYYYY');

OPEN MCUR_CHECKEXIST(PI_SALES_CODE, PI_DATE);
IF (MCUR_CHECKEXIST%NOTFOUND) THEN
  -- Do something here
END IF;


Can I do the checking (MCUR_CHECKEXIST%NOTFOUND) without doing the fetching operation (the checking statements above still result to boolean TRUE or FALSE) ??

Thanks in advance
Re: Cursor operation, checking return result [message #265473 is a reply to message #265469] Thu, 06 September 2007 07:11 Go to previous messageGo to next message
Gary Revell
Messages: 6
Registered: August 2007
Junior Member
Hi,

I believe that it is only when the fetch is executed that the %NOTFOUND is then set.

Opening the cursor just parses the SQL and binds the variables.

What are you trying to achieve here?

Gary
icon5.gif  Re: Cursor operation, checking return result [message #265475 is a reply to message #265469] Thu, 06 September 2007 07:14 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

may be something like this
declare
cursor c1(v_deptno number) is
select ename,sal from emp where deptno=v_deptno;
begin
open c1(2);
if c1%notfound then
dbms_output.put_line('found');
else
dbms_output.put_line('not found');
end if;
end;


but what are you trying to achive here


regards,

[Updated on: Thu, 06 September 2007 07:15]

Report message to a moderator

Re: Cursor operation, checking return result [message #265483 is a reply to message #265469] Thu, 06 September 2007 07:23 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You have to fetch to get %found/%notfound attribute set:
SQL> declare cursor c is select * from t;
  2  begin
  3    open c;
  4    if c%notfound then dbms_output.put_line('not found');
  5    elsif c%found then dbms_output.put_line('found');
  6    else dbms_output.put_line('unknown'); end if;
  7  end;
  8  /
unknown

PL/SQL procedure successfully completed.

SQL> select count(*) from t;
  COUNT(*)
----------
         0

1 row selected.

Regards
Michel
Previous Topic: Find time difference
Next Topic: Problem regarding date function
Goto Forum:
  


Current Time: Fri Feb 07 15:50:55 CST 2025