Oracle EXECUTE immediate UPDATE

Question

I am just started to study Oracle. When I Execute Following Statement

DECLARE
  sql_stmt VARCHAR2(200);
  table_obj user_tables.table_name%type;
begin
    for l_i in (select table_name from results) loop

      table_obj := dbms_assert.sql_object_name(l_i.table_name);

      sql_stmt :=  'update results set cnt_record = ( select count(*) from :1) where table_name = UPPER(:2)'; 

      EXECUTE IMMEDIATE sql_stmt 
      USING table_obj, l_i.table_name;          
    end loop;
end;

I get an error:

Error report -

ORA-00903: invalid table name
ORA-06512: at line 11
00903. 00000 - "invalid table name"
*Cause:
*Action:

score 0 · Accepted Answer · answered Apr 15 '18 at 12:22

0

You are only allow to pass constant names into an expression. You are not allowed to pass table names, column names, operators, function names and so on.

So, you have to "munge" the query string before you execute it:

      table_obj := dbms_assert.sql_object_name(l_i.table_name);

      sql_stmt :=  'update results set cnt_record = ( select count(*) from <tablename>) where table_name = UPPER(:1)'; 

      sql_stmt := replace(sql_stmt, '<tablename>', table_obj);

      EXECUTE IMMEDIATE sql_stmt 
      USING l_i.table_name;          
    end loop;
end;

You could replace both occurrences using replace(), but the argument in upper() is fine for a parameter.

answered Apr 15 '18 at 12:22

Gordon Linoff

1,242,037
58
646
786

Thanks! And you can more in detail about "You could replace both occurrences using replace(), but the argument in upper() is fine for a parameter." – GPlat Apr 15 '18 at 12:33
@GPlat . . . I'm making a distinction between the table being used as a name for comparison and the table being used in the `from` clause. The first can use a parameter, even though the second cannot. – Gordon Linoff Apr 15 '18 at 14:52

Oracle EXECUTE immediate UPDATE

1 Answers1