ORA-00900: Invalid SQL Statement - Fix Syntax Errors

ORA-00900: Invalid SQL Statement

Error Overview

Error Text: ORA-00900: invalid SQL statement

The ORA-00900 error occurs when Oracle’s SQL parser cannot recognize the submitted text as a valid SQL statement. This typically happens when a non-SQL command is submitted directly to Oracle, a statement contains a fundamental structural error, or a PL/SQL-only construct is used in a pure SQL context.

Common Causes

1. Non-SQL Commands Submitted to Oracle

• SQL*Plus commands (e.g., DESCRIBE, SET, SHOW) passed via JDBC or OCI
• Operating system commands submitted as SQL
• Attempting to execute host commands without the proper call context

2. PL/SQL Constructs in SQL Context

• Using BEGIN...END blocks where only SQL is accepted
• Anonymous PL/SQL blocks submitted through a SQL-only interface
• Stored procedure calls using EXEC keyword outside SQL*Plus

3. Misspelled or Unsupported DML/DDL Keywords

• Typos in statement-opening keywords (SELCT, INSRT, UPDAET)
• Using proprietary syntax from another database vendor (MySQL, T-SQL)
• Submitting empty strings or whitespace-only statements

4. Driver or Framework Issues

• ORM frameworks generating invalid statement fragments
• JDBC drivers receiving statements with leading/trailing illegal characters
• Bind variable placeholders left unresolved in the statement text

Diagnostic Queries

Check Recent Invalid SQL in Shared Pool

-- Find recent SQL statements flagged as parse errors
SELECT
  s.sql_id,
  s.parse_calls,
  s.executions,
  s.sql_text
FROM v$sql s
WHERE s.parse_calls > 0
  AND s.executions = 0
  AND s.last_active_time > SYSDATE - 1/24
ORDER BY s.last_active_time DESC
FETCH FIRST 20 ROWS ONLY;

Identify Sessions Generating Errors

-- Sessions currently experiencing parse errors
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.module,
  s.action,
  s.sql_id
FROM v$session s
WHERE s.status = 'ACTIVE'
  AND s.username IS NOT NULL
ORDER BY s.last_call_et DESC;

Review Audit Trail for Bad Statements

-- Check unified audit trail for ORA-00900 occurrences
SELECT
  event_timestamp,
  db_user_name,
  os_user,
  userhost,
  sql_text,
  return_code
FROM unified_audit_trail
WHERE return_code = 900
  AND event_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
ORDER BY event_timestamp DESC;

Check Application Error Logs via Alert Log

-- Review recent errors in the alert log (Oracle 11g+)
SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-00900%'
  AND originating_timestamp > SYSTIMESTAMP - INTERVAL '24' HOUR
ORDER BY originating_timestamp DESC;

Step-by-Step Resolution

1. Identify the Exact Statement Causing the Error

Enable SQL tracing for the offending session to capture the exact text:

-- Enable trace for a specific session
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 42, serial_num => 1234, waits => TRUE);

-- Or enable for the current session
ALTER SESSION SET SQL_TRACE = TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'ora900_debug';

-- After reproducing the error, format the trace file
-- tkprof tracefile.trc output.txt sys=no

2. Fix SQL*Plus Commands Sent via JDBC/OCI

SQL*Plus-specific commands cannot be sent through programmatic interfaces:

-- WRONG: DESCRIBE is a SQL*Plus command, not valid SQL
-- DESCRIBE employees;  -- Fails with ORA-00900 via JDBC

-- CORRECT: Use the data dictionary instead
SELECT
  column_name,
  data_type,
  data_length,
  nullable
FROM all_tab_columns
WHERE table_name = 'EMPLOYEES'
  AND owner = 'HR'
ORDER BY column_id;

3. Fix PL/SQL Blocks Submitted Without Terminator

Anonymous PL/SQL blocks require a trailing / in SQL*Plus or must be wrapped for JDBC:

-- WRONG via SQL*Plus (missing terminator)
-- BEGIN
--   DBMS_OUTPUT.PUT_LINE('Hello');
-- END;

-- CORRECT in SQL*Plus
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello');
END;
/

-- CORRECT via JDBC: use CallableStatement for stored procedures
-- cs = con.prepareCall("{ call my_procedure(?) }");

4. Fix Misspelled Keywords

-- WRONG
-- SELCT employee_id, last_name FORM employees;

-- CORRECT
SELECT employee_id, last_name FROM employees;

5. Replace Vendor-Specific Syntax

-- WRONG: MySQL-style LIMIT clause
-- SELECT * FROM employees LIMIT 10;

-- CORRECT: Oracle syntax
SELECT * FROM employees
FETCH FIRST 10 ROWS ONLY;

-- Also valid in older Oracle versions
SELECT * FROM (
  SELECT * FROM employees
) WHERE ROWNUM <= 10;

6. Handle Empty or Null Statements in Application Code

// Java example: guard against empty statements
String sql = getSqlFromConfig();
if (sql != null && !sql.trim().isEmpty()) {
    stmt = conn.prepareStatement(sql);
    stmt.execute();
}

Prevention Strategies

1. Validate SQL Before Execution

-- Use DBMS_SQL.PARSE to validate without executing
DECLARE
  v_cursor  INTEGER;
  v_sql     VARCHAR2(4000) := 'SELECT * FROM employees WHERE department_id = :1';
BEGIN
  v_cursor := DBMS_SQL.OPEN_CURSOR;
  BEGIN
    DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
    DBMS_OUTPUT.PUT_LINE('SQL is valid');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Invalid SQL: ' || SQLERRM);
  END;
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/

2. Enforce Statement Whitelisting in Applications

• Accept only specific statement types (SELECT, INSERT, UPDATE, DELETE)
• Reject statements containing SQL*Plus metacommands before submission
• Use an ORM or query builder rather than raw string concatenation

3. Use Standardized SQL Patterns

-- Encapsulate repeated query patterns in views or stored procedures
CREATE OR REPLACE VIEW v_active_employees AS
SELECT employee_id, last_name, department_id, salary
FROM employees
WHERE hire_date IS NOT NULL;

-- Application calls the view; no risk of invalid DDL keywords
SELECT * FROM v_active_employees WHERE department_id = 50;

4. Enable Auditing for Parse Failures

-- Audit failed SQL executions
AUDIT ALL STATEMENTS WHENEVER NOT SUCCESSFUL;

-- Review with:
SELECT db_user_name, sql_text, return_code, event_timestamp
FROM unified_audit_trail
WHERE return_code = 900
ORDER BY event_timestamp DESC;

Related Errors

• ORA-00907 - Missing right parenthesis
• ORA-00906 - Missing left parenthesis
• ORA-00933 - SQL command not properly ended
• ORA-00904 - Invalid identifier

Emergency Response

Quick Fixes

1. Identify the bad statement immediately

   SELECT sql_text FROM v$sql
   WHERE last_active_time = (SELECT MAX(last_active_time) FROM v$sql)
   FETCH FIRST 1 ROWS ONLY;

2. Temporarily enable extended SQL tracing on suspect session

   EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(
     session_id  => :sid,
     serial_num  => :serial,
     waits       => TRUE,
     binds       => TRUE
   );

3. Roll back any partial transaction from the session

   -- If a partial DML preceded the bad statement
   ROLLBACK;

Post-Resolution Cleanup

-- Disable tracing once root cause identified
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => :sid, serial_num => :serial);

-- Remove unnecessary audit policy if created for diagnosis
NOAUDIT ALL STATEMENTS;

-- Flush shared pool only if corrupted cursors suspected
ALTER SYSTEM FLUSH SHARED_POOL;