Oracle动态更新语句报错ORA-00903：无效表名求助

Got it, let's break down why you're hitting this ORA-00903 error. The core problem here is a super common pitfall with dynamic SQL in Oracle: you can't use bind variables for database object names (like tables or columns).

What's Wrong with Your Current Code? #

When you write select count(*) from :1 and pass table_obj via the USING clause, Oracle treats that bind variable as a string literal. Instead of interpreting it as a table name, it tries to execute something like:

select count(*) from 'your_table_name'

That's invalid syntax—table names can't be wrapped in quotes like this, which is exactly why you're getting the "invalid table name" error.

The Fix #

Since bind variables don't work for object identifiers, you need to safely concatenate the validated table name directly into your dynamic SQL. Great call using dbms_assert.sql_object_name to validate the table name first—this protects against SQL injection, which is critical when working with dynamic SQL.

Here's the corrected code:

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);
    -- Concatenate the validated table name into the SQL string
    sql_stmt := 'update results set cnt_record = (select count(*) from ' || table_obj || ') where table_name = UPPER(:1)';
    -- Use bind variable only for the WHERE clause value (the correct use case)
    EXECUTE IMMEDIATE sql_stmt USING l_i.table_name;
  end loop;
end;
/

Key Notes #

• dbms_assert.sql_object_name guarantees that table_obj is a valid, existing table name in your database. Never skip this step when concatenating dynamic object names—it blocks malicious SQL injection attempts.
• We still use a bind variable (:1) for the WHERE clause value (l_i.table_name), which is exactly what bind variables are designed for: passing literal values, not object identifiers.

内容的提问来源于stack exchange，提问作者GPlat