ORA-00984: column not allowed here dynamic sql

Question

I'm trying to run this sql script in oracle and get an error

ORA-00984: column not allowed here.

declare
  tablename varchar2(200):='imagesroom';
  temp varchar2(50):='room_id';
  iid number:=1;
  dir varchar2(200):='imgdirroom';
  in_fname varchar2(100):='img1.jpg';
  obj ORDIMAGE;
  ctx RAW(64) := NULL;
begin
  execute immediate 'INSERT INTO '||tablename||'('||temp||',extension,icon) 
  VALUES(iid,'''',ORDImage(''FILE'',upper(dir),in_fname)) 
  RETURNING icon INTO obj';
end;
/   

ERROR at line 1:
ORA-00984: column not allowed here
ORA-06512: at line 10

This one works great

INSERT INTO imagesroom(room_id,extension,icon) 
VALUES(iid,'',ORDImage('FILE',upper(dir),in_fname)) 
RETURNING icon INTO obj;

What's wrong with it?

if I do it this way

execute immediate 'INSERT INTO :table(:type,extension,icon) 
VALUES(:id,'''',ORDImage(''FILE'',upper(:dir),:fname)) RETURNING icon INTO obj' 
using tablename,temp,iid,dir,in_fname;

then

ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 10

appear...

Related: [Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?](http://stackoverflow.com/q/25489002/1461424) — sampathsris, Aug 26 '14 at 04:06

Nick Krasnov · Accepted Answer · 2013-11-15T19:12:56.893

Firstly, why the second insert inside a PL/SQL block works and almost the same insert executed dynamically using execute immediate statement doesn't? Simply because of name resolution. When second insert, that insert statement which you've described as this one works great is being executed iid , dir and in_fname resolved to locally declared variables. And in case of the dynamically executed insert statement those iid , dir resolved to column names of the table you are trying to insert data into, and it's simply not allowed to put column names of a table you are trying to insert into in the value clause of an insert statement. Use bind variables.

Secondly, about returning into clause. You need two of them. One as part of a dynamically formed insert statement(dynamic returning into clause) and one for execute immediate statement(static returning into clause), in order to get returning by the insert statement values out. Or, one dynamic returning into clause and one OUT bind variable(in this case).

To that end, your anonymous pl/sql block might look like this:

declare
  tablename  varchar2(200) :='imagesroom';
  temp       varchar2(50)  :='room_id';
  iid        number        :=1;
  dir        varchar2(200) :='imgdirroom';
  in_fname   varchar2(100) :='img1.jpg';
  obj        ORDIMAGE;
  ctx        RAW(64)       := NULL; -- really unnecessary, NULL by default.
  l_instr    varchar2(4000);
begin

  l_instr := 'INSERT INTO ' || tablename || '('||temp||',extension,icon) 
                VALUES(:1,'''',ORDImage(''FILE'',upper(:2), :3)) 
              RETURNING icon INTO :4';

  execute immediate l_instr 
    using iid, dir, in_fname  returning into obj;

end;
/

You can use `using iid, dir, in_fname, out obj` too - either works, so is there an advantage to using a second `returning` clause instead? — Alex Poole, Nov 14 '13 at 20:05
thanks, after I run this `temp2:='img_'||temp||'.currval'; execute immediate 'UPDATE '||tablename||' SET icon = obj WHERE img_id = '||temp2;` and it that sequences can't be used there, sequnce name is in temp2, it works in not dynamic, where is mistake? — user2993505, Nov 14 '13 at 20:10
@AlexPoole. Alex, returning into clause allows you to return a "*record*" into a variable of a record data type. You wont be able to do that using `using` clause and out-bind variable. In this particular situation(no "*record*" being returned by the DML statement) there is really no visible advantages to me, except you do not have to specify mode (OUT) for a bind variable in case you have to return more than one value into more than one out-bind variable. — Nick Krasnov, Nov 14 '13 at 21:10

score 1 · Answer 2 · answered Nov 14 '13 at 19:02

1

The RETURNING INTO clause should be outside the dynamic DML statement:

execute immediate 'INSERT INTO '||tablename||'('||temp||',extension,icon) 
  VALUES(iid,'''',ORDImage(''FILE'',upper(dir),in_fname))'
RETURNING icon INTO obj;

There's an example at the bottom of this documentation site about EXECUTE IMMEDIATE

answered Nov 14 '13 at 19:02

Przemyslaw Kruglej

8,003
2
26
41

seemd so but doesn't work, 'cos returning isn't using for dml. this statement will return error about icon, if delete it, will appear previous error – user2993505 Nov 14 '13 at 19:11
@user2993505 Sorry, my bad. – Przemyslaw Kruglej Nov 14 '13 at 21:30

Alex Poole · Answer 3 · 2013-11-14T19:58:37.573

The iid, dir, in_fname and obj references inside the SQL string you're executing are literals, so they're being interpreted as column names. You should use bind variables (as shown in the 10g docs:

...
begin
  execute immediate 'INSERT INTO '||tablename||'('||temp||',extension,icon) 
  VALUES(:iid,'''',ORDImage(''FILE'',upper(:dir),:in_fname)) 
  RETURNING icon INTO :obj'
  USING iid, dir, in_fname, OUT obj;
end;
/

You can add something like

  dbms_output.put_line(obj.in_fname);

... to verify that obj is populated.

ORA-00984: column not allowed here dynamic sql

3 Answers3