Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

is it possible to define default value of a variable in sql scripts

Rajeshvig - OracleJun 5 2008 — edited Jun 6 2008
Hi,

I was thinking of a way to assign a default value to a variable without any prompt by the script as we do in shell script.

To make my query more clear, consider the example.

suppose we have a sql script like test.sql with contents
===========================================
define x=&1

declare
x1 number;
begine

x1:=nvl(&x,10);
dbms_output.put_line(x1);
end;
/
===========================================

even though I am using nvl it will prompt for value of 1 which needs user interference. for example if I run test.sql output is as below:
=============
SQL> @test
Enter value for 1:
10
=============

Is there any way to avoid sql from prompting for values of variables for which we want it to take a default value (if not specified as input argument)

Thanks


now if i run this as
sql>@test 10
it executes
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 4 2008
Added on Jun 5 2008
4 comments
1,648 views