hplsql/src/test/queries/db/sys_refcursor.sql

CREATE PROCEDURE spResultSet1 (cur OUT SYS_REFCURSOR)
BEGIN
  OPEN cur FOR SELECT 'A' FROM src LIMIT 1;
END; 

DECLARE 
  c1 SYS_REFCURSOR;
  v1 VARCHAR(30);
BEGIN
  IF c1%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('%ISOPEN failed');
  ELSE 
    DBMS_OUTPUT.PUT_LINE('%ISOPEN correct');
  END IF; 
  
  CALL spResultSet1(c1);
  
  IF c1%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('%ISOPEN correct');
  ELSE 
    DBMS_OUTPUT.PUT_LINE('%ISOPEN failed');
  END IF; 
  
  IF c1%FOUND IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('%FOUND correct');
  ELSE
    DBMS_OUTPUT.PUT_LINE('%FOUND failed');
  END IF;

  IF c1%NOTFOUND IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('%NOTFOUND correct');
  ELSE
    DBMS_OUTPUT.PUT_LINE('%NOTFOUND failed');
  END IF;
  
  FETCH c1 INTO v1;
  
  IF c1%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('%FOUND correct');
  ELSE
    DBMS_OUTPUT.PUT_LINE('%FOUND failed');
  END IF;

  IF c1%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('%NOTFOUND failed');
  ELSE
    DBMS_OUTPUT.PUT_LINE('%NOTFOUND correct');
  END IF;  
  
  FETCH c1 INTO v1;
  
  IF c1%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('%FOUND failed');
  ELSE
    DBMS_OUTPUT.PUT_LINE('%FOUND correct');
  END IF;

  IF c1%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('%NOTFOUND correct');
  ELSE
    DBMS_OUTPUT.PUT_LINE('%NOTFOUND failed');
  END IF;  

  CLOSE c1; 
END;