Why isn't my COLUMN NEW_VALUE getting set by my SELECT statement?

Question

I have the following SQL*Plus code:

COLUMN x NEW_VALUE x2
BEGIN
   -- ...
   EXECUTE IMMEDIATE 'SELECT ''a'' x FROM dual';
   EXECUTE IMMEDIATE 'SELECT colname x FROM some_table WHERE (some_condition)';
   -- ...
END;

at this point, &x2 is still empty, and colname is NOT NULL. Why wasn't it set, to 'a' if nothing else?

Alex Poole · Accepted Answer · 2012-06-29T20:18:36.037

COLUMN is an SQL*Plus client construct, and the dynamic SQL is running inside the PL/SQL engine, so there's no real connection. I'm not sure where you'd document something not applying.

The closest I can think of for this - assuming you're doing this to have the results of your dynamic SQL available to plan SQL statements later in a script - is using bind variables instead:

var x varchar2(30);

BEGIN
    EXECUTE IMMEDIATE 'SELECT ''a'' x FROM dual' INTO :x;
END;
/

select :x from dual;

You can use :x anywhere you'd have used &x2, but bear in mind that because it's a bind variable you don't need to put it in single quotes (if it's a varchar) when you use it; if you did this:

select ':x' from dual;

... then you'd back the literal string :x, not a.

Edited to add

If you particularly need the & form, you can do a further step (untested, but can't see why not):

column x2 new_value x3
select :x as x2 from dual;

... and you'll have &x3 available.

I like the EXECUTE IMMEDIATE ... INTO :x, wasn't aware of that usage! — Thought, Jun 29 '12 at 20:11
Also: My confusion I think came from the fact that &variables "worked" inside of the EXECUTE IMMEDIATE string, so the assumption that COLUMN would also fill them in there followed subconsciously. Thinking it through, though, they only work "inside" of the EXECUTE IMMEDIATE because SQL*Plus performs the replacement _before_ sending the text to the PL/SQL processor. — Thought, Jun 29 '12 at 20:15

score 0 · Answer 2 · answered Jun 29 '12 at 17:07

After hours of testing and searching (fruitlessly) on the web, this is the best I can come up with: EXECUTE IMMEDIATE runs in its own bubble universe, at least as far as the COLUMN support is concerned. It doesn't even see it, and won't no matter what you do. No idea why this is, or why it isn't documented anywhere (at least that I can find).

Why isn't my COLUMN NEW_VALUE getting set by my SELECT statement?

2 Answers2