0

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.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Steve Lennon
  • 69
  • 1
  • 12
  • Why would you want to do this? You can certainly open and return a cursor with a different structure that will return 0 rows when the caller fetches from it. But doing so is going to significantly increase the complexity of the code that calls this procedure. Perhaps you really just want to have a `WHERE` clause on your `SELECT` that causes it to return no rows in some cases? – Justin Cave Jan 22 '21 at 11:14
  • What is boolean statement that you substituted `TRUE` for in your code? Can you just move that into a `WHERE` clause in your `SELECT` and drop the `IF/ELSE` entirely? – Del Jan 25 '21 at 15:52

2 Answers2

0

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.)

Popeye
  • 35,427
  • 4
  • 10
  • 31
  • Is it the right thing to do ? Do we have any other option. – Steve Lennon Jan 22 '21 at 09:47
  • right thing is to handle the logic in the application. but for that you will need one more output parameter such as flag which indicates if application should access the cursor or not (Mostly using count query) – Popeye Jan 22 '21 at 09:48
0

You can simply assign NULL to the refcursor like this:

PROCEDURE test(  id_number IN VARCHAR2,
                  resultIN OUT SYS_REFCURSOR) AS
BEGIN
  if false then
    OPEN resultIN FOR
      SELECT dummy 
      from dual;
  ELSE
     resultIN := null;
  END IF;
END;
blitzkopf
  • 21
  • 3