Solved

How to check if a SQL*Plus variable has been defined or not

Posted on 2013-01-21
6
730 Views
Last Modified: 2013-01-27
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
0
Comment
Question by:tindavid
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 21

Expert Comment

by:Amitkumar Panchal
ID: 38805037
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 38805709
use the following:

variable variablex varchar2(30)

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

print variablex
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38806050
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

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:tindavid
ID: 38808375
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
0
 

Accepted Solution

by:
tindavid earned 0 total points
ID: 38808647
Hi All,

I have finally composed the solution as below:

The only problem is that I had to spool an out put for SQL regexp to search the variable existing or not. Well, without further advise I had to take this as my soltuion for the customer.



Env.sql  ¿- as usual, no change
=====================================
Define user_a = 'A'
Define user_b = 'B'
Define user_c = 'C'
===========================

01_rollout.sql
===========================
@env
@set_var  USER_A   NA   A
@set_var  USER_B   NA   B
@set_var  USER_C   NA   C
@set_var  USER_D   NA   D
@set_var  USER_E   NA    E
@set_var  USER_F   NA    F
Define
===========================

Where value of arg1, arg2 and arg3 :
  Arg1 -- the variable name
  Arg2 – Default value if not defined in env.sql
 Arg3 – Same value to be defined as in env.sql (must in sync with env.sql)


set_var.sql
=============================================================================
Rem reusable script to setup undefined plus variable in env.sql
rem
define unknow_var = '&1'
define unknow_def = '&2'
define unknow_val = '&3'

spool define_output.lst
define
spool off
column  var_col_defined new_value var_defined
select  case when  regexp_like ( Q'[
@define_output.lst
]'
, 'DEFINE &&unknow_var[[:blank:]]*='  -- or put any variable name in place of 1
)
then  'Y'
else  'N'
end  as var_col_defined
from dual;

col return_value new_value var_defined noprint
var in_var varchar2(100)
set serveroutput on
begin
  :in_var := '&&var_defined';
  if :in_var = 'N' then
     :in_var := '&&unknow_def';  -- setting a default value
  else
     :in_var := '&&unknow_val';  -- this value should be same as value in env.sql  define user_d = 'D'
  end if;
end;
/
col return_value new_value &&unknow_var noprint
select :in_var return_value from dual;
undefine var_defined
================================================================================


Testing

1)      Just edit the env.sql to comment out any variable that you have set

Define user_a = 'A'
Define user_b = 'B'
Define user_c = 'C'
rem Define user_d = 'D'
rem Define user_e = 'E'
Define user_f = 'F'

2)      
SQL > @env
SQL > @01_rollout
0
 

Author Closing Comment

by:tindavid
ID: 38823892
As none of the feedback give me a better solution that I have composed. I need to close this thread.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

622 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