ORA-01747: Invalid Column Specification - Fix SQL Errors

ORA-01747: Invalid user.table.column Specification

Error Overview

Error Text: ORA-01747: invalid user.table.column, table.column, or column specification

The ORA-01747 error occurs when Oracle cannot parse a column reference in a SQL statement. The column specification syntax is malformed — most often because a column name conflicts with an Oracle reserved word, the dotted-notation path is incorrect, a quoted identifier is improperly formed, or an UPDATE SET clause contains a structural error. This error is purely a parse-time failure and causes no data modification.

Common Causes

1. Column Name Is an Oracle Reserved Word

• Column named DATE, LEVEL, ROWNUM, COMMENT, NUMBER, or other keywords
• Application generates dynamic SQL with column names that clash with Oracle syntax
• Schema migrated from another RDBMS where keywords are not reserved

2. Incorrect Dotted-Notation Path

• Four-part or deeper notation such as schema.table.column.extra is not valid
• Missing or extra dot in qualified column references
• Database link notation confused with column notation

3. Malformed UPDATE SET Clause

• Syntax error in the SET list of an UPDATE statement
• Missing comma between column assignments
• Using = on the wrong side or inside a subquery incorrectly

4. Improperly Quoted Identifiers

• Mixed or unmatched quote characters around column names
• Double-quote characters inside a single-quoted string literal
• Identifier case sensitivity issues when quotes are omitted

5. Dynamic SQL Generation Errors

• Application code building SQL strings with incorrect column lists
• ORM or report tool emitting non-standard column qualifications
• Concatenation error leaving an extra dot or colon in the statement

Diagnostic Queries

Identify Reserved Word Conflicts

-- Check if a column name is an Oracle reserved word
SELECT keyword, reserved
FROM v$reserved_words
WHERE keyword = UPPER('&column_name')
ORDER BY keyword;

-- List all reserved and non-reserved keywords
SELECT keyword, reserved, res_type, res_attr, res_semi
FROM v$reserved_words
WHERE reserved = 'Y'
ORDER BY keyword;

Inspect Column Names in a Table

-- Check actual column names for a table
SELECT
  column_name,
  data_type,
  nullable,
  column_id
FROM user_tab_columns
WHERE table_name = UPPER('&table_name')
ORDER BY column_id;

-- Find columns whose names match reserved words
SELECT
  t.owner,
  t.table_name,
  t.column_name,
  r.keyword,
  r.reserved
FROM dba_tab_columns t
JOIN v$reserved_words r ON r.keyword = t.column_name
WHERE t.owner = USER
  AND r.reserved = 'Y'
ORDER BY t.table_name, t.column_id;

Review Recent Parse Failures

-- Find statements that failed recently in shared pool
SELECT
  sql_id,
  parse_calls,
  executions,
  sql_text
FROM v$sql
WHERE sql_text LIKE '%ORA-01747%'
   OR (parse_calls > 0 AND executions = 0)
ORDER BY last_active_time DESC
FETCH FIRST 20 ROWS ONLY;

-- Check current session errors
SELECT *
FROM v$session
WHERE sid = SYS_CONTEXT('USERENV', 'SID');

Step-by-Step Resolution

1. Quote Reserved-Word Column Names

When a column name is a reserved word, always use double quotes around it in SQL:

-- If column is named DATE (a reserved word)
-- BAD:
SELECT id, DATE FROM my_table;

-- GOOD: quote the reserved word column name
SELECT id, "DATE" FROM my_table;

-- BAD UPDATE:
UPDATE my_table SET DATE = SYSDATE WHERE id = 1;

-- GOOD UPDATE:
UPDATE my_table SET "DATE" = SYSDATE WHERE id = 1;

-- Verify it works in a simple query first
SELECT "DATE", "COMMENT", "LEVEL"
FROM my_table
WHERE ROWNUM <= 5;

2. Fix Dotted-Notation Errors

Oracle supports at most three-part notation: schema.table.column.

-- BAD: four-part or incorrect notation
SELECT t.schema.table.column FROM my_table t;

-- GOOD: correct alias and column reference
SELECT t.column_name
FROM schema_name.my_table t;

-- GOOD: fully qualified without alias
SELECT my_table.column_name
FROM my_table
WHERE my_table.id = 1;

-- GOOD: schema-qualified
SELECT s.column_name
FROM schema_name.my_table s
WHERE s.id = 1;

3. Fix UPDATE SET Syntax

-- BAD: missing comma between assignments
UPDATE employees
SET first_name = 'John'
    last_name = 'Smith'   -- Missing comma causes ORA-01747
WHERE employee_id = 100;

-- GOOD: proper comma-separated assignment list
UPDATE employees
SET first_name = 'John',
    last_name  = 'Smith'
WHERE employee_id = 100;

-- BAD: reversed assignment in SET
UPDATE employees
SET 'John' = first_name
WHERE employee_id = 100;

-- GOOD: column on left, value on right
UPDATE employees
SET first_name = 'John'
WHERE employee_id = 100;

4. Rename Reserved-Word Columns (Long-Term Fix)

When you control the schema, rename offending columns to non-reserved names:

-- Rename a column that clashes with a reserved word
ALTER TABLE my_table RENAME COLUMN "DATE" TO created_date;
ALTER TABLE my_table RENAME COLUMN "COMMENT" TO remarks;
ALTER TABLE my_table RENAME COLUMN "LEVEL" TO hierarchy_level;

-- Update dependent views and code after renaming
-- Recompile any invalid objects
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => USER, compile_all => FALSE);

-- Check for invalid objects after rename
SELECT object_name, object_type, status
FROM user_objects
WHERE status = 'INVALID'
ORDER BY object_type, object_name;

5. Fix Dynamic SQL Column Lists

-- BAD: dynamic SQL with unquoted reserved-word column
DECLARE
  v_sql VARCHAR2(4000);
  v_col VARCHAR2(128) := 'DATE';
BEGIN
  v_sql := 'SELECT ' || v_col || ' FROM my_table';
  EXECUTE IMMEDIATE v_sql;  -- Will raise ORA-01747
END;
/

-- GOOD: check and quote reserved words in dynamic SQL
CREATE OR REPLACE FUNCTION safe_column_ref(p_col VARCHAR2) RETURN VARCHAR2 AS
  v_reserved NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_reserved
  FROM v$reserved_words
  WHERE keyword = UPPER(p_col) AND reserved = 'Y';

  IF v_reserved > 0 THEN
    RETURN '"' || p_col || '"';
  ELSE
    RETURN p_col;
  END IF;
END;
/

DECLARE
  v_sql VARCHAR2(4000);
BEGIN
  v_sql := 'SELECT ' || safe_column_ref('DATE') || ' FROM my_table';
  EXECUTE IMMEDIATE v_sql;
END;
/

Prevention Strategies

1. Enforce Naming Standards

-- Trigger to prevent reserved-word column names at DDL time (12c+)
-- Alternatively, use a naming convention audit query
SELECT
  c.table_name,
  c.column_name,
  r.keyword
FROM user_tab_columns c
JOIN v$reserved_words r
  ON r.keyword = c.column_name AND r.reserved = 'Y'
ORDER BY c.table_name;

2. Code Review Checklist for SQL

• Always test generated SQL statements in SQL*Plus or SQL Developer before deploying
• Enable SQL parsing in application test suites to catch ORA-01747 before production
• Avoid column names that match any entry in V$RESERVED_WORDS

3. Static Analysis Tools

• Use Oracle SQL Developer’s code insight to flag reserved words used as identifiers
• Add a pre-commit hook that scans DDL files for reserved-word column names
• Review ORM entity definitions when mapping to legacy schemas with keyword columns

4. Character Set and Identifier Guidelines

-- Confirm identifier character set rules for the database
SELECT *
FROM nls_database_parameters
WHERE parameter = 'NLS_CHARACTERSET';

-- Use DBMS_ASSERT to validate identifiers in dynamic SQL
DECLARE
  v_safe_col VARCHAR2(128);
BEGIN
  v_safe_col := DBMS_ASSERT.SIMPLE_SQL_NAME('my_column');
  -- Raises ORA-44003 if not a valid simple SQL name
END;
/

Related Errors

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