Analysis of the specific causes of Oracle ORA-00903 errors

ORA-00903 Invalid table name

ORA-00903: Invalid table name

Cause A table or cluster name is invalid or does not exist. This is also issued if a invalid cluster name or no cluster name is specified in a ALTER cluster or DROP cluster Statement.

Action Check spelling. A valid table name or cluster name must begin with a and could contain only alphanumeric characters and the special C Haracters $, _, and #. The name must is less than or equal to characters and cannot is a reserved word.

Reason: The table name or cluster name does not exist or is not valid, and this error message occurs when the alter CLUSTER or DROP CLUSTER statement is run.

Scenario: Check to see if the spelling is correct. A valid table name or cluster name must begin with a letter, contain only letters or numbers, not more than 30 characters, and can contain special characters $, _, #. The table name or cluster name cannot be a keyword.

Case one: Using Dbms_sql packages to execute DDL statements

The Dbms_sql package can be used to execute DDL statements directly from Pl/sql.

This is an example of the process of creating a table. The procedure has two parameters: a list of table names and fields and their types.

CREATE OR REPLACE PROCEDURE ddlproc (tablename varchar2, cols varchar2) AS
cursor1 INTEGER;
BEGIN
cursor1 ：= dbms_sql.open_cursor;
dbms_sql.parse(cursor1, 'CREATE TABLE ' || tablename || '
( ' || cols || ' )', dbms_sql.v7);
dbms_sql.close_cursor(cursor1);
end;
/
SQL> execute ddlproc ('MYTABLE','COL1 NUMBER, COL2 VARCHAR2(10)');
PL/SQL procedure successfully completed.
SQL> desc mytable;
Name Null? Type
------------------------------- -------- ----
COL1 NUMBER
COL2 VARCHAR2(10)

NOTE: DDL statements are executed by the Parese command. Therefore, you cannot use a bind variable against a DDL statement, or you will receive an error message. The following example of using a bind variable in a DDL statement is incorrect.

**** Incorrect Example ****
CREATE OR REPLACE PROCEDURE ddlproc (tablename VARCHAR2,
colname VARCHAR2,
coltype VARCHAR2) AS
cursor1 INTEGER;
ignore INTEGER;
BEGIN
cursor1 ：= dbms_sql.open_cursor;
dbms_sql.parse(cursor1, 'CREATE TABLE ：x1 (：y1 ：z1)', dbms_sql.v7);
dbms_sql.bind_variable(cursor1, '：x1', tablename);
dbms_sql.bind_variable(cursor1, '：y1', colname);
dbms_sql.bind_variable(cursor1, '：z1', coltype);
ignore ：= dbms_sql.execute(cursor1);
dbms_sql.close_cursor(cursor1);
end;
/