Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-01-21
6
Medium Priority
?
786 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
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 video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

715 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