ORA-00904: invalid column name

[rsn]

Example 1:

declare
type tcref is ref cursor;
cref tcref;
ch char:='a';
begin
open cref for select ch from dual;
end;
/
Input truncated to 1 characters
PL/SQL procedure successfully completed.

Example 2:

declare
type tcref is ref cursor;
cref tcref;
ch char:='a';
begin
open cref for 'select ch from dual';
end;
/
Input truncated to 1 characters
declare
*
ERROR at line 1:
ORA-00904: invalid column name
ORA-06512: at line 7

Why the second variant doesn't work?

 

[Marco Kalter]

In the first situation the select statement is parsed at run-time, and the "ch" expression is bound to the local "ch" variable.

In the second situation, the select statement is parsed at run-time, and the local variable context is not supported.

 

[rsn]

Ok. How to receive all values of the pl/sql table?
Real example-prototype:

declare
type tresult is table of varchar2(4000) index by binary_integer;
result tresult;
type tcref is ref cursor;
cref tcref;
n number:=0;
f varchar2(32767):='';
begin
-- filling of the pl/sql table
...

for i in 0..n
loop
if (i!=0) then
f:=f||',';
end if;
f:=f||'result('||to_char(i)||')';
end loop;
open :cref for ('select '||f||' from dual');
end;

Too does not work: :-(

open :cref for 'select f from dual' using f;

 

[Marco van der Linden]

@rsn

In the 2nd example of your first post

declare
type tcref is ref cursor;
cref tcref;
ch char:='a';
begin
open cref for 'select ch from dual';
end;
/

replace this line

open cref for 'select ch from dual';

with this

open cref for 'select :ch from dual' using ch;

That should do the trick.

In your 2nd posting you have

open :cref for ('select '||f||' from dual');

I think that should be

open cref for ('select '||f||' from dual');

i.e. lose the colon before cref.

 

[rsn]

Thanks.
It's work!

open cref for 'select :f from dual' using f;

Line

open cref for ('select '||f||' from dual');

doesn't work.

The line looks like

open :cref for ('select '||f||' from dual');

because the cursor is transferred in the c++ program by DOA:

query->SetComplexVariable("cref", cursorQuery);