Variables in TOAD scripts

Question

I have a SQL script that is being executed in TOAD. Currently, I have it laid out with just statement after statement, thusly:

select such-and-such from somewhere;

delete other-thing from somewhere-else;

And so on. Some of the where clauses end up being repetitive because I have complex inner queries to get particular IDs to operate on. I'd like to capture the ID in the beginning of the script in a variable, and then use that variable in subsequent where clauses. So something like this:

variable MY_ID = select the-ID from somewhere;

select such-and-such from somewhere where ID = @MY_ID;

Obviously, I'm making up that syntax, but that is what I'm looking for. But I'm not sure if that is possible in a TOAD script. I know I can convert the whole thing to a PL/SQL block but I'm trying to avoid having to do that for various reasons.

Any way to do this using TOAD without converting to a PL/SQL block?

I can't offer an answer, since I'm unfamiliar with TOAD, but what are your reasons for not using PLSQL? — David Oneill, Dec 22 '09 at 16:44
There are several reasons for not switching to PL/SQL. One is that it is 90% done already and I don't feel like investing the time to switch. Two is that PL/SQL scripts are harder to debug from my experience and are also harder to "step through" one statement at a time. This could be a lack of knowledge on my part since I'm no PL/SQL expert. Third I'm handing this script off to others and again from my experience "simple" SQL scripts are easier to deal with than PL/SQL. — RationalGeek, Dec 22 '09 at 18:38

score 13 · Accepted Answer · answered Dec 22 '09 at 19:04

I think this will accomplish what you want. You can declare a bind variable, insert a value into it, and then use it in future statements.

variable l_var varchar2(1);

begin
  select dummy
    into :l_var
    from dual;
end;

select *
  from dual
 where dummy = :l_var;

score 2 · Answer 2 · edited Jan 20 '17 at 17:43

2

I use SQL*PLUS substitution variables. They are supported by TOAD. You can execute this code by pressing F5.

COLUMN VAR NEW_VALUE VAR_VALUE

SELECT 'SOMETHING' VAR FROM DUAL;  --this sets 'VAR_VALUE' = 'SOMETHING'

SELECT '&VAR_VALUE' FROM DUAL;  --this uses the value set by the previous stmt.

edited Jan 20 '17 at 17:43

Aurasphere

3,841
12
44
71

answered Jan 20 '17 at 16:08

Chris Ellison

21
1

As written, this is also a good answer: you can even have multiple variables and the whole thing can be run as a script in Toad. The last statement in the above answer can be executed by itself as a single query, and Toad will prompt you for each of the `&`-type variables; however, even if all your variables have the same name, Toad will prompt you for each and every one. – JonathanDavidArndt Jul 19 '17 at 14:19

score 1 · Answer 3 · edited May 23 '17 at 12:01

1

I no longer actively use TOAD, but there should be some mechanism for setting values for bind parameters ie select such-and-such from somewhere where ID = :myid; such that every time it occurs TOAD supplies the same value for that parameter.

Alternatively, you could create a session context value or PL/SQL package variable (note: not the same thing as rewriting your entire code to use PL/SQL). See this question

edited May 23 '17 at 12:01

Community

1
1

answered Dec 22 '09 at 17:00

Dan

10,990
7
51
80

I am aware of what you're referring to with bind variables. However, I want to capture the value of a different select statement. The variable is potentially a different value every time I run the script. – RationalGeek Dec 22 '09 at 18:36
So just set it differently at the beginning each time you run it. If you go the package route (taking Thomas Jones-Low's example) then make your first statement begin foo.myVar := 'whatever I want this time'; end; – Dan Dec 22 '09 at 20:05

score 1 · Answer 4 · answered Apr 02 '15 at 02:01

The "Calculated Fields" feature in TOAD is actually quite powerful if used in the right way. It's nothing more than a "token" script editor that attaches itself to the Query itself. It's only available via the Query Design Editor, and not from the native Editor, which allows you to write straight up SQL.

As a hint, next time you create a Query Designed in TOAD and need to create complex WHERE or sub-queries, try the "Calculated Fields" feature and use the FORMS option to basically attach your conditions to a given column or query. You'll be surprised how powerful it is. And it helps keep your SQL query in a nice readable format.

score 0 · Answer 5 · answered Nov 24 '21 at 23:55

When I tray to do a 'group by' using the result of the CASE sentence name AS Ticket, Toad show error saying that Ticket in not a valid item,

SELECT 
CASE WHEN ( amt_1 >= 10000000                             ) THEN  'R100000'
     WHEN ( amt_1 <   9000000 and amt_1 >=  8000000       ) THEN  'R090000'
     WHEN ( amt_1 <   8000000 and amt_1 >=  7000000       ) THEN  'R080000'
     WHEN ( amt_1 <   7000000 and amt_1 >=  6000000       ) THEN  'R070000'
     WHEN ( amt_1 <   6000000 and amt_1 >=  5000000       ) THEN  'R060000'
     WHEN ( amt_1 <   5000000 and amt_1 >=  4000000       ) THEN  'R050000'
     WHEN ( amt_1 <   4000000 and amt_1 >=  3000000       ) THEN  'R040000'
     WHEN ( amt_1 <   3000000 and amt_1 >=  2000000       ) THEN  'R030000'
     WHEN ( amt_1 <   2000000 and amt_1 >=  1000000       ) THEN  'R020000'
     WHEN ( amt_1 <   1000000 and amt_1 >=  500000        ) THEN  'R010000'
     WHEN ( amt_1 <    500000 and amt_1 >=  100000        ) THEN  'R005000'
     WHEN ( amt_1 <    100000                             ) THEN  'R001000'             
END  as Ticket,    
CAST ( SUM(AMT_1/100) AS DECIMAL(10,2) )  
Count(*)     
FROM BASE24.PTLF 
GROUP BY  Ticket

If you have a new question, please ask it by clicking the [Ask Question](https://stackoverflow.com/questions/ask) button. Include a link to this question if it helps provide context. - [From Review](/review/late-answers/30427382) — Ana GH, Nov 25 '21 at 10:30

Variables in TOAD scripts

5 Answers5

Linked