Running SELECT … INTO :bind_variable from SQL

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…

This entry was posted in Oracle and tagged . Bookmark the permalink.

6 Responses to Running SELECT … INTO :bind_variable from SQL

  1. Alex F says:

    Obviously :) Since SELECT INTO is a PL/SQL statement…
    But it is strange that there’s no error.

  2. Sokrates says:

    nive observation, ‘select into’ within SQL works without exception
    did you know, that ‘the opposite’, ‘select without into’ within PL/SQL also works without exception ?

    without testing it out, can you answer which outcome has
    exec execute immediate ‘select dummy from dual connect by level > 0′;
    ?

  3. Tanel Poder says:

    @Alex F

    Yeah this post was really more about “assumption is the mother of all F***ups” than the specific feature :)

  4. It seems that SQL engine just ignores such “into” clause (but “into” clause in not top level will lead to ORA-01744).

    DB10 > conn ...
    Connected.
    DB10 > select dummy, dummy, dummy into :bind from dual;
    DUM DUM DUM
    --- --- ---
    X   X   X
    
    DB10 >
    DB10 > select executions, sql_text
      2    from v$sql
      3   where sql_text like 'select dummy, dummy, dummy%';
    
    EXECUTIONS SQL_TEXT
    ---------- --------------------------------------------------------------------------------
             1 select dummy, dummy, dummy into :bind from dual
    
    DB10 >
    DB10 > declare
      2    d1 varchar2(1);
      3    d2 varchar2(1);
      4    d3 varchar2(1);
      5  begin
      6
      7    execute immediate 'select dummy, dummy, dummy into :bind from dual' into d1, d2, d3;
      8
      9  end;
     10  /
    
    PL/SQL procedure successfully completed.
    
    DB10 >
    DB10 > select executions, sql_text
      2    from v$sql
      3   where sql_text like 'select dummy, dummy, dummy%';
    
    EXECUTIONS SQL_TEXT
    ---------- --------------------------------------------------------------------------------
             1 select dummy, dummy, dummy into :bind from dual
             1 select dummy, dummy, dummy into :bind from dual
    
    DB10 >
    DB10 > alter system flush shared_pool;
    
    System altered.
    
    DB10 >
    DB10 > exec select dummy, dummy, dummy into :bind from dual;
    SP2-0552: Bind variable "BIND" not declared.
    DB10 >
    DB10 > var bind1 varchar2
    DB10 > var bind2 varchar2
    DB10 > var bind3 varchar2
    DB10 >
    DB10 > exec select dummy, dummy, dummy into :bind1, :bind1, :bind3 from dual;
    
    PL/SQL procedure successfully completed.
    
    DB10 >
    DB10 > select executions, sql_text
      2    from v$sql
      3   where lower(sql_text) like 'select dummy, dummy, dummy%';
    
    EXECUTIONS SQL_TEXT
    ---------- --------------------------------------------------------------------------------
             1 SELECT DUMMY, DUMMY, DUMMY FROM DUAL
    
  5. Hi Tanel,

    when we checked across the trace for first scenario internally its transformed and nothing is returned

    SELECT “DUAL”.”DUMMY” “DUMMY” FROM “SYS”.”DUAL” “DUAL”
    SELECT dummy INTO :blah FROM dual
    SELECT :blah blah FROM DUAL

    for second test case

    —– Current SQL Statement for this session (sql_id=g46579zv4x31b) —–
    BEGIN SELECT dummy INTO :blah FROM dual; END;

    SELECT DUMMY FROM DUAL
    —– PL/SQL Stack —–
    —– PL/SQL Call Stack —–
    object line object
    handle number name
    21FBD764 1 anonymous block

    SELECT “DUAL”.”DUMMY” “DUMMY” FROM “SYS”.”DUAL” “DUAL”

    So, it’s an object does got allocated in memory internally. So, it does returned value from pl/sql. If I recall script which you have utilized for ORA-04031 session for recreating the scenario/issue, the pl/sql does allocate memory allocations when compared/fired across from sql engine..
    let me know your valuable comments.

  6. Kevin says:

    Would be a good post in the Little Things Doth Crabby Make series :-)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>