sql - How to return null in OUT SYS_REFCURSOR

Question

I have a procedure in the package which returns a SYS_REFCURSOR, i want to return null or empty based on a condition, not sure how can i do it.

  PROCEDURE test(  id_number IN VARCHAR2,
                   resultIN OUT SYS_REFCURSOR) AS
    BEGIN
    if true then
     OPEN resultIN FOR
       SELECT 
       fieldsValue 
      from TableName;
    ELSE
    Return empty resultIN ;
    END IF;
  END;

This is not working for me. How can I do the same.

question from:https://stackoverflow.com/questions/65842739/how-to-return-null-in-out-sys-refcursor

Answer 1

You can use fake query as follows:

OPEN resultIN FOR
select * from dual where 1=2; 

-- if you want one row also then use 

OPEN resultIN FOR
select case when 1=2 then 1 end as dummy_row from dual; 

It is important to use OPEN resultIN FOR otherwise application which is going to use it will directly throw closed cursor error. (Cursor is not opened.)