Variables in TOAD scripts

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;

​x
 
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;

 
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?

Answer

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;

 
variable l_var varchar2(1);​begin  select dummy    into :l_var    from dual;end;​select *  from dual where dummy = :l_var;​

Advertisement

Answer