DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00911 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00911: Invalid Character — Causes, Fixes & Prevention

ORA-00911 is one of Oracle's most commonly encountered errors, triggered when a SQL statement contains a character that Oracle's SQL parser does not recognize as valid. This typically happens when illegal characters such as semicolons, invisible Unicode characters, or full-width special characters are present in the SQL string. Understanding the root cause quickly can save significant debugging time in production environments.

Top 3 Causes & SQL Examples

1. Trailing Semicolon in Application Code

The most frequent cause of ORA-00911 is including a semicolon (;) at the end of a SQL statement when executing it through a database driver such as JDBC, cx_Oracle, or ODP.NET. While SQL*Plus and SQL Developer use the semicolon as a statement terminator, database drivers treat it as part of the SQL string itself, causing the parser to fail.

Incorrect (causes ORA-00911):

-- This will fail when executed via JDBC or cx_Oracle
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id = 50;
Enter fullscreen mode Exit fullscreen mode

Correct (remove the semicolon):

-- No semicolon at the end when using a DB driver
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id = 50
Enter fullscreen mode Exit fullscreen mode

Note: PL/SQL blocks (BEGIN...END;) are an exception and may require semicolons depending on the driver. Always check your specific driver documentation.

2. Invisible or Non-ASCII Characters in SQL

When SQL is copied from word processors, web browsers, email clients, or markdown editors, invisible Unicode characters (e.g., non-breaking space \u00A0, line separator \u2028, or BOM characters) can silently embed themselves into the SQL string. These characters are invisible to the human eye but are immediately flagged by Oracle's parser.

Detecting hidden characters using DUMP:

-- Use DUMP to inspect character codes in a string
SELECT DUMP('SELECT * FROM dual', 16) AS char_codes
FROM dual;

-- Remove non-printable and non-ASCII characters using REGEXP_REPLACE
SELECT REGEXP_REPLACE(
           :sql_input,
           '[^\x09\x0A\x0D\x20-\x7E]',  -- Keep tab, newline, CR, and printable ASCII
           ''
       ) AS cleaned_sql
FROM dual;
Enter fullscreen mode Exit fullscreen mode

Always paste SQL into a plain text editor (e.g., Notepad, VSCode with plain text mode) to strip hidden formatting before using it in your application.

3. Full-Width or Invalid Special Characters

In environments where input method editors (IMEs) are used — particularly for East Asian languages — it is easy to accidentally insert full-width characters (e.g., ， instead of ,, or " instead of ") into SQL statements. Oracle's parser does not accept these characters and immediately raises ORA-00911.

Incorrect (full-width comma — causes ORA-00911):

-- Full-width comma and quotation marks will break parsing
SELECT employee_id，first_name，last_name
FROM employees;
Enter fullscreen mode Exit fullscreen mode

Correct (standard ASCII punctuation):

-- Use standard half-width ASCII characters only
SELECT employee_id, first_name, last_name
FROM employees;

-- Column aliases with spaces must use standard double quotes
SELECT
    employee_id  AS "Employee ID",
    first_name   AS "First Name",
    hire_date    AS "Hire Date"
FROM employees
WHERE ROWNUM <= 5;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Symptom Fix
SQL works in SQL*Plus but fails in app Remove trailing semicolon
SQL copied from browser/email fails Strip non-ASCII chars with REGEXP_REPLACE
Error after typing SQL with IME enabled Replace all punctuation with ASCII equivalents

A simple PL/SQL utility to sanitize SQL strings before execution:

CREATE OR REPLACE FUNCTION clean_sql(p_sql IN VARCHAR2)
RETURN VARCHAR2
IS
    v_sql VARCHAR2(32767);
BEGIN
    v_sql := TRIM(p_sql);
    -- Remove trailing semicolon
    IF SUBSTR(v_sql, -1) = ';' THEN
        v_sql := SUBSTR(v_sql, 1, LENGTH(v_sql) - 1);
    END IF;
    -- Strip non-ASCII characters (preserve tab, newline, carriage return)
    v_sql := REGEXP_REPLACE(v_sql, '[^\x09\x0A\x0D\x20-\x7E]', '');
    RETURN TRIM(v_sql);
END clean_sql;
/

-- Test the function
SELECT clean_sql('SELECT * FROM employees;') AS result FROM dual;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Enforce a No-Semicolon Policy in Application SQL
Establish a team coding standard that prohibits trailing semicolons in SQL strings passed to database drivers. Integrate a SQL linter (e.g., SQLFluff) into your CI/CD pipeline to automatically catch these issues before deployment.

2. Always Use Plain-Text Editors for SQL Authoring
Never write or paste SQL directly from rich-text sources. Use editors configured to save files as UTF-8 without BOM, and leverage the "Paste as Plain Text" option (Ctrl+Shift+V) to avoid embedding invisible formatting characters.

Related Oracle Errors

  • ORA-00900 — Invalid SQL statement (general syntax failure)
  • ORA-01756 — Quoted string not properly terminated (mismatched quotes)
  • ORA-00907 — Missing right parenthesis (bracket mismatch)
  • ORA-00936 — Missing expression (incomplete SQL structure)

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