DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00900 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00900: Invalid SQL Statement — Causes, Fixes & Prevention

ORA-00900 is thrown by Oracle when the database engine cannot recognize the submitted text as a valid SQL statement. This typically happens due to typos in SQL keywords, unsupported syntax, or malformed dynamic SQL strings. It is one of the most common parse-level errors and can affect developers of all experience levels.

Top 3 Causes and Fixes

1. Typos or Invalid SQL Keywords

A misspelled keyword such as SELEC instead of SELECT, or using another database's proprietary syntax (e.g., MySQL's SHOW TABLES) in Oracle, will immediately trigger ORA-00900.

-- Incorrect (typo in keyword)
SELEC employee_id, first_name
FORM employees
WHER department_id = 10;

-- Correct
SELECT employee_id, first_name
FROM employees
WHERE department_id = 10;

-- Wrong: MySQL-style syntax not supported in Oracle
-- SHOW TABLES;

-- Correct Oracle equivalent
SELECT table_name
FROM user_tables
ORDER BY table_name;
Enter fullscreen mode Exit fullscreen mode

Fix: Always double-check SQL keywords. Use an IDE like Oracle SQL Developer or Toad that provides syntax highlighting and auto-completion to catch typos before execution.

2. Malformed Dynamic SQL

When building SQL strings dynamically in PL/SQL or application code, concatenation errors can result in missing keywords, NULL strings, or incomplete statements being sent to the Oracle engine.

-- Problematic dynamic SQL (missing keyword due to bad concatenation)
DECLARE
    v_sql    VARCHAR2(4000);
    v_table  VARCHAR2(30) := 'EMPLOYEES';
    v_result NUMBER;
BEGIN
    -- Bad: accidentally omits SELECT keyword
    v_sql := ' * FROM ' || v_table;  -- missing SELECT

    -- Always print/log SQL before executing for debugging
    DBMS_OUTPUT.PUT_LINE('SQL to execute: ' || v_sql);

    EXECUTE IMMEDIATE 'SELECT' || v_sql INTO v_result;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Failed SQL: ' || v_sql);
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

-- Correct and safe dynamic SQL with bind variable
DECLARE
    v_sql     VARCHAR2(4000);
    v_dept_id NUMBER := 10;
    v_count   NUMBER;
BEGIN
    v_sql := 'SELECT COUNT(*) FROM employees WHERE department_id = :1';
    EXECUTE IMMEDIATE v_sql INTO v_count USING v_dept_id;
    DBMS_OUTPUT.PUT_LINE('Row count: ' || v_count);
END;
/
Enter fullscreen mode Exit fullscreen mode

Fix: Always log the complete SQL string before executing it. Add a NULL/empty check guard before calling EXECUTE IMMEDIATE.

3. Trailing Semicolons or Multiple Statements in One Call

When executing SQL through JDBC, ODBC, or OCI drivers, including a semicolon (;) at the end of a statement or bundling multiple statements into a single execution call can cause ORA-00900.

-- In JDBC (Java), this may raise ORA-00900:
-- String sql = "SELECT * FROM employees WHERE employee_id = 101;";
--                                                              ^ remove this

-- Correct for JDBC:
-- String sql = "SELECT * FROM employees WHERE employee_id = 101";

-- In SQL*Plus or SQL Developer, semicolons are fine:
SELECT employee_id, first_name, salary
FROM employees
WHERE employee_id = 101;

-- Do NOT run multiple statements in one JDBC execute() call:
-- "SELECT * FROM employees; SELECT * FROM departments;"  ← ORA-00900

-- Run them separately instead:
SELECT * FROM employees WHERE rownum <= 3;
-- (separate execution)
SELECT * FROM departments WHERE rownum <= 3;
Enter fullscreen mode Exit fullscreen mode

Fix: Strip trailing semicolons when passing SQL to Oracle drivers programmatically. Execute each statement individually rather than batching multiple SQL statements in one call.

Quick Prevention Tips

  • Use a proper SQL IDE: Tools like Oracle SQL Developer, Toad, or DBeaver provide real-time syntax validation and auto-complete, eliminating most keyword typos before execution.
  • Always use bind variables: Bind variables reduce dynamic SQL complexity and lower the risk of malformed statements.
  • Log failed SQL strings: Implement error logging that captures the exact SQL string on failure — this makes diagnosing ORA-00900 dramatically faster in production. 
-- Simple error logging pattern
DECLARE
    v_sql VARCHAR2(4000) := 'SELEC * FORM employees'; -- bad SQL
BEGIN
    EXECUTE IMMEDIATE v_sql;
EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO sql_error_log (error_code, error_msg, failed_sql)
        VALUES (SQLCODE, SQLERRM, v_sql);
        COMMIT;
        RAISE;
END;
/
Enter fullscreen mode Exit fullscreen mode

Related Oracle Errors

Error Code Description
ORA-00901 Invalid CREATE command
ORA-00903 Invalid table name
ORA-00907 Missing right parenthesis
ORA-00911 Invalid character in SQL (e.g., stray semicolon)
ORA-00921 Unexpected end of SQL command

ORA-00900 is a parse-level error — fix it by reviewing the exact SQL string being submitted, correcting any typos, removing unsupported syntax, and ensuring your SQL is complete before execution.

📖 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)