ORA-903 Invalid Table Name using DBMS_SQL to bind SCHEMA or TABLE Name

(Doc ID 1296347.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version 8.0.6.0 and later
Information in this document applies to any platform.

Symptoms

When Using DBMS_SQL to create a dynamic SQL statement which passes the table name or schema name as a bind variable returns the following error:

ORA-00903: invalid table name

Code Example:

1. Create the following database objects for the use case.

SQL> create table client_profile(id number,institution_id number);

Table created.

SQL> insert into client_profile values (1,2);

1 row created.

SQL> commit;


2. Execute the following code that demonstrates the error:

declare
  v_schema_name varchar2(30) :='SCOTT';
  cursor1 integer;
  rows_processed integer;
  myid number;
  myINSTITUTION_ID number;
  v_sql_stmt varchar2(200);
BEGIN
 -- This statement contains the bind variable :x
  v_sql_stmt := 'select ID,INSTITUTION_ID from :x.CLIENT_PROFILE';

  cursor1 := dbms_sql.open_cursor;
  dbms_sql.parse (cursor1, v_sql_stmt , DBMS_SQL.native);
  dbms_sql.bind_variable(cursor1, 'x', v_schema_name);
  dbms_sql.define_column (cursor1, 1, myid);
  dbms_sql.define_column (cursor1, 2, myINSTITUTION_ID);
  rows_processed := dbms_sql.execute (cursor1);
  loop
    if dbms_sql.fetch_rows (cursor1) > 0 then
       dbms_sql.column_value (cursor1, 1, myid);
       dbms_sql.column_value (cursor1, 2, myINSTITUTION_ID);
       dbms_output.put_line('ID='||myid || ' Institution_ID=' || myINSTITUTION_ID);
    else
       exit;
    end if;
  end loop;
  dbms_sql.close_cursor (cursor1);
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(sqlerrm);
  if dbms_sql.is_open (cursor1) then
    dbms_sql.close_cursor (cursor1);
  end if;
END;
/




Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms