Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    Ontario, Canada
    Posts
    2

    Unanswered: Refcursors and incorrect %NOTFOUND usage?

    First of all I want to say that dBforums rocks and it has been a wealth of information for me. Every time I query Google for db-related stuff, I almost always get results from dbForums. Keep up the awesome work!

    I am very, very new to Oracle. How new? What time is it now?

    I have been tasked to write a stored procedure (in a package, of course) that queries a table for the existence of a record in a table. I require two returns: a flag indicating yes it was found our no it was not found; and, if it was found, then return a refcursor of the record.

    I know what you're thinking punk, why not just return the refcursor and let the caller check empty cursor (or EOF/BOF). That makes sense to me, but this is how I was asked to code it, alas.

    Anyways, here is the procedure:

    Code:
    PROCEDURE FindCustomer
    (
      vr_cust_id IN VARCHAR2,
      vr_retval OUT VARCHAR2,
      io_cursor IN OUT gResultSet
    )
    IS
    BEGIN
    
      OPEN io_cursor FOR
      SELECT *
      FROM   customers
      WHERE  customer_id = vr_cust_id;
    
      IF io_cursor%NOTFOUND THEN
        vr_retval := 'N';
      ELSE
        vr_retval := 'Y';
      END IF;
    
      EXCEPTION
        WHEN OTHERS THEN
          vr_retval := 'N';	
    
    END FindCustomer;
    It compiles and executes okay, i'm just not getting the results I expected. When I enter a customer_id that I know is invalid, the retval is still coming back as 'Y'. I don't think I'm using the %NOTFOUND cursor attribute correctly.

    Here is the test output from SQL*Plus:
    Code:
    SQL> variable rc refcursor;
    SQL> variable retval varchar2;
    SQL> exec SCG.FindCustomer('bilbobaggins', :retval, :rc);
    
    PL/SQL procedure successfully completed.
    
    SQL> print retval;
    
    RETVAL
    -------------
    Y
    
    SQL> print rc;
    
    no rows selected
    
    SQL> spool off
    Any suggestions?

    Regards,
    codeMnky

  2. #2
    Join Date
    Mar 2004
    Posts
    1
    some sample code:

    Code:
    CREATE FUNCTION f(cur SYS_REFCURSOR, mgr_hiredate DATE)
      RETURN NUMBER IS
        emp_hiredate DATE;
        before number :=0;
       after number:=0;
    begin
      loop
         fetch cur into emp_hiredate;
         exit when cur%NOTFOUND;
         if emp_hiredate > mgr_hiredate then
            after:=after+1;
         else
            before:=before+1;
         end if;
      end loop;
      close cur;
      if before > after then
         return 1;
      else
         return 0;
      end if;
    end;
    what you have missed is:


    Code:
     OPEN io_cursor FOR
      SELECT *
      FROM   customers
      WHERE  customer_id = vr_cust_id;
    
    
      fetch io_cursor into  MyVarName1,MyVarName2.....;
    
     IF io_cursor%NOTFOUND THEN

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •