Oracle ORA-00996 Error: Causes and Solutions Complete Guide

ORA-00996: The Concatenate Operator is ||, Not |

ORA-00996 is a syntax error thrown by Oracle when a single pipe character (|) is used instead of the correct double pipe (||) string concatenation operator. Oracle's SQL parser strictly requires two consecutive pipe symbols to perform string concatenation, and a single pipe is not a valid operator in Oracle SQL. This error is especially common among developers migrating from other databases or programming languages where | carries different meanings.

---

Top 3 Causes

1. Typing a Single Pipe Instead of Double Pipe

The most frequent cause — a simple typo or unfamiliarity with Oracle's concatenation syntax.

-- WRONG: ORA-00996 triggered
SELECT first_name | ' ' | last_name AS full_name
FROM employees;

-- CORRECT: Use double pipe ||
SELECT first_name || ' ' || last_name AS full_name
FROM employees;

2. Migrating Code from Another DBMS or Language

Developers moving SQL from MySQL, PostgreSQL, or other languages sometimes bring over incompatible syntax habits.

-- WRONG: MySQL-style or other language habit
SELECT product_name | ' $' | unit_price AS label
FROM products;

-- CORRECT: Oracle standard concatenation
SELECT product_name || ' $' || unit_price AS label
FROM products;

-- ALTERNATIVE: Use Oracle's CONCAT() function (two arguments only)
SELECT CONCAT(product_name, ' - Available') AS label
FROM products;

3. Dynamically Built SQL Strings with Wrong Operator

In PL/SQL, dynamically assembled SQL strings can accidentally include a single | if the building logic has a bug.

-- WRONG: Dynamic SQL with incorrect operator
DECLARE
    v_sql VARCHAR2(500);
BEGIN
    v_sql := 'SELECT emp_id | emp_name FROM employees';  -- ORA-00996 at runtime
    EXECUTE IMMEDIATE v_sql;
END;
/

-- CORRECT: Properly constructed dynamic SQL
DECLARE
    v_sql    VARCHAR2(500);
    v_cursor SYS_REFCURSOR;
    v_result VARCHAR2(200);
BEGIN
    v_sql := 'SELECT employee_id || '' - '' || first_name || '' '' || last_name '
          || 'FROM employees WHERE department_id = :1';

    OPEN v_cursor FOR v_sql USING 10;
    LOOP
        FETCH v_cursor INTO v_result;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_result);
    END LOOP;
    CLOSE v_cursor;
END;
/

---

Quick Fix Solutions

Replace every single | used for string concatenation with ||. Here are practical, real-world examples:

-- Combining name columns safely with NULL handling
SELECT NVL(first_name, 'N/A') || ' ' || NVL(last_name, 'N/A') AS full_name
FROM employees;

-- Appending formatted date to a string
SELECT first_name || ' (Hired: ' || TO_CHAR(hire_date, 'YYYY-MM-DD') || ')' AS info
FROM employees
WHERE department_id = 30;

-- Building a full address string
SELECT street_address || ', ' || city || ' ' || postal_code AS address
FROM locations;

---

Prevention Tips

1. Enable syntax highlighting in your IDE.
   Tools like SQL Developer, Toad, or DBeaver visually distinguish | from ||, making typos easy to spot before execution. Integrate a SQL linter (e.g., SQLFluff) into your CI/CD pipeline to catch syntax errors automatically before deployment.

2. Establish and enforce a SQL coding standard.
   Document that || is the only accepted concatenation operator in your team's Oracle SQL guidelines. When migrating code from other platforms, use a migration checklist that explicitly flags string concatenation operator differences, and always run unit tests on all SQL statements post-migration to verify correctness.

---

Related Oracle Errors

• ORA-00907 – Missing right parenthesis; another common SQL syntax error.
• ORA-00917 – Missing comma in column or value lists.
• ORA-00920 – Invalid relational operator; similar category of operator misuse.
• ORA-06550 – PL/SQL compilation error wrapper that often accompanies ORA-00996 inside PL/SQL blocks.

---

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