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…
Obviously :) Since SELECT INTO is a PL/SQL statement…
But it is strange that there’s no error.
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′;
?
@Alex F
Yeah this post was really more about “assumption is the mother of all F***ups” than the specific feature :)
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 DUALHi 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.
Would be a good post in the Little Things Doth Crabby Make series :-)