oracle script defaulting a variable

How is s SQL script variable tested for not being defined and if not defined  set a default value.

A very simple example to describe the issue.  In the third input example I want the missing &3 to default to *. The real problem is much more complicated, this simply defineds the issue.

@test_script T_user kee *
@test_script T_user kee user_id
@test_script T_user kee

--------------------------------------------
-- test_script.sql
define col_name=&3
select &col_name from &1  where user_id like '%&2%';

----------------------------------------------

what I would like to have happen

if &3 is null then
     define col_name=*
else
   define col_name=&3
end if;

select &col_name from &1  where user_id like '%&2%';
fpkeeganAsked:
Who is Participating?
 
johnsoneSenior Oracle DBACommented:
Have a look at the ACCEPT command.  I don't think you can incorporate that into a passed parameter, but it allows a default value:

http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve005.htm#BACGJIHF

Another way could be to use a dummy value, like 'ALL' and then you could do something like this:

col col_name new_value col_name_new
select decode('&3', 'ALL', '*', '&3') col_name_new from dual;

This would set the value of the col_name parameter.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.