【Oracle】ORA-01006の原因と解決方法｜bind variable does not exist・バインド変数が存在しない時の直し方

-- SQLにあるのは :dept_id だけ
SELECT employee_id, employee_name
FROM employees
WHERE department_id = :dept_id;

-- NG: 実行側で :department_id を渡していると対応先がない
-- bind: department_id = 10

SELECT employee_id, employee_name
FROM employees
WHERE department_id = :dept_id;

-- OK: SQLに存在する :dept_id に値を渡す
-- bind: dept_id = 10

DECLARE
  v_sql   VARCHAR2(4000) := 'SELECT * FROM employees WHERE 1 = 1';
  v_name  VARCHAR2(100) := NULL;
  v_count NUMBER;
BEGIN
  IF v_name IS NOT NULL THEN
    v_sql := v_sql || ' AND employee_name LIKE :name';
  END IF;

  -- NG: v_name がNULLならSQLに :name が存在しない
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (' || v_sql || ')'
    INTO v_count
    USING v_name;
END;
/

DECLARE
  v_sql   VARCHAR2(4000) := 'SELECT COUNT(*) FROM employees WHERE 1 = 1';
  v_name  VARCHAR2(100) := NULL;
  v_count NUMBER;
BEGIN
  IF v_name IS NOT NULL THEN
    v_sql := v_sql || ' AND employee_name LIKE :name';
    EXECUTE IMMEDIATE v_sql INTO v_count USING '%' || v_name || '%';
  ELSE
    EXECUTE IMMEDIATE v_sql INTO v_count;
  END IF;
END;
/

DECLARE
  v_sql  VARCHAR2(4000);
  v_dept NUMBER := 10;
  v_cnt  NUMBER;
BEGIN
  v_sql := 'SELECT COUNT(*)
            FROM employees
            WHERE department_id = :x
               OR manager_id = :x';

  -- SQL文では :x が2回出ているので、値も2回渡す
  EXECUTE IMMEDIATE v_sql INTO v_cnt USING v_dept, v_dept;
END;
/

DECLARE
  v_block VARCHAR2(4000);
  v_dept  NUMBER := 10;
BEGIN
  v_block := 'BEGIN update_dept(:x, :x); END;';

  -- PL/SQLブロックでは同じ名前 :x は1つの値に対応する
  EXECUTE IMMEDIATE v_block USING v_dept;
END;
/

DECLARE
  TYPE rc IS REF CURSOR;
  c      rc;
  v_sql  VARCHAR2(4000) := 'SELECT * FROM employees';
  v_dept NUMBER := 10;
BEGIN
  -- NG: SQLに :dept_id がないのに v_dept を渡している
  OPEN c FOR v_sql USING v_dept;
END;
/

DECLARE
  TYPE rc IS REF CURSOR;
  c      rc;
  v_sql  VARCHAR2(4000) := 'SELECT * FROM employees WHERE department_id = :dept_id';
  v_dept NUMBER := 10;
BEGIN
  OPEN c FOR v_sql USING v_dept;
END;
/

DECLARE
  c INTEGER;
  n INTEGER;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(
    c,
    'SELECT * FROM employees WHERE department_id = :dept_id',
    DBMS_SQL.NATIVE
  );

  -- NG: SQLには :department_id が存在しない
  DBMS_SQL.BIND_VARIABLE(c, ':department_id', 10);
  n := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.CLOSE_CURSOR(c);
END;
/

DBMS_SQL.BIND_VARIABLE(c, ':dept_id', 10);

sql = """
SELECT employee_id, employee_name
FROM employees
WHERE department_id = :dept_id
"""

# NG: SQLにない department_id を渡している
cursor.execute(sql, {"department_id": 10})

# OK
cursor.execute(sql, {"dept_id": 10})

String sql = "SELECT employee_id FROM employees WHERE department_id = :dept_id";

// 名前付きパラメータを扱うラッパーを使う場合、SQL側の名前と一致させる
params.put("dept_id", 10);

// department_id のような別名を渡すと、SQL側に対応先がない