Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-01-21
6
Medium Priority
?
868 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 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 78

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

824 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