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…