DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00903 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00903: Invalid Table Name — Causes, Fixes & Prevention

ORA-00903 is thrown by Oracle when the parser encounters a token in the position where a valid table name is expected, but the provided name violates Oracle's object-naming rules or is otherwise unrecognizable. This commonly happens when a reserved word is used as a table name, when the name contains illegal characters, or when a dynamically built SQL string produces an empty or NULL table name. Understanding the root cause quickly is key to resolving it without wasted debugging time.

Top 3 Causes & SQL Examples

1. Using an Oracle Reserved Word as a Table Name

Oracle reserves hundreds of keywords (ORDER, SELECT, TABLE, FROM, etc.). Using any of them as an unquoted table name immediately triggers ORA-00903.

-- Triggers ORA-00903
SELECT * FROM ORDER;

-- Fix: wrap in double quotes (case-sensitive from here on)
SELECT * FROM "ORDER";

-- Better fix: rename the table entirely
ALTER TABLE "ORDER" RENAME TO SALES_ORDER;
SELECT * FROM SALES_ORDER;
Enter fullscreen mode Exit fullscreen mode

2. Illegal Characters or Spaces in the Table Name

Oracle identifiers must start with a letter and contain only letters, digits, _, $, or #. Hyphens, spaces, slashes, and dots are not permitted in unquoted names.

-- Triggers ORA-00903 (hyphen is not allowed)
SELECT * FROM SALES-DATA;

-- Temporary workaround with double quotes
SELECT * FROM "SALES-DATA";

-- Permanent fix: recreate with a valid name
CREATE TABLE SALES_DATA AS SELECT * FROM "SALES-DATA";
DROP TABLE "SALES-DATA";

-- Spaces in table name
SELECT * FROM EMPLOYEE DATA;   -- ORA-00903
SELECT * FROM "EMPLOYEE DATA"; -- works, but not recommended
ALTER TABLE "EMPLOYEE DATA" RENAME TO EMPLOYEE_DATA;
Enter fullscreen mode Exit fullscreen mode

3. NULL or Empty Table Name in Dynamic SQL

When building SQL strings dynamically in PL/SQL or application code, a NULL or blank table name variable leaves nothing after FROM, causing the error. Additionally, Oracle does not allow bind variables for object names — concatenation is required.

-- Wrong: bind variable cannot be used for table name
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :tname'
    INTO v_count USING 'EMPLOYEES';
-- ORA-00903

-- Correct: validate first, then concatenate
DECLARE
    v_tname VARCHAR2(128) := 'EMPLOYEES';
    v_sql   VARCHAR2(500);
    v_count NUMBER;
    v_exists NUMBER;
BEGIN
    -- Guard against NULL/empty
    IF v_tname IS NULL OR TRIM(v_tname) = '' THEN
        RAISE_APPLICATION_ERROR(-20001, 'Table name cannot be NULL.');
    END IF;

    -- Validate object existence before executing
    SELECT COUNT(*) INTO v_exists
      FROM ALL_TABLES
     WHERE TABLE_NAME = UPPER(v_tname)
       AND OWNER = SYS_CONTEXT('USERENV','CURRENT_SCHEMA');

    IF v_exists = 0 THEN
        RAISE_APPLICATION_ERROR(-20002, 'Table does not exist: ' || v_tname);
    END IF;

    -- Use DBMS_ASSERT to prevent SQL injection
    v_sql := 'SELECT COUNT(*) FROM '
             || DBMS_ASSERT.SQL_OBJECT_NAME(UPPER(v_tname));
    EXECUTE IMMEDIATE v_sql INTO v_count;
    DBMS_OUTPUT.PUT_LINE('Row count: ' || v_count);
END;
/
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

Situation Fix
Reserved word as table name Rename the table or wrap in "double quotes"
Illegal characters in name Rename using only A-Z, 0-9, _, $, #
NULL table name in dynamic SQL Add a NOT NULL guard before EXECUTE IMMEDIATE
Bind variable used for table name Switch to string concatenation + DBMS_ASSERT

Prevention Tips

Follow Oracle Naming Standards from Day One
Establish a DDL naming convention that enforces alphanumeric-plus-underscore names, avoids all reserved words, and keeps names within 30 characters (128 for Oracle 18c+). Bake these rules into your code review checklist or a DDL linting step in your CI/CD pipeline.

Always Use DBMS_ASSERT in Dynamic SQL
Whenever a table name is assembled at runtime, run it through DBMS_ASSERT.SQL_OBJECT_NAME(). It validates that the name refers to a real, accessible object and simultaneously blocks SQL injection — two problems solved with one function call.

Related Oracle Errors

  • ORA-00904 – invalid identifier: same class of error but applies to column names.
  • ORA-00942 – table or view does not exist: the name is syntactically valid but the object isn't found or accessible.
  • ORA-01741 – illegal zero-length identifier: raised when an empty string "" is used as an identifier.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)