Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query like

SELECT id FROM xyz WHERE ...;

which has a more or less complex WHERE clause and returns exactly one row with one column (ID). I need this ID for several later queries in a script. So my goal was to reuse this result in a variable but I don´t come to a solution which works.

Joining this query would not be a good option in this circumstance. So is it possible to tell Oracle to store this single result in a variable and use this variable to build up new queries - explicitly to use in in the conditional clause of other queries?

If so, perhaps somebody could post a simple example - most simple perhaps would be sth. like

SELECT :VARIABLE FROM DUAL
share|improve this question
2  
What is executing your script? SQL*Plus? –  Jack Douglas Oct 29 '12 at 9:14
    
Currently I´m using Toad for Oracle - but could use Sqlplus too. This shouldn´t make any difference... –  cljk Oct 29 '12 at 11:19
1  
Why do you think "this shouldn´t make any difference"? SQL*Plus has it's own variables that are quite distinct from the database (and of course therefore can't be used in Toad) –  Jack Douglas Oct 29 '12 at 11:23
    
or do you mean that you are looking for a solution that can be run in SQLPlus *or Toad? –  Jack Douglas Oct 29 '12 at 11:27
    
I agree that it shouldn't make any difference, but it does. –  Colin 't Hart Oct 29 '12 at 11:32

2 Answers 2

For Toad, from this answer on SO:

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;
share|improve this answer
    
TOAD seems to support at least some SQLPlus syntax. But TOAD is more forgiving with missing ; and / as well as comments at the end of lines (which will cause the statement to be ignored in SQLPlus, but TOAD will execute it). –  Colin 't Hart Oct 29 '12 at 11:31
    
In other words you can't guarantee your script will have the same action in both :) –  Jack Douglas Oct 29 '12 at 11:35
1  
You can, but you must be very careful to use the syntax supported by both SQL*Plus and TOAD! And test, test, test in both tools. –  Colin 't Hart Oct 29 '12 at 11:36

It makes a difference... SQL*Plus can use variables, but they're a SQL*Plus feature and you can just grab that block of code and pass it to an Oracle backend to execute (like you could with a Transact SQL block for example).

You can choose between using bind variables in SQL*Plus as follows: http://www.adp-gmbh.ch/ora/sqlplus/use_vars.html

Or use SQL*Plus's own variables with DEFINE and COLUMN commands: http://www.adp-gmbh.ch/ora/sqlplus/new_value.html

If you really need the code to be executable in other environments, you will probably have to go down the PL/SQL route.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.