I have a query like


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

  • 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

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);

  select dummy
    into :l_var
    from dual;

select *
from dual
where dummy = :l_var;
  • 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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