DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01007 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01007: Variable Not in Select List — Causes, Fixes & Prevention

ORA-01007 occurs in Oracle when a program tries to reference a variable or column that does not exist in the SELECT list of an open cursor. This error is a runtime error, meaning it won't be caught at compile time, making it especially tricky to debug in production environments. It commonly surfaces in PL/SQL, Pro*C, and OCI-based applications during FETCH operations.

Top 3 Causes

1. Mismatch Between SELECT Columns and FETCH INTO Variables

The most frequent cause is when the number of variables in the FETCH INTO clause doesn't match the number of columns in the cursor's SELECT list.

-- PROBLEMATIC CODE
DECLARE
  CURSOR emp_cur IS
    SELECT employee_id, first_name, salary  -- 3 columns
    FROM employees;

  v_id    NUMBER;
  v_name  VARCHAR2(50);
  -- Missing variable for 'salary'!
BEGIN
  OPEN emp_cur;
  FETCH emp_cur INTO v_id, v_name;  -- ORA-01007 raised here
  CLOSE emp_cur;
END;
/

-- FIXED CODE
DECLARE
  CURSOR emp_cur IS
    SELECT employee_id, first_name, salary
    FROM employees;

  v_id     NUMBER;
  v_name   VARCHAR2(50);
  v_salary NUMBER;
BEGIN
  OPEN emp_cur;
  FETCH emp_cur INTO v_id, v_name, v_salary;  -- All 3 columns matched
  CLOSE emp_cur;
END;
/
Enter fullscreen mode Exit fullscreen mode

2. Incorrect DEFINE_COLUMN Usage in DBMS_SQL

When using the DBMS_SQL package for dynamic SQL, every column in the SELECT list must be explicitly defined using DEFINE_COLUMN in the exact same order. Skipping a column or defining extras causes ORA-01007.

-- CORRECT DBMS_SQL usage
DECLARE
  v_cursor  INTEGER;
  v_sql     VARCHAR2(500);
  v_id      NUMBER;
  v_name    VARCHAR2(50);
  v_status  INTEGER;
BEGIN
  v_sql    := 'SELECT employee_id, first_name FROM employees WHERE ROWNUM <= 5';
  v_cursor := DBMS_SQL.OPEN_CURSOR;

  DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);

  -- Define columns in SELECT order — position matters!
  DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_id);        -- employee_id
  DBMS_SQL.DEFINE_COLUMN(v_cursor, 2, v_name, 50);  -- first_name

  v_status := DBMS_SQL.EXECUTE(v_cursor);

  LOOP
    EXIT WHEN DBMS_SQL.FETCH_ROWS(v_cursor) = 0;
    DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_id);
    DBMS_SQL.COLUMN_VALUE(v_cursor, 2, v_name);
    DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/
Enter fullscreen mode Exit fullscreen mode

3. Host Variable Mismatch in Pro*C / OCI Applications

In embedded SQL environments like Pro*C, host variables must correspond precisely to the columns returned by the cursor. When a query is modified at runtime but the host variable structure is not updated accordingly, ORA-01007 is raised.

/* Pro*C example — ensure host variables match SELECT list */
EXEC SQL DECLARE emp_cur CURSOR FOR
    SELECT employee_id, first_name, salary   /* 3 columns */
    FROM employees
    WHERE department_id = :dept_id;

/* Must declare matching host variables */
int    emp_id;
char   emp_name[51];
double emp_salary;   /* Do NOT omit this — causes ORA-01007 */

EXEC SQL OPEN emp_cur;
EXEC SQL FETCH emp_cur INTO :emp_id, :emp_name, :emp_salary;
EXEC SQL CLOSE emp_cur;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  • Always count your SELECT columns and match them exactly with INTO variables.
  • Use %ROWTYPE to automatically align the record structure with the cursor definition — this eliminates manual counting entirely. 
-- Safest approach using %ROWTYPE
DECLARE
  CURSOR emp_cur IS
    SELECT employee_id, first_name, salary
    FROM employees
    WHERE department_id = 10;

  v_rec emp_cur%ROWTYPE;  -- Automatically matches cursor structure
BEGIN
  OPEN emp_cur;
  LOOP
    FETCH emp_cur INTO v_rec;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_rec.employee_id || ' | ' || v_rec.first_name);
  END LOOP;
  CLOSE emp_cur;
END;
/
Enter fullscreen mode Exit fullscreen mode
  • Use cursor FOR loops whenever possible — they eliminate FETCH INTO entirely. 
BEGIN
  FOR r IN (SELECT employee_id, first_name, salary FROM employees) LOOP
    DBMS_OUTPUT.PUT_LINE(r.employee_id || ' - ' || r.salary);
  END LOOP;
END;
/
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Standardize on %ROWTYPE: Make it a team coding standard to use cursor%ROWTYPE or table%ROWTYPE instead of listing individual variables. This makes your code resilient to future changes in the SELECT list.

  2. Add runtime unit tests for dynamic SQL: Dynamic SQL queries are not validated at compile time. Write unit tests that exercise various parameter combinations and integrate them into your CI/CD pipeline to catch ORA-01007 before deployment.

Related Errors

Error Code Description
ORA-01008 Not all variables bound — companion error in dynamic SQL
ORA-01422 Exact fetch returns more rows than requested
ORA-06511 Cursor already open — cursor lifecycle mismanagement
ORA-01009 Missing mandatory parameter — similar context in Pro*C

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