How to check if Cursor Expression(select cursor) is returning null?

Question

I've a select cursor expression with multiple inner select cursors. I wanted to check in the outer most select cursor if the inner cursor is returning any value or not. eg.,

SELECT
CURSOR
  (SELECT
   CURSOR
     (SELECT c.cat_name category ,
      CURSOR
        (SELECT
         CURSOR
           (SELECT grp.grpng
            GROUPING ,
                             grp.action_group action_group ,
            CURSOR
              (SELECT
               CURSOR
                 (SELECT header_id ,
                         order_no ,
                         cust_name ,
                         cust_phone ,
                         org ,
                         status `enter code here`,
                         reason
                  FROM headers h
                  WHERE h.header_id = da.header_id ) "G_HEADER"
               FROM dual) "LIST_G_HEADER"
            FROM action_group grp
            WHERE grp.grpng = da.grpng
              AND grp.action_grp = da.action_grp ) "G_ACTION"
         FROM action_rep da
         WHERE da.org = 'test'
           AND da.cat_id = c.cat_id
         ORDER BY category,
         GROUPING,
                  reason ) "LIST_G_ACTION_GROUP"
      FROM dual ) "G_CATEGORY"
   FROM dual ) "LIST_G_CATEGORY" ,
       c.category_name
FROM cat c
ORDER BY c.cat_name

I need to put a check to see if the result of "LIST_G_CATEGORY" cursor is null or not and if it is null then do not display that row. P.S: please ignore the column name mismatches as the query is made up in rush to post here.

Version: Oracle 11g R2

You "made up in rush" but this represents an actual piece of business logic? What is the reason for this baroque nesting of scalar cursors? — APC, Aug 31 '16 at 05:34

score 0 · Accepted Answer · edited May 23 '17 at 10:33

In order to check whether the cursor is empty or not, one should fetch it first.

This means that you could crate a function (probably using SYS_REFCURSOR) that will return 1 or 0 accordingly, when the cursor is empty or not - but you should be aware of the rowtype that the cursor returns, in order to do that.

Check this references

oracle cursor expression,

How to check if Cursor Expression(select cursor) is returning null?

1 Answers1