how to check if SYS_REFCURSOR is empty-CodePudding

I have a general SYS_REFCURSOR that I want to check if it is empty or not.

The code is like this:

declare
    v_cursor SYS_REFCURSOR;
begin
   OPEN v_cursor FOR <any select statement>.
   check if v_cursor is empty.
end;

Can someone tell me how to check if the weak cursor is empty, please? I have to mention that the base SELECT statement can be anything from any table. The column numbers or type it is known only at runtime.

Thank you,

CodePudding user response：

You can't see if a ref cursor contains data without fetching, and consuming at least one row from it.

If you really need to determine this at the point the cursor is opened, without knowing the structure at that point, you could execute a modified query that just counts the rows returned by your real query - possibly limited to a single row if that helps performance - either as a simple execute immediate ... into ... or with a separate open/fetch/close for consistency; something like:

declare
  v_cursor SYS_REFCURSOR;
  v_query VARCHAR2(4000);
  v_count PLS_INTEGER;
begin
  v_query := <any select statement>;

  -- see if the query finds any data
  OPEN v_cursor FOR 'select count(*) from (' || v_query || ')'; -- could limit rows
  FETCH v_cursor INTO v_count;
  CLOSE v_cursor;
  if v_count = 0 then
    dbms_output.put_line('No data');
    return;
  end if;
  
  dbms_output.put_line('Found data, opening cursor for real');
  OPEN v_cursor FOR v_query;
  -- loop over results, return to caller, etc.
end;
/

db<>fiddle

CodePudding user response：

Try to fetch a row and then use v_cursor%NOTFOUND to determine of the cursor is empty:

DECLARE
    v_cursor SYS_REFCURSOR;
    v_value  DUAL.DUMMY%TYPE;
BEGIN
   OPEN v_cursor FOR SELECT DUMMY FROM DUAL WHERE 1 = 0;
   FETCH v_cursor INTO v_value;
   IF v_cursor%NOTFOUND THEN
     DBMS_OUTPUT.PUT_LINE('Cursor empty');
   ELSE
     DBMS_OUTPUT.PUT_LINE('Cursor not empty');
   END IF;
END;
/

DECLARE
  CURSOR v_cursor IS
    SELECT DUMMY FROM DUAL WHERE 1 = 0;
  v_row v_cursor%ROWTYPE;
BEGIN
  OPEN v_cursor;
  FETCH v_cursor INTO v_row;
  IF v_cursor%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('Cursor empty');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Cursor not empty');
  END IF;
END;
/

Both output:

Cursor empty

db<>fiddle here