how to deal with NO DATA FOUND using SYS_REFCURSOR

Question

how can i do if my select dont come with result using SYS_REFCURSOR ?

what i have try so far is using NO_DATA_FOUND, but its not working, my STATUS keep returning me as = 1

code:

...   
MYVARIABLE IN OUT SYS_REFCURSOR
...

OPEN MYVARIABLE FOR
     SELECT NAME FROM TABLE WHERE COD = 1; 
     STATUS := 1;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
      STATUS := 0;

any ideias ?

thanks!

---

the real sql is something like this:

Edit

V_SQL := 'SELECT SUM(T1.VLRLIQ) VALOR,T1.CODCLI,T1.NOMCLI
     ,(SELECT METCLI FROM WEB_CRM_CLIVEN T2
     WHERE T2.CODCLI = T1.CODCLI AND T2.CODVEN = '|| P_CODVEN ||'
      AND T2.MES = '|| V_MES ||' AND T2.ANO = '|| V_ANO ||') META
      FROM SAPIENS.USU_VRESNFV T1,SAPIENS.E085CLI T2
    WHERE T1.CODVEN = '|| P_CODVEN ||'
     AND TO_CHAR(T1.DATEMI,''YYYY'') = '|| V_ANO ||'
     AND TO_CHAR(T1.DATEMI,''MM'') = '|| V_MES ||'
     AND T1.VENFAT = ''S''
     '|| V_CGCCPF ||'
     '|| V_NOMCLI ||'
     AND T2.CODCLI = T1.CODCLI
     AND T1.CODEMP = 1
     GROUP BY T1.CODCLI,T1.NOMCLI
     UNION
   SELECT SUM(''0'') VALOR,CODCLI,NOMCLI,(SELECT METCLI FROM WEB_CRM_CLIVEN T3
      WHERE T3.CODCLI = T2.CODCLI AND T3.CODVEN = '|| P_CODVEN ||'
      AND T3.MES = '|| V_MES ||' AND T3.ANO = '|| V_ANO ||') META
      FROM SAPIENS.E085CLI T2
    WHERE
     CODCLI IN (SELECT CODCLI FROM WEB_CRM_VEN_CARTEIRA
      WHERE CODVEN = '|| P_CODVEN ||' AND MES = '|| V_MES ||' AND ANO = '|| V_ANO ||')
      '|| V_CGCCPF ||'
      '|| V_NOMCLI ||'
     GROUP BY CODCLI,NOMCLI
     ORDER BY VALOR DESC';

     STATUS := 1;

     OPEN RESULTADO FOR V_SQL;

Answer 1

In your code, you are just opening the cursor but not fetching from it. When you open a cursor, PL/SQL executes the query for that cursor. It also identifies the rows that meet the criteria in the WHERE clause and join conditions. The OPEN does not actually retrieve any of these rows; that action is performed by the FETCH statement. You would then use cursor attributes to check if the result set is empty; if it is, then the following cursor attributes would have these values: %FOUND = FALSE, %NOTFOUND = TRUE, and %ROWCOUNT = 0.

Here is an example:

     SQL> DECLARE
       2     l_cur   SYS_REFCURSOR;
       3     l_col   VARCHAR2 (10);
       4  BEGIN
       5     OPEN l_cur FOR
       6        SELECT 'Hi there' col
       7          FROM DUAL
       8         WHERE 1 = 0;
       9
      10     DBMS_OUTPUT.put_line ('Opened cursor');
      11
      12     FETCH l_cur INTO l_col;
      13
      14     DBMS_OUTPUT.put_line ('Fetched from cursor');
      15
      16     IF l_cur%NOTFOUND
      17     THEN
      18        DBMS_OUTPUT.put_line ('Oops! No data found. Raising exception...');
      19        RAISE NO_DATA_FOUND;
      20     END IF;
      21
      22     CLOSE l_cur;
      23  EXCEPTION
      24     WHEN NO_DATA_FOUND
      25     THEN
      26        DBMS_OUTPUT.put_line ('Exception raised.');
      27  END;
      28  /
     Opened cursor
     Fetched from cursor
     Oops! No data found. Raising exception...
     Exception raised.

     PL/SQL procedure successfully completed.

Answer 2

To slightly modify @Eddie Awad's answer, the usual code pattern I use for fetching from a cursor variable is as follows:

DECLARE 
  l_cur SYS_REFCURSOR; 
  l_col VARCHAR2 (10); 
BEGIN 
  OPEN l_cur FOR 
    SELECT 'Hi there' col 
      FROM DUAL 
      WHERE 1 = 0; 

  DBMS_OUTPUT.PUT_LINE('Opened cursor'); 

  <<cursor_loop>>
  LOOP
    FETCH l_cur INTO l_col; 

    DBMS_OUTPUT.PUT_LINE('Fetched from cursor'); 

    EXIT cursor_loop WHEN l_cur%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE('Process data fetched from cursor');    
  END LOOP;  -- cursor_loop

  CLOSE l_cur; 
  DBMS_OUTPUT.PUT_LINE('Closed cursor');
END; 

The idea is to open the cursor variable (or get it back from a procedure), then loop until all rows have been fetched from the cursor.

Share and enjoy.