ORA-00911 - Invalid Character

ORA-00911: Invalid Character

The ORA-00911 error occurs when Oracle encounters an invalid or unexpected character in a SQL statement. This error is commonly caused by copy-paste operations, encoding issues, or using incorrect syntax.

Use at Your Own Risk

This documentation is for educational purposes only. Always test solutions in development environments before production use. See our disclaimer for full terms.

Error Details

• Error Code: ORA-00911
• Error Message: “invalid character”
• Error Type: SQL Syntax Error
• Severity: Compilation Error

Common Causes

1. Invalid Characters in SQL

Hidden or invisible characters copied from documents or web pages.

2. Encoding Issues

Character encoding problems when transferring SQL between systems.

3. Special Characters

Using characters that have special meaning in SQL in incorrect contexts.

4. Copy-Paste Artifacts

Non-printable characters introduced during copy-paste operations.

5. Unicode Issues

Problems with Unicode characters in SQL statements.

Examples and Solutions

Example 1: Hidden Characters from Copy-Paste

Problem:

SELECT * FROM employees WHERE department_id = 10; -- Contains hidden character
-- ORA-00911: invalid character

Solution:

-- Retype the statement instead of copy-paste
SELECT * FROM employees WHERE department_id = 10;

-- Or use a text editor to show invisible characters
-- Remove any non-ASCII characters

Example 2: Smart Quotes

Problem:

SELECT 'John's Department' FROM dual;
-- ORA-00911: invalid character (smart quote character)

Solution:

-- Use standard single quotes
SELECT 'John''s Department' FROM dual;

-- Or use double quotes for identifiers
SELECT "John's Department" AS description FROM dual;

Example 3: Wrong Quote Types

Problem:

SELECT "invalid" FROM dual; -- Using wrong quote type for string literal
-- ORA-00911: invalid character

Solution:

-- Use single quotes for string literals
SELECT 'valid' FROM dual;

-- Use double quotes for identifiers only
SELECT column_name AS "Column Name" FROM table_name;

Example 4: Byte Order Mark (BOM)

Problem:

-- BOM character at beginning of file
SELECT * FROM employees;
-- ORA-00911: invalid character

Solution:

-- Remove BOM from file
-- Save file as UTF-8 without BOM
-- Or use text editor to remove BOM

SELECT * FROM employees;

Example 5: Non-ASCII Characters

Problem:

SELECT * FROM employées WHERE département = 'IT';
-- ORA-00911: invalid character (accented characters)

Solution:

-- Use ASCII characters for object names
SELECT * FROM employees WHERE department = 'IT';

-- Or enable proper character set support
-- ALTER DATABASE CHARACTER SET AL32UTF8;
-- (Requires DBA privileges and proper planning)

Diagnostic Queries

Find Character Codes

-- Check ASCII values of characters
SELECT ASCII(SUBSTR('problematic_string', 1, 1)) FROM dual;

-- Convert string to hex to see exact bytes
SELECT RAWTOHEX(UTL_RAW.CAST_TO_RAW('problematic_string')) FROM dual;

Check Database Character Set

-- Check database character set
SELECT parameter, value
FROM nls_database_parameters
WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

-- Check session character set
SELECT parameter, value
FROM nls_session_parameters
WHERE parameter IN ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');

Validate SQL Syntax

-- Use EXPLAIN PLAN to validate syntax without execution
EXPLAIN PLAN FOR
SELECT * FROM employees;

-- Check for parsing errors
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Prevention Strategies

1. Use Proper Text Editors

-- Use editors that show invisible characters
-- Examples: Notepad++, VS Code, vim with :set list
-- Avoid copying from Word documents or web pages

2. Standard Character Validation

-- Create function to validate SQL strings
CREATE OR REPLACE FUNCTION is_valid_sql_char(p_char VARCHAR2)
RETURN BOOLEAN IS
BEGIN
    -- Check if character is valid SQL character
    RETURN ASCII(p_char) BETWEEN 32 AND 126
           OR p_char IN (CHR(9), CHR(10), CHR(13)); -- Tab, LF, CR
END;
/

3. Character Set Consistency

-- Ensure consistent character sets across environment
-- Database: AL32UTF8
-- Client: UTF8
-- Files: UTF-8 without BOM

4. SQL Standards Compliance

-- Follow SQL standards
-- Use single quotes for literals
SELECT 'String literal' FROM dual;

-- Use double quotes for identifiers only when necessary
SELECT employee_id AS "Employee ID" FROM employees;

-- Avoid special characters in object names
CREATE TABLE employee_data (id NUMBER); -- Good
-- CREATE TABLE employee-data (id NUMBER); -- Avoid

Tools for Diagnosis

Hex Dump Analysis

# Linux/Unix - examine file for hidden characters
hexdump -C problematic_file.sql | head -20

# Windows - use PowerShell
Format-Hex problematic_file.sql | Select-Object -First 20

SQL*Plus Settings

-- Show invisible characters in SQL*Plus
SET PAGESIZE 0
SET LINESIZE 1000
SET TRIMSPOOL ON

-- Enable better error reporting
SET SQLBLANKLINES ON

Text Editor Configuration

-- Notepad++: View > Show Symbol > Show All Characters
-- VS Code: View > Render Whitespace
-- Vim: :set list

Resolution Steps

Step 1: Identify Problem Area

-- Comment out sections to isolate the problem
/*
SELECT * FROM employees
WHERE department_id = 10;
*/

-- Add back sections one by one
SELECT * FROM employees;
-- WHERE department_id = 10;

Step 2: Check Character Encoding

-- Verify file encoding in text editor
-- Convert to UTF-8 without BOM if necessary
-- Remove any special formatting

Step 3: Recreate Statement

-- Manually retype the SQL statement
-- Don't copy-paste from other sources
-- Use standard keyboard characters only

Step 4: Validate Syntax

-- Use online SQL validators
-- Test in SQL*Plus or SQL Developer
-- Check Oracle documentation for syntax

Best Practices

File Handling

• Save SQL files as UTF-8 without BOM
• Use plain text editors for SQL development
• Avoid copying from formatted documents

Character Usage

• Use ASCII characters for object names
• Stick to standard SQL syntax
• Avoid smart quotes and special symbols

Development Environment

• Configure editors to show invisible characters
• Use consistent character encoding across tools
• Set up proper Oracle client character set

Code Review

• Review SQL for non-standard characters
• Validate syntax before deployment
• Use version control to track changes

Related Errors

• ORA-00900: Invalid SQL Statement
• ORA-00933: SQL Command Not Properly Ended
• ORA-00936: Missing Expression
• ORA-00904: Invalid Identifier

Summary

ORA-00911 typically results from hidden characters, encoding issues, or incorrect syntax. The key to resolution is identifying and removing invalid characters, ensuring proper encoding, and following standard SQL syntax rules. Prevention involves using appropriate tools and maintaining consistent character encoding practices.