Link to home
Start Free TrialLog in
Avatar of tindavid
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
Avatar of Am P
Am P
Flag of India image

use the following:

variable variablex varchar2(30)

begin
  select to_char(sysdate) into :variablex from dual;
end;
/

print variablex
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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.

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;
/

Open in new window

Avatar of tindavid

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
ASKER CERTIFIED SOLUTION
Avatar of tindavid
tindavid

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
As none of the feedback give me a better solution that I have composed. I need to close this thread.