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…


  1. Check out my 2022 online training classes in a new format!
    Advanced Oracle SQL Tuning training. Advanced Oracle Troubleshooting training, Linux Performance & Troubleshooting training: Learn when it's convenient to you and get your training questions answered even months after taking the class!
  2. Get weekly updates by email or follow Social/RSS