DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01405 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01405: Fetched Column Value is NULL — Causes, Fixes & Prevention

ORA-01405 occurs in embedded SQL environments such as Pro*C, Pro*COBOL, or OCI-based applications when a FETCH operation retrieves a NULL value from the database into a host variable that has no associated indicator variable declared. Unlike SQL*Plus, where NULL is handled transparently, host-language programs require explicit NULL handling through indicator variables or query-level NULL substitution. Ignoring this requirement causes Oracle to raise ORA-01405 at runtime.

Top 3 Causes

1. Missing Indicator Variable in Pro*C / OCI Programs

The most common cause. When fetching a potentially nullable column into a host variable without a paired indicator variable, Oracle cannot signal the NULL condition and raises ORA-01405.

-- Problematic FETCH (no indicator variable for SALARY)
EXEC SQL FETCH emp_cursor
    INTO :emp_name,
         :salary;   -- ORA-01405 if SALARY is NULL

-- Fixed FETCH (with indicator variables)
EXEC SQL FETCH emp_cursor
    INTO :emp_name :emp_name_ind,
         :salary   :salary_ind;   -- salary_ind == -1 means NULL
-- After fetch: if (salary_ind == -1) salary = 0;
Enter fullscreen mode Exit fullscreen mode

2. Aggregate Functions or Scalar Subqueries Returning NULL

Aggregate functions like SUM() or AVG() return NULL when no rows match. Scalar subqueries return NULL when no rows exist. Fetching these results without NULL handling triggers ORA-01405.

-- Problematic: SUM returns NULL when no matching rows exist
SELECT NVL(SUM(SALARY), 0)        AS TOTAL_SALARY,
       NVL(AVG(COMMISSION_PCT), 0) AS AVG_COMMISSION
  FROM EMPLOYEES
 WHERE DEPARTMENT_ID = 999;

-- Problematic scalar subquery returning NULL
SELECT EMPLOYEE_ID,
       NVL(
           (SELECT SUM(AMOUNT)
              FROM SALES
             WHERE EMP_ID = E.EMPLOYEE_ID),
           0
       ) AS TOTAL_SALES
  FROM EMPLOYEES E;
Enter fullscreen mode Exit fullscreen mode

3. OUTER JOIN Producing NULL in Non-Key Columns

LEFT or RIGHT OUTER JOINs produce NULL for columns from the non-driving table when no matching row exists. Fetching these unmatched columns without NVL or indicator variables causes ORA-01405.

-- Problematic: DEPARTMENT_NAME is NULL for employees with no department
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       NVL(D.DEPARTMENT_NAME, 'UNASSIGNED') AS DEPT_NAME,
       NVL(D.LOCATION_ID, 0)                AS LOC_ID
  FROM EMPLOYEES E
  LEFT OUTER JOIN DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Option A – Use NVL or COALESCE directly in SQL (simplest fix):

SELECT EMPLOYEE_ID,
       COALESCE(FIRST_NAME, LAST_NAME, 'UNKNOWN') AS EMP_NAME,
       NVL(SALARY, 0)                             AS SALARY,
       NVL(COMMISSION_PCT, 0)                     AS COMMISSION
  FROM EMPLOYEES
 WHERE DEPARTMENT_ID = 80;
Enter fullscreen mode Exit fullscreen mode

Option B – Filter NULLs before fetching:

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
  FROM EMPLOYEES
 WHERE SALARY IS NOT NULL
   AND DEPARTMENT_ID = 80;
Enter fullscreen mode Exit fullscreen mode

Option C – Audit NULL counts before application deployment:

SELECT COUNT(*)          AS TOTAL_ROWS,
       COUNT(SALARY)     AS NON_NULL_SALARY,
       COUNT(*) - COUNT(SALARY) AS NULL_SALARY_CNT
  FROM EMPLOYEES;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Always declare indicator variables for every host variable in Pro*C/OCI code, even for columns you believe are NOT NULL today — data requirements change over time.

  2. Enforce NVL/COALESCE as a code review standard for all SELECT statements involving aggregate functions, OUTER JOINs, or scalar subqueries. Add this check to your team's pull request checklist to catch NULL-unsafe queries before they reach production.

Related Errors

Error Code Description
ORA-01403 No data found — also common in cursor FETCH blocks
ORA-01400 Cannot insert NULL into NOT NULL column
ORA-06502 PL/SQL value error — assigning NULL to a NOT NULL variable
ORA-01422 Exact fetch returns more than requested rows

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