Running SELECT … INTO :bind_variable from SQL

Tanel Poder

2011/04/10

I just wasted a few minutes troubleshooting one of my scripts – and realized that while SELECT … INTO :bind_variable does not error out when executed as top-level SQL, it doesn’t seem to populate the resulting bind variable:

SQL> VAR blah VARCHAR2(10)
SQL> SELECT dummy INTO :blah FROM dual;

D
-
X

SQL> print blah

BLAH
--------------------------------


 

See, the bind variable is empty…

Now, let’s run the same statement via PL/SQL engine:

 

SQL> EXEC SELECT dummy INTO :blah FROM dual;

PL/SQL procedure successfully completed.

SQL> print blah

BLAH
--------------------------------
X


 

…and it works…

[Comments]
Click on Tweet to comment or ask a question! Keep the "via @tanelpoder" in the tweet text to notify me.
NB! Check out my 2019 online training classes here! Practical Linux Performance & Application Troubleshooting training (new), Advanced Oracle SQL Tuning training, Advanced Oracle Troubleshooting training. In addition to the online classes, all attendees will receive personal downloadable video recordings too!