accessing a bind variable in sqlplus

Question

In the following example,

variable recordId number;

BEGIN
  SELECT MAX(recordvalue) 
    INTO recordId 
    FROM sometable;
END;

PRINT recordid;
SELECT * 
  FROM someothertable 
 WHERE recordkey = &recordId;

The select statement on the last line cannot access the value of recordId. I know i can access recordId inside the pl/sql block using :recordId but is there a way to access recordId in a sql statement that is not in a pl/sql block? (like in the last line).

Answer 1

You can use bind variables in SQL*Plus too, still as :recordId. The & version will prompt for a value, and has no direct relationship to the variable version.

variable recordId number;

BEGIN
    SELECT MAX(recordvalue) 
    INTO :recordId 
    FROM sometable;
END;
/

PRINT recordid;

SELECT * 
FROM someothertable 
WHERE recordkey = :recordId;

The slightly more common way to assign values to bind variables is with exec :recordId := value;, but exec is really just shorthand for an anonymous block anyway.

Not sure why you'd want to mix and match like this though. If the intention is to use the result of one query in a later one, you could try new_value instead:

column x_val new_value y_val; -- could also noprint if you do not need to see the intermediate value

SELECT MAX(recordvalue) x_val
FROM sometable;

SELECT * 
FROM someothertable 
WHERE recordkey = &y_val;