How to handle NO_DATA_FOUND in sys-refcursor using bind variables

How to handle the case when select statement don't return any record in SYS_REFCURSOR ? I am using dynamic sql generation process using bind variables.

 create or replace
        procedure test_dynamic_sql
          (
            last_name varchar2,
            rc out sys_refcursor,
            jobid varchar2,
            sdate date,
            edate date,
            status out varchar2,
            message out varchar2 )
        is
          q long;
          lname varchar2(240);

        begin
           q := 'select employee_id    
        from employees e           
        where 1=1';
            if last_name is not null then
              q := q || 'and (e.LAST_NAME = :LAST_NAME)';
            else
              q := q || 'and (1=1 or :LAST_NAME is null)';
            end if;
            if jobid is not null then
              q := q || 'and (e.JOB_ID = :JOBID)';
            else
              q := q || 'and (1=1 or :JOBID is null)';
            end if;
            if sdate is not null then
              q := q || 'and (e.hire_date >= :sdate)';
            else
              q := q || 'and (1=1 or :sdate is null)';
            end if;
            if edate is not null then
              q := q || 'and (e.hire_date <= :edate)';
            else
              q := q || 'and (1=1 or :edate is null)';
            end if;

            open rc for q using last_name, jobid, sdate, edate;
          /*     
          IF rc%NOTFOUND THEN
            STATUS  := 'NR';
            MESSAGE := 'Not Found';
          ELSE
            STATUS  := 'S';
            MESSAGE := 'Found';
          END IF;
          */ 

        exception
        when others then
          STATUS  :='E';
          message := sqlcode||'-->'||sqlerrm;
        end;

I have tried %NOTFOUND and %FOUND attributes but its not working. I have also tried NO_DATA_FOUND exception but its also not working.

I need to return status as 'S', 'E', 'NR'

S --> SUCCESS (when records found)
E --> ERROR (when any error occured)
NR--> NO RECORDS (when 0 records)

Thanks!

Solution

This answer is to address the issues you are having while working with Ref Cursors as Out Parameters. The below code calls your test_dynamic_sql() Procedure. Within this Proc we OPEN the cursor, FETCH the data it is pointing to and we DO NOT CLOSE the cursor as we are immediately using that Cursor again outside of the test_dynamic_sql() Procedure. One thing to NOTE - When FETCH is used that Cursor will no longer provide you with the data and must be opened again. Since your Cursor is utilizing Dynamic SQL we must Declare our Dynamic 'Query' in the same place we are declaring the rest of our Global Variables.

"Cursors are NOT designed to be re-used: you read them once, keep moving forward and as you're doing so you're discarding any previously scanned rows." This fact was stolen from this SO Post: Oracle. Reuse cursor as parameter in two procedures.

Outside of this procedure we first must check if the Cursor was successfully initialized by using an IF statement to check if the Cursor exists: IF (g_rc IS NOT NULL) THEN.

Full Code Sample below:

DECLARE

  /* g for Global */
  g_status          VARCHAR2(5);
  g_message         VARCHAR2(100);

  g_rc              SYS_REFCURSOR;

  /* Store Dynamic SQL Query */
  g_SQL             VARCHAR2(200);

  /* Bind Variables */
  g_jobid           NUMBER;
  g_last_name       VARCHAR2(240);

  /* Declare Global Record used to FETCH data into */
  g_rec_employee      employees%ROWTYPE;

  PROCEDURE test_dynamic_sql(pv_last_name VARCHAR2,
                              p_rc OUT SYS_REFCURSOR,
                              pv_jobid VARCHAR2,
                              pv_status OUT VARCHAR2,
                              pv_message OUT VARCHAR2,
                              pv_q OUT VARCHAR2)
  AS

    /* Declare Record used to FETCH data into */
    rec_employee    employees%ROWTYPE;  

    /* Bind Variables */
    jobid           NUMBER        :=  to_number(pv_jobid);
    last_name       VARCHAR2(240) :=  pv_last_name;  

  BEGIN

    /* Reset/Initialize Cursor Output Variable */
    p_rc            :=  NULL;

    /* Dynamic SQL statement with placeholder: */
    pv_q := 'SELECT * FROM employees WHERE 1=1';

      IF last_name IS NOT NULL
        THEN pv_q := pv_q || ' AND (lastname = :LAST_NAME)';
        ELSE pv_q := pv_q || ' AND (1=1 or :LAST_NAME is null)';
      END IF;

      IF jobid IS NOT NULL
        THEN pv_q   := pv_q || ' AND (ID = :JOBID)';
        ELSE pv_q   := pv_q || ' AND (1=1 or :JOBID is null)';
      END IF;

    /* Open cursor & specify bind argument in USING clause: */
    OPEN p_rc FOR pv_q USING last_name, jobid;    

    LOOP
      /* In order to work with any Data that a Cursor 'points' to we must FETCH the data.  This allows us to use %ROWCOUNT and %NOTFOUND */
      FETCH p_rc INTO rec_employee;     
      EXIT WHEN p_rc%NOTFOUND;    
    END LOOP;

    IF p_rc%ROWCOUNT = 0 THEN
      pv_status  :=  'NR';
      pv_message :=  'Not Found';
      --EXIT;
    ELSIF p_rc%ROWCOUNT = 1 THEN
      pv_status  :=  'S';
      pv_message :=  'Found';
      --EXIT;
    ELSE
      pv_status  :=  'MU';
      pv_message :=  'Multiple Records';

    END IF;

    --dbms_output.put_line('Final Count: ' || p_rc%ROWCOUNT);

    /* Close Cursor - We don't close the Cursor here as we want to use this cursor as an OUT Parameter outside of this Proc */
    CLOSE p_rc;

  EXCEPTION
          WHEN OTHERS THEN
            pv_status  :='E';
            pv_message := sqlcode||'-->'||sqlerrm;
            dbms_output.put_line('STATUS: ' || pv_status);
            dbms_output.put_line('MESSAGE: ' || pv_message);
            CLOSE p_rc;

  END test_dynamic_sql;

BEGIN

  g_jobid     :=    null;
  g_last_name :=    'Loch';

  test_dynamic_sql(pv_last_name => g_last_name,
                    p_rc        => g_rc,      /* Out Parameter */
                    pv_jobid    => g_jobid,
                    pv_status   => g_status,  /* Out Parameter */
                    pv_message  => g_message, /* Out Parameter */
                    pv_q        => g_SQL      /* Out Parameter */
                  );

  /* Output OUT Variables aka Provide Output to User */
  dbms_output.put_line('STATUS: '  || g_status);
  dbms_output.put_line('MESSAGE: ' || g_message);

  IF (g_rc IS NOT NULL) THEN
    dbms_output.put_line('We have something here. Fetching Data Now:');

    OPEN g_rc FOR g_sql USING g_last_name, g_jobid;

    LOOP    
      FETCH g_rc INTO g_rec_employee;
      EXIT WHEN g_rc%NOTFOUND; 
      /* Print the Job ID just to show it is working */
      dbms_output.put_line('Job_ID: ' || g_rec_employee.id || ' is the id');      
    END LOOP;

    dbms_output.put_line('Total of: '|| g_rc%ROWCOUNT || ' records Fetched.');

    CLOSE g_rc;
  ELSE
    dbms_output.put_line('null');
  END IF;



EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
    CLOSE g_rc;

END;

The above works with the same Employees table data as in my first Answer to this SO Question.