how to return some default value in oracle cursor when nothing is found

Question

I have following statement in one of the oracle SP:

OPEN CUR_NUM FOR 
   SELECT RTRIM(LTRIM(num)) as num 
   FROM USER WHERE USER_ID = v_user_id;

When the above does not return anything the SP result just has no column name in it. But I'd like the cursor to still have num column with some default value like NOTHING when no data is found.

Is this doable ?

Answer 1

If you really need to do that, you could use a UNION to select your dummy-row in case the user_id does not exist:

OPEN CUR_NUM FOR 
   SELECT RTRIM(LTRIM(num)) AS num 
   FROM user WHERE USER_ID = v_user_id
 UNION ALL
   SELECT 'NOTHING' AS num
   FROM dual
   WHERE NOT EXISTS (
     SELECT 1
     FROM user
     WHERE user_id = v_user_Id
   )

Answer 2

two choices i think:

add NVL() around the value if there is a row being returned but the column is null,

otherwise

add a MIN or MAX function if possible - this can force a row to be returned