tindavid
asked on
How to check if a SQL*Plus variable has been defined or not
Let's say I have login in to Oracle's sqlplus.
SQL> define
.....
....
I could not find a variable call 'variablex'
Below is the suedo code of how to reset the variable 'variablex'
begin
if 'variablex' not defined then
variablex := 'xxxxxx'
else
leave it
end if;
end;
SQL > define
variablex = 'xxxxxx'
!!! Bear in mind 'variablex' is a dynamic value
SQL> define
.....
....
I could not find a variable call 'variablex'
Below is the suedo code of how to reset the variable 'variablex'
begin
if 'variablex' not defined then
variablex := 'xxxxxx'
else
leave it
end if;
end;
SQL > define
variablex = 'xxxxxx'
!!! Bear in mind 'variablex' is a dynamic value
use the following:
variable variablex varchar2(30)
begin
select to_char(sysdate) into :variablex from dual;
end;
/
print variablex
variable variablex varchar2(30)
begin
select to_char(sysdate) into :variablex from dual;
end;
/
print variablex
See if this is what you are after.
It uses sqplus variables not DEFINE. I don't know that you can alter a sqpllus defined variable in a PL/SQL block.
It uses sqplus variables not DEFINE. I don't know that you can alter a sqpllus defined variable in a PL/SQL block.
var variablex char(1);
begin
if :variablex is null then
:variablex := 'A';
end if;
dbms_output.put_line('Got: ' || :variablex);
end;
/
exec :variablex := 'X';
begin
if :variablex is null then
:variablex := 'A';
end if;
dbms_output.put_line('Got: ' || :variablex);
end;
/
ASKER
Ok, here is the full picture of what I need.
S1[.sql]
define user_a = 'A'
define user_b = 'B'
define user_c = 'C'
S2[.sql]
@script1 &user_a
@script1 &user_b
@script1 &user_c
When running above scripts batch mode
SQL >@S1
SQL >@S2
[ there should be no error as all variable are defined in S1 ]
Now, no change to S1
But S2 has extra line:
@script1 &user_a
@script1 &user_b
@script1 &user_c
@script1 &user_d
When running above scripts batch mode
SQL >@S1
SQL >@S2
[S2 will prompt the value for user_d as it is not defined in S1]
I wish to add Addition code in S2 to avoid such prompt.
You may ask why I do not define user_d in S1.sql, this is the requirement that user wish to have the flixbility to define or not define user_d in S1.sql,
In case the user_d does define in S1 the "@script1 &user_d" will be executed by use the value defined in S1, if not defined in S1, user_d will be set to a defualt value in S2 (from the additional code).
You solution should be like:
S2[.sql]
-----
Extra code to set user_d to a default value if it is not defined in the SQL session
-----
@script1 &user_a
@script1 &user_b
@script1 &user_c
@script1 &user_d
S1[.sql]
define user_a = 'A'
define user_b = 'B'
define user_c = 'C'
S2[.sql]
@script1 &user_a
@script1 &user_b
@script1 &user_c
When running above scripts batch mode
SQL >@S1
SQL >@S2
[ there should be no error as all variable are defined in S1 ]
Now, no change to S1
But S2 has extra line:
@script1 &user_a
@script1 &user_b
@script1 &user_c
@script1 &user_d
When running above scripts batch mode
SQL >@S1
SQL >@S2
[S2 will prompt the value for user_d as it is not defined in S1]
I wish to add Addition code in S2 to avoid such prompt.
You may ask why I do not define user_d in S1.sql, this is the requirement that user wish to have the flixbility to define or not define user_d in S1.sql,
In case the user_d does define in S1 the "@script1 &user_d" will be executed by use the value defined in S1, if not defined in S1, user_d will be set to a defualt value in S2 (from the additional code).
You solution should be like:
S2[.sql]
-----
Extra code to set user_d to a default value if it is not defined in the SQL session
-----
@script1 &user_a
@script1 &user_b
@script1 &user_c
@script1 &user_d
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
As none of the feedback give me a better solution that I have composed. I need to close this thread.
https://forums.oracle.com/forums/thread.jspa?threadID=976322
http://vbegun.blogspot.in/2008/04/on-sqlplus-defines.html (referred in above thread)