Solved

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

Posted on 2013-01-21
6
556 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
6 Comments
 
LVL 20

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle Finace 3 47
query returning everything 11 68
What is the version of ojdbc6.jar 2 24
sql query 9 22
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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 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
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 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