DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01747 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01747: Invalid User.Table.Column Specification — Causes and Fixes

ORA-01747 is thrown by Oracle when a column specification in a SQL statement is syntactically invalid, most commonly in the SET clause of an UPDATE statement or within an INSERT statement. Oracle's SQL parser cannot resolve the column reference and raises this error, halting execution immediately. Understanding the root cause is straightforward once you know what Oracle's parser expects.

Top 3 Causes and Fixes

Cause 1: Using a Table Alias or Table Name in the UPDATE SET Clause

This is the most frequent cause. Unlike SELECT or WHERE, Oracle's UPDATE ... SET clause does not allow prefixing column names with a table name or alias.

Incorrect:

-- ORA-01747: alias not allowed in SET clause
UPDATE employees e
SET e.salary = e.salary * 1.1,
    e.last_modified = SYSDATE
WHERE e.department_id = 10;
Enter fullscreen mode Exit fullscreen mode

Correct:

-- Remove alias from SET clause only
UPDATE employees e
SET salary = salary * 1.1,
    last_modified = SYSDATE
WHERE e.department_id = 10;
Enter fullscreen mode Exit fullscreen mode

The fix is simple: remove the table name or alias prefix from every column assignment in the SET clause. You can still use the alias freely in the WHERE clause.

Cause 2: Using an Oracle Reserved Word as a Column Name

Oracle has hundreds of reserved words (DATE, LEVEL, COMMENT, NUMBER, SELECT, etc.). If any of these are used as column names without double-quote delimiters, the parser misreads them as SQL keywords and raises ORA-01747.

Check if a word is reserved:

SELECT keyword, reserved
FROM v$reserved_words
WHERE keyword IN ('DATE', 'COMMENT', 'LEVEL', 'NUMBER')
ORDER BY keyword;
Enter fullscreen mode Exit fullscreen mode

Incorrect:

-- ORA-01747: COMMENT is a reserved word
UPDATE order_info
SET COMMENT = 'Shipped',
    DATE = SYSDATE
WHERE order_id = 1001;
Enter fullscreen mode Exit fullscreen mode

Correct:

-- Wrap reserved-word column names in double quotes
UPDATE order_info
SET "COMMENT" = 'Shipped',
    "DATE"    = SYSDATE
WHERE order_id = 1001;
Enter fullscreen mode Exit fullscreen mode

Note: Double-quoted identifiers are case-sensitive in Oracle. Always use the exact case that was used at table creation time.

Cause 3: Malformed Column Specification in Dynamic SQL

When building SQL strings programmatically, a missing space, extra dot, or invalid character in the column name can produce ORA-01747 at runtime.

Problematic dynamic SQL:

DECLARE
    v_sql VARCHAR2(1000);
BEGIN
    -- Bug: extra dot before column name
    v_sql := 'UPDATE employees SET .salary = 7000 WHERE employee_id = 100';
    EXECUTE IMMEDIATE v_sql;  -- ORA-01747
END;
/
Enter fullscreen mode Exit fullscreen mode

Corrected version with input validation:

DECLARE
    v_sql      VARCHAR2(1000);
    v_col      VARCHAR2(128) := 'salary';
    v_val      NUMBER        := 7000;
    v_emp_id   NUMBER        := 100;
BEGIN
    -- Validate column name format before building SQL
    IF NOT REGEXP_LIKE(v_col, '^[A-Za-z][A-Za-z0-9_$#]*$') THEN
        RAISE_APPLICATION_ERROR(-20001, 'Invalid column name: ' || v_col);
    END IF;

    v_sql := 'UPDATE employees SET ' || v_col || ' = :1 WHERE employee_id = :2';
    EXECUTE IMMEDIATE v_sql USING v_val, v_emp_id;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Update successful.');
END;
/
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  1. Scan your SET clause — remove any tablename. or alias. prefix from column assignments.
  2. Check for reserved words — query v$reserved_words and wrap offending column names in double quotes (").
  3. Review dynamic SQL — print or log the generated SQL string before executing it to spot malformed column references.
  4. Test in a non-production environment first — always validate DML against a dev or staging schema.

Prevention Tips

  • Adopt a naming convention that avoids Oracle reserved words entirely. Prefer descriptive suffixes like ORDER_DATE instead of DATE, or ITEM_COMMENT instead of COMMENT. This eliminates the double-quote requirement and makes SQL cleaner.
  • Use a SQL-aware IDE (SQL Developer, Toad, DBeaver) with syntax highlighting and real-time error detection. These tools flag incorrect SET clause syntax and reserved-word conflicts before you even run the query, catching ORA-01747 at development time rather than in production.

Related Oracle Errors

Error Code Description
ORA-00904 invalid identifier — column or object name does not exist
ORA-00936 missing expression — expression missing in SET or WHERE clause
ORA-01745 invalid host/bind variable name — malformed bind variable in dynamic SQL

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