oracle script defaulting a variable

Posted on 2012-09-10
Last Modified: 2012-09-15
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=*
   define col_name=&3
end if;

select &col_name from &1  where user_id like '%&2%';
Question by:fpkeegan
    1 Comment
    LVL 34

    Accepted Solution

    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:

    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.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Article by: Swadhin
    From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now