DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00926 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00926: Missing VALUES Keyword in Oracle

ORA-00926 is a syntax error that Oracle throws when the VALUES keyword is missing or misplaced in an INSERT statement. The Oracle parser strictly validates SQL syntax and immediately raises this error when it cannot find the expected VALUES keyword in the correct position. This is one of the most common beginner mistakes, especially when migrating SQL scripts from other databases like MySQL or PostgreSQL to Oracle.

Top 3 Causes

1. VALUES Keyword Completely Omitted

The most straightforward cause — the developer simply forgets to type VALUES between the column list and the data values.

-- WRONG: Missing VALUES keyword
INSERT INTO employees (employee_id, first_name, salary)
(1001, 'John', 5000);

-- CORRECT: VALUES keyword properly placed
INSERT INTO employees (employee_id, first_name, salary)
VALUES (1001, 'John', 5000);
Enter fullscreen mode Exit fullscreen mode

A typo like VAUES or VLAUES also triggers this error:

-- WRONG: Typo in keyword
INSERT INTO employees (employee_id, salary)
VAUES (1001, 5000);

-- CORRECT
INSERT INTO employees (employee_id, salary)
VALUES (1001, 5000);
Enter fullscreen mode Exit fullscreen mode

2. Unclosed Parenthesis in Column List

If the column list parenthesis is not properly closed, Oracle cannot locate the VALUES keyword and raises ORA-00926.

-- WRONG: Missing closing parenthesis on column list
INSERT INTO employees (employee_id, first_name, last_name
VALUES (1001, 'John', 'Doe');

-- CORRECT: All parentheses properly closed
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (1001, 'John', 'Doe');
Enter fullscreen mode Exit fullscreen mode

3. Mixing INSERT...VALUES and INSERT...SELECT Syntax

Oracle supports two INSERT forms, and mixing them incorrectly triggers ORA-00926.

-- WRONG: Cannot use SELECT inside VALUES like this
INSERT INTO employees (employee_id, first_name, salary)
VALUES (SELECT employee_id, first_name, salary FROM temp_emp WHERE dept_id = 10);

-- CORRECT: Use INSERT...SELECT without VALUES
INSERT INTO employees (employee_id, first_name, salary)
SELECT employee_id, first_name, salary
FROM temp_emp
WHERE dept_id = 10;

-- CORRECT: Scalar subquery inside VALUES is allowed
INSERT INTO salary_log (employee_id, avg_salary, log_date)
VALUES (
    1001,
    (SELECT AVG(salary) FROM employees WHERE department_id = 10),
    SYSDATE
);
Enter fullscreen mode Exit fullscreen mode

For inserting multiple rows in Oracle, use INSERT ALL:

-- CORRECT: Oracle multi-row insert
INSERT ALL
    INTO employees (employee_id, first_name, salary) VALUES (1001, 'John', 5000)
    INTO employees (employee_id, first_name, salary) VALUES (1002, 'Jane', 6000)
SELECT 1 FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  • ✅ Confirm VALUES is spelled correctly and present in the statement
  • ✅ Ensure the column list parentheses are fully closed before VALUES
  • ✅ Do not place a SELECT statement directly inside VALUES ()
  • ✅ Use INSERT ALL ... SELECT 1 FROM DUAL for multi-row inserts in Oracle
  • ✅ Use scalar subqueries inside VALUES only for single-value lookups

Prevention Tips

1. Use an IDE with SQL Syntax Highlighting
Tools like SQL Developer or Toad highlight keywords in real time, making it easy to spot a missing or misspelled VALUES keyword before execution. Always validate SQL syntax within the IDE before running scripts in production.

2. Use Bind Variables and Prepared Statements
Adopting bind variables reduces manual typing errors and improves both security and performance:

-- Safe INSERT pattern using bind variables in PL/SQL
BEGIN
    INSERT INTO employees (employee_id, first_name, salary)
    VALUES (:emp_id, :first_name, :salary);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
Enter fullscreen mode Exit fullscreen mode

Related Oracle Errors

Error Code Description
ORA-00917 Missing comma in column or values list
ORA-00907 Missing right parenthesis
ORA-00928 Missing SELECT keyword (opposite of ORA-00926)
ORA-00936 Missing expression inside VALUES clause

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