Oracle ORA-00903 error Cause Analysis

Source: Internet
Author: User
Tags alphanumeric characters

ORA-00903 invalid table name

ORA-00903: Invalid table name

Cause a table or cluster name is invalid or does not exist. This
Message is also issued if an invalid cluster name or no cluster name is
Specified in an alter cluster or drop cluster statement.

Action check spelling. A valid table name or cluster name must
Begin with a letter and may contain only alphanumeric characters and
The special characters $, _, and #. The name must be less than or equal
To 30 characters and cannot be a reserved word.

Cause: The table name or cluster name does not exist or is invalid. This error message is displayed when you run the alter cluster or drop cluster statement.

Solution: Check whether the spelling is correct. A valid table name or cluster name must start with a letter and contain only letters or numbers. It cannot exceed 30 characters and can contain special characters $ ,_,#. The table name or cluster name cannot be a keyword.

Case 1: Use the dbms_ SQL package to execute DDL statements

The dbms_ SQL package can be used to execute DDL statements directly from PL/SQL.

This is an example of creating a table. This process has two parameters: Table Name and field and its type list.

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 bind variables for DDL statements. Otherwise, you will receive an error message. The following example of using bind variables in DDL statements 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;
/

Although there is no error message during process creation. But at runtime, you will get the error message "ORA-00903: Invalid table name ".

SQL> execute ddlproc ('mytable', 'col1', 'number ');

Begin ddlproc ('mytable', 'col1', 'number'); end;

*

Error at line 1:

ORA-00903: Invalid table name

ORA-06512: At "SYS. dbms_sys_ SQL", line 239

ORA-06512: At "SYS. dbms_ SQL", line 25

ORA-06512: At "Scott. ddlproc", line 8

ORA-06512: At line 1

Case 2: trigger errors in SQL * Plus

How did you find the number dbms_error_code? How can I display the Oracle error description in SQL * Plus?

Row 3 error:

ORA-04098: The 'System. log_errors_trig 'trigger is invalid and cannot take effect again

ORA-00903: Invalid indication

Ora errors can be found in the full documentation in the error message Guide (technet.oracle.com. The error message you listed is system.
The trigger named log_errors_trig in the ID is invalid because an invalid table name is referenced in the trigger. You need to find the trigger code and start from there.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.