Toad World® Forums

Variables in Toad for Oracle

My question is regarding something that, to me, should be blindingly simple, but I cannot find a satisfactory (or functional) answer on the internet.

In my group, we use fairly simple queries to pull information from various tables. I am trying to build a more complex query for other users that only requires them to edit one portion (the variable), then that variable would be reused at various portions of the query. My group doesn’t do any inserts or manipulation, just data pulls for reports and analysis.

For example (psudeocode):

set variable var1 = ‘change_me’

select * from schema.table where field1 = var1 and field 2 like ‘%’ + var1 + ‘%’

order by field2

I can’t find any way to do this. I find all sorts of things about DECLARE and DEFINE and whatnot, but it doesn’t work at all.

To me, this should be one of the most simple things you could do in a query, but it does not appear so. I know that I can use :var1 to get the dialog to pop up, but I wish to do this without the dialog.

Does anyone know how I can do this?

Does this helps you (pure sqlplus variables, that may be used all around Oracle). MIne database is XE:

column a_db new_value a_db_value
select name a_db from v$database;

column a_date_from new_value a_date_from_value
column a_date_to new_value a_date_to_value
select to_char(MIN(end_interval_time),'yyyy-mm-dd') a_date_from,
to_char(MAX(end_interval_time),'yyyy-mm-dd') a_date_to
from dba_hist_snapshot ;

spool c:\temp\database-&a_db_value-&a_date_from_value-&a_date_to_value

select 1 from dual;

spool off;

host dir c:\temp

Result is:

A_DB

XE
1 row selected.

A_DATE_FROM

A_DATE_TO

2014-02-04
2014-02-08

1 row selected.

     1

     1

1 row selected.
Volume in drive C is Boot
Volume Serial Number is 161E-6352

Directory of c:\temp

08.02.2014. 06:44 .
08.02.2014. 06:44 ..
08.02.2014. 06:44 55 database-XE-2014-02-04-2014-02-08.LST
1 File(s) 55 bytes
2 Dir(s) 85.913.124.864 bytes free

I don’t see how that is supposed to work. The column line seems to have three pieces of required information - I only want two, the variable name and the information in the variable. I am not trying to Select information into a variable, I want to just manually set the variable before the Select statement. Even when I can get it to process (in theory) the variable input without an error, it gives me an error every time when I try to actually use the variable in the query itself. I usually only do basic queries in SQL, and this is something that should be sooooo basic.

You want to use substitution variables if I understand you correctly. Paste the following into the Editor and execute as a script using F5 to see for yourself.

define var = ‘Variable Value’

select ‘&&var’ from dual;

select ‘&&var’ from dual;

On 02/10/2014 01:36 PM, Hubert wrote:

RE: Variables in Toad for Oracle

Reply by Hubert
I don’t see how that is supposed to work. The column line seems to have three pieces of required information - I only want two, the variable name and the information in the variable. I am not trying to Select information into a variable, I want to just
manually set the variable before the Select statement. Even when I can get it to process (in theory) the variable input without an error, it gives me an error every time when I try to actually use the variable in the query itself. I usually only do basic
queries in SQL, and this is something that should be sooooo basic.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.


No virus found in this message.

Checked by AVG - www.avg.com

Version: 2014.0.4259 / Virus Database: 3684/7045 - Release Date: 01/30/14

Internal Virus Database is out of date.

Hubert,

Mine example shows much more complicated then manual settings. Please look int next example:

column a_db new_value a_db_value

select ‘xxx’ a_db from dual;

Now in “&a_db_value” it is “xxx” as a value. Look the whole output from toad;

A_DB


xxx

1 row selected.

old: select ‘&a_db_value’ from dual

new: select 'xxx ’ from dual

‘XXX’


xxx

1 row selected.

Hope now is more clear

Brg

Damir

Taking your original example, Hubert, I created this variation (changing to tablenames that I have) and when run with f5 it does not present any dialog box
but just runs:

define
var1 =
‘change_me’

select
*
from s_acct
where name_1

‘&&var1’
and name_2
like
‘%&&var1%’

order
by name_2

Toad even shows you in the “script output” box what the statement looked like after variable substitution, if you want to double-check or trouble-shoot.

Nate Schroeder

US Row Crops IT Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. G3WB - Saint Louis, MO - 63167

314-694-2592

This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled

to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and

all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.

All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its

subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of “Viruses” or other “Malware”.

Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying

this e-mail or any attachment.

The information contained in this email may be subject to the export control laws and regulations of the United States, potentially

including but not limited to the Export Administration Regulations (EAR) and sanctions regulations issued by the U.S. Department of

Treasury, Office of Foreign Asset Controls (OFAC). As a recipient of this information you are obligated to comply with all

applicable U.S. export laws and regulations.

Michael, Nate, you are right on the money. That worked perfectly, especially when I used F5 instead of F9.

Thank you very much!

You can also do the same using F9 to execute for single statements. In this case the user will see the dialog, but will be prompted for the value only once. In reality this is no more cumbersome than having them edit the SQL script directly and it gives you
access to the other benefits of running via F9 compared to F5.

This is a terrible example, but execute the following with F9. Enter your value once and it’s used in all places where its referenced.

SELECT ‘&&var’, ‘&&var’ from dual;

On 02/10/2014 02:39 PM, Hubert wrote:

RE: Variables in Toad for Oracle

Reply by Hubert
Michael, Nate, you are right on the money. That worked perfectly, especially when I used F5 instead of F9.

Thank you very much!

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.


No virus found in this message.

Checked by AVG - www.avg.com

Version: 2014.0.4259 / Virus Database: 3684/7045 - Release Date: 01/30/14

Internal Virus Database is out of date.

I do not get why mine examples were not OK, they offer even more flexibility…or I do not understand the something?

Here is another example with integer value. Ithink that this approach may be defined as constant or any value from database:

A_V


123

1 row selected.

old: select &a_real_value*1000 from dual

new: select 123*1000 from dual

123*1000


123000

1 row selected.

sqlplus_demo.png

Damir, your answer may be absolutely correct, I just don’t understand it. I don’t understand what the column command is doing, nor why there are apparently three inputs for the column command, when I would only expect two.

column <something?> <something2?> <something3?> <-- I don’t know what these pieces do. Are they three different variables being declared? Is one an input for a variable? Then what is the third? As I said above, most of my SQL experience is in writing very simple queries.

I’ve run into a new problem. I got the basic substitution variable to work as listed by Michael and Nate, but now I need a second substitution variable that I can select a value into, but I cannot get it to work. This is the syntax I am trying (based on what I read on the net):

select data into &&variable2 from database.table where field = ‘&&variable1’

When the script gets to the use of &&variable2 in the main select statement, it pops up and asks me for a value, and the log says that variable2 is undefined.

I also tried:

define variable2 = (select data from database.table where field = ‘&&variable1’), but that just inserted ‘select’ into the variable.

column <something2?> <something3?>

something1=alias you’ll use in query where you’ll assign value

something2=leave as is always “new_value” hardcoded!

something3=this is alias which is always called with ampersand i.e &real_value

Imagine this case scenario (emp table from scot schema)

SQL> select empno, ename, mgr from emp;

EMPNO ENAME             MGR

 7369 SMITH            7902

 7499 ALLEN            7698

 7521 WARD             7698

 7566 JONES            7839

 7654 MARTIN           7698

 7698 BLAKE            7839

 7782 CLARK            7839

 7839 KING

 7844 TURNER           7698

 7900 JAMES            7698

 7902 FORD             7566

 7934 MILLER           7782

12 rows selected.

Now let’s make query with two user defined variable (something like your case)

– assign a_v1 as 7844.

col a_v1 new_value a_real_value1;
select 7844 a_v1 from dual;

– a_v1 passed value to &a_real_value1

– let us see the content of &a_real_value1

select &a_real_value1 from dual;

– this &a_real_value1 will be used later as part of where condition

– now let’s assign a_v2 variable

col a_v2 new_value a_real_value2;

select ename a_v2 from emp where empno=&a_real_value1;

– a_v2 is assigned with ename which is retrieved with empno 7844 (defined through &a_real_value1 variable)

– now let us see value of second variable

– it is string soit should have ampersand

select ‘&a_real_value2’ final_value from dual;

now here is whole example without comments:

SQL> select &a_real_value1 from dual;

old 1: select &a_real_value1 from dual

new 1: select 7844 from dual

** 7844**

----------

** 7844**

SQL> col a_v1 new_value a_real_value1;

SQL> select 7844 a_v1 from dual;

** A_V1**

----------

** 7844**

SQL> select &a_real_value1 from dual;

old 1: select &a_real_value1 from dual

new 1: select 7844 from dual

** 7844**

----------

** 7844**

SQL> col a_v2 new_value a_real_value2;

SQL> select ename a_v2 from emp where empno=&a_real_value1;

old 1: select ename a_v2 from emp where empno=&a_real_value1

new 1: select ename a_v2 from emp where empno= 7844

A_V2

----------

TURNER

SQL> select ‘&a_real_value2’ final_value from dual;

old 1: select ‘&a_real_value2’ final_value from dual

new 1: select ‘TURNER’ final_value from dual

FINAL_VALUE

--------------------------------

TURNER

SQL>

And this is exactly your ask how to emulate “select data into &&variable2 from database.table where field = ‘&&variable1’” in sqlplus.

just to accent that this approach is universal for sqlplus (script) in toad as well Oracle SQL*Plus

Can you define a date variable? Other documentation I have read indicates you can only define CHAR variables

Every date is represented as CHAR formatted value.

So answer is Yes.