ORA-01008 Not All Variables Bound - Fix Bind Variable Issues

ORA-01008: Not All Variables Bound

Error Overview

Error Text: ORA-01008: not all variables bound

This error occurs when a SQL statement contains bind variable placeholders (like :1, :variable_name) but the application hasn’t provided values for all of them. It’s commonly seen in dynamic SQL, prepared statements, and database APIs when there’s a mismatch between bind variables in the SQL and the values provided.

Understanding Bind Variables

Bind Variable Types

Oracle Bind Variables
├── Positional Binds (:1, :2, :3)
├── Named Binds (:employee_id, :dept_name)
├── PL/SQL Variables (in PL/SQL blocks)
├── Substitution Variables (&variable in SQL*Plus)
└── Host Variables (in Pro*C, Pro*COBOL)

Common Scenarios

• Missing bind values - SQL has more binds than values provided
• Duplicate bind names - Same bind used multiple times
• Dynamic SQL errors - Incorrectly constructed dynamic queries
• API misuse - Wrong binding method for the database driver
• Copy-paste errors - Bind variables from old code not updated

Diagnostic Steps

1. Identify the Problematic SQL Statement

-- Check currently executing SQL with bind variables
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.sql_id,
  q.sql_text,
  s.status,
  s.program
FROM v$session s
LEFT JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.username IS NOT NULL
  AND q.sql_text LIKE '%:%'  -- Contains bind variables
  AND s.status = 'ACTIVE'
ORDER BY s.last_call_et DESC;

-- Check SQL text for specific SQL_ID
SELECT
  sql_id,
  sql_text,
  sql_fulltext,
  executions,
  error_count,
  first_load_time,
  last_load_time
FROM v$sql
WHERE sql_id = 'your_sql_id';  -- Replace with actual SQL_ID

-- Check bind variables for a SQL statement
SELECT
  sql_id,
  name,
  position,
  datatype_string,
  was_captured,
  value_string,
  last_captured
FROM v$sql_bind_capture
WHERE sql_id = 'your_sql_id'  -- Replace with actual SQL_ID
ORDER BY position;

2. Analyze Bind Variable Usage

-- Count bind variables in SQL text
DECLARE
  v_sql_text CLOB := 'SELECT * FROM employees WHERE dept_id = :1 AND salary > :2 AND hire_date > :3';
  v_bind_count NUMBER := 0;
  v_pos NUMBER := 1;
BEGIN
  -- Count positional binds
  WHILE INSTR(v_sql_text, ':', v_pos) > 0 LOOP
    v_pos := INSTR(v_sql_text, ':', v_pos);
    IF v_pos > 0 THEN
      v_bind_count := v_bind_count + 1;
      v_pos := v_pos + 1;
    END IF;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Number of bind variables found: ' || v_bind_count);

  -- List all bind variables
  v_pos := 1;
  WHILE INSTR(v_sql_text, ':', v_pos) > 0 LOOP
    v_pos := INSTR(v_sql_text, ':', v_pos);
    IF v_pos > 0 THEN
      DECLARE
        v_end_pos NUMBER;
        v_bind_name VARCHAR2(100);
      BEGIN
        v_end_pos := LEAST(
          NVL(NULLIF(INSTR(v_sql_text, ' ', v_pos), 0), LENGTH(v_sql_text) + 1),
          NVL(NULLIF(INSTR(v_sql_text, ',', v_pos), 0), LENGTH(v_sql_text) + 1),
          NVL(NULLIF(INSTR(v_sql_text, ')', v_pos), 0), LENGTH(v_sql_text) + 1)
        );
        v_bind_name := SUBSTR(v_sql_text, v_pos, v_end_pos - v_pos);
        DBMS_OUTPUT.PUT_LINE('Bind variable: ' || v_bind_name);
        v_pos := v_pos + LENGTH(v_bind_name);
      END;
    END IF;
  END LOOP;
END;
/

-- Check for duplicate bind names in SQL
WITH bind_analysis AS (
  SELECT
    sql_id,
    sql_text,
    REGEXP_COUNT(sql_text, ':\w+') as total_binds,
    REGEXP_COUNT(sql_text, ':1\D') as bind_1_count,
    REGEXP_COUNT(sql_text, ':2\D') as bind_2_count
  FROM v$sql
  WHERE sql_text LIKE '%:%'
    AND parsing_schema_name = USER
)
SELECT * FROM bind_analysis
WHERE total_binds > 0
ORDER BY total_binds DESC;

3. Debug Application Bind Issues

-- Create procedure to test bind variable counts
CREATE OR REPLACE PROCEDURE test_bind_variables(
  p_sql_text VARCHAR2
) AS
  v_cursor INTEGER;
  v_bind_count NUMBER := 0;
  v_execute_result INTEGER;
BEGIN
  -- Parse the SQL
  v_cursor := DBMS_SQL.OPEN_CURSOR;

  BEGIN
    DBMS_SQL.PARSE(v_cursor, p_sql_text, DBMS_SQL.NATIVE);
    DBMS_OUTPUT.PUT_LINE('SQL parsed successfully');

    -- Count bind variables (simplified - counts colons)
    v_bind_count := REGEXP_COUNT(p_sql_text, ':\w+');
    DBMS_OUTPUT.PUT_LINE('Number of bind variables: ' || v_bind_count);

    -- Try to execute without binding (will fail if binds exist)
    BEGIN
      v_execute_result := DBMS_SQL.EXECUTE(v_cursor);
      DBMS_OUTPUT.PUT_LINE('Executed without binding - no bind variables present');
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -1008 THEN
          DBMS_OUTPUT.PUT_LINE('ORA-01008: Bind variables need values');
          DBMS_OUTPUT.PUT_LINE('You need to bind ' || v_bind_count || ' variable(s)');
        ELSE
          DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        END IF;
    END;

  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Parse error: ' || SQLERRM);
  END;

  DBMS_SQL.CLOSE_CURSOR(v_cursor);

EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(v_cursor) THEN
      DBMS_SQL.CLOSE_CURSOR(v_cursor);
    END IF;
    RAISE;
END;
/

-- Test the procedure
EXEC test_bind_variables('SELECT * FROM employees WHERE dept_id = :dept AND salary > :sal');

Immediate Solutions

Solution 1: Fix Basic Bind Variable Issues

Ensure All Bind Variables Have Values

-- Example: Correct use of bind variables in PL/SQL
DECLARE
  v_dept_id NUMBER := 10;
  v_salary NUMBER := 50000;
  v_count NUMBER;
BEGIN
  -- Incorrect: Missing bind for :sal
  -- SELECT COUNT(*) INTO v_count
  -- FROM employees
  -- WHERE department_id = :dept AND salary > :sal;

  -- Correct: Using bind variables properly
  EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM employees WHERE department_id = :dept AND salary > :sal'
  INTO v_count
  USING v_dept_id, v_salary;  -- Provide values for both binds

  DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
/

-- Using DBMS_SQL with proper binding
DECLARE
  v_cursor INTEGER;
  v_sql VARCHAR2(200);
  v_dept_id NUMBER := 10;
  v_salary NUMBER := 50000;
  v_result NUMBER;
  v_count NUMBER;
BEGIN
  v_sql := 'SELECT COUNT(*) FROM employees WHERE department_id = :dept AND salary > :sal';

  v_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);

  -- Bind all variables
  DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept', v_dept_id);
  DBMS_SQL.BIND_VARIABLE(v_cursor, ':sal', v_salary);

  -- Define column for output
  DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_count);

  -- Execute and fetch
  v_result := DBMS_SQL.EXECUTE(v_cursor);
  IF DBMS_SQL.FETCH_ROWS(v_cursor) > 0 THEN
    DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_count);
    DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
  END IF;

  DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/

Handle Duplicate Bind Names

-- When the same bind appears multiple times
DECLARE
  v_emp_id NUMBER := 100;
  v_result VARCHAR2(100);
BEGIN
  -- SQL with repeated bind variable
  EXECUTE IMMEDIATE
    'SELECT first_name || '' '' || last_name
     FROM employees
     WHERE employee_id = :id
        OR manager_id = :id
        OR department_id = (SELECT department_id FROM employees WHERE employee_id = :id)'
  INTO v_result
  USING v_emp_id, v_emp_id, v_emp_id;  -- Must provide value for each occurrence

  DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);

  -- Alternative: Use named notation (Oracle 12c+)
  EXECUTE IMMEDIATE
    'SELECT first_name FROM employees WHERE employee_id = :id OR manager_id = :id'
  INTO v_result
  USING IN v_emp_id, IN v_emp_id;
END;
/

Solution 2: Fix Dynamic SQL Issues

Build Dynamic SQL Correctly

-- Create function to build dynamic SQL with proper binds
CREATE OR REPLACE FUNCTION build_dynamic_query(
  p_table_name VARCHAR2,
  p_conditions VARCHAR2 DEFAULT NULL,
  p_order_by VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2 AS
  v_sql VARCHAR2(4000);
  v_bind_count NUMBER := 0;
BEGIN
  -- Start with base query
  v_sql := 'SELECT * FROM ' || p_table_name;

  -- Add WHERE clause if conditions provided
  IF p_conditions IS NOT NULL THEN
    v_sql := v_sql || ' WHERE ' || p_conditions;

    -- Count bind variables in conditions
    v_bind_count := REGEXP_COUNT(p_conditions, ':\w+');
    DBMS_OUTPUT.PUT_LINE('Query requires ' || v_bind_count || ' bind variable(s)');
  END IF;

  -- Add ORDER BY if provided
  IF p_order_by IS NOT NULL THEN
    v_sql := v_sql || ' ORDER BY ' || p_order_by;
  END IF;

  DBMS_OUTPUT.PUT_LINE('Generated SQL: ' || v_sql);

  RETURN v_sql;
END;
/

-- Use the function with proper binding
DECLARE
  v_sql VARCHAR2(4000);
  v_cursor SYS_REFCURSOR;
  v_emp employees%ROWTYPE;
  v_dept_id NUMBER := 50;
  v_salary NUMBER := 5000;
BEGIN
  -- Build dynamic SQL
  v_sql := build_dynamic_query(
    'employees',
    'department_id = :dept AND salary > :sal',
    'employee_id'
  );

  -- Execute with proper binds
  OPEN v_cursor FOR v_sql USING v_dept_id, v_salary;

  LOOP
    FETCH v_cursor INTO v_emp;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ' ' || v_emp.last_name);
  END LOOP;

  CLOSE v_cursor;
END;
/

Solution 3: Application-Level Fixes

Java JDBC Proper Binding

// Java example with proper bind variable handling
public class BindVariableExample {

    public void correctBindUsage(Connection conn) throws SQLException {
        String sql = "SELECT * FROM employees WHERE department_id = ? AND salary > ?";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            // Bind all variables - position matters!
            pstmt.setInt(1, 50);      // First ? - department_id
            pstmt.setDouble(2, 5000); // Second ? - salary

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    System.out.println(rs.getString("first_name") + " " +
                                     rs.getString("last_name"));
                }
            }
        }
    }

    public void namedParameterBinding(Connection conn) throws SQLException {
        // Using OraclePreparedStatement for named parameters
        String sql = "SELECT * FROM employees WHERE department_id = :dept " +
                    "AND salary > :sal AND hire_date > :hire_date";

        OraclePreparedStatement opstmt = (OraclePreparedStatement)
            conn.prepareStatement(sql);

        try {
            // Bind by name
            opstmt.setIntAtName("dept", 50);
            opstmt.setDoubleAtName("sal", 5000);
            opstmt.setDateAtName("hire_date",
                new java.sql.Date(System.currentTimeMillis()));

            try (ResultSet rs = opstmt.executeQuery()) {
                // Process results
            }
        } finally {
            opstmt.close();
        }
    }

    // Handle dynamic number of bind variables
    public void dynamicBinds(Connection conn, List<Integer> deptIds)
            throws SQLException {
        // Build SQL with correct number of binds
        StringBuilder sql = new StringBuilder(
            "SELECT * FROM employees WHERE department_id IN (");

        for (int i = 0; i < deptIds.size(); i++) {
            if (i > 0) sql.append(", ");
            sql.append("?");
        }
        sql.append(")");

        try (PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
            // Bind all values
            for (int i = 0; i < deptIds.size(); i++) {
                pstmt.setInt(i + 1, deptIds.get(i));
            }

            try (ResultSet rs = pstmt.executeQuery()) {
                // Process results
            }
        }
    }
}

Python cx_Oracle Binding

import cx_Oracle

class OracleBindExample:

    def correct_positional_binding(self, connection):
        """Example of correct positional binding"""
        cursor = connection.cursor()

        # SQL with positional binds
        sql = """
            SELECT employee_id, first_name, last_name, salary
            FROM employees
            WHERE department_id = :1 AND salary > :2
        """

        # Bind values in correct order
        dept_id = 50
        min_salary = 5000

        cursor.execute(sql, [dept_id, min_salary])  # List for positional

        for row in cursor:
            print(f"{row[1]} {row[2]}: ${row[3]}")

        cursor.close()

    def correct_named_binding(self, connection):
        """Example of correct named binding"""
        cursor = connection.cursor()

        # SQL with named binds
        sql = """
            SELECT employee_id, first_name, last_name
            FROM employees
            WHERE department_id = :dept_id
              AND salary BETWEEN :min_sal AND :max_sal
              AND hire_date > :hire_date
        """

        # Bind values using dictionary
        bind_vars = {
            'dept_id': 50,
            'min_sal': 5000,
            'max_sal': 10000,
            'hire_date': datetime(2020, 1, 1)
        }

        cursor.execute(sql, bind_vars)  # Dictionary for named binds

        for row in cursor:
            print(f"{row[1]} {row[2]}")

        cursor.close()

    def handle_duplicate_binds(self, connection):
        """Handle SQL with duplicate bind variable names"""
        cursor = connection.cursor()

        # SQL with repeated bind name
        sql = """
            SELECT *
            FROM employees e1
            WHERE e1.salary > (
                SELECT AVG(salary)
                FROM employees e2
                WHERE e2.department_id = :dept_id
            )
            AND e1.department_id = :dept_id
        """

        # cx_Oracle handles duplicate names automatically
        cursor.execute(sql, dept_id=50)  # Only provide once

        for row in cursor:
            print(row)

        cursor.close()

Long-Term Solutions

1. Implement Bind Variable Validation

Create Validation Framework

-- Create package for bind variable validation
CREATE OR REPLACE PACKAGE bind_validator AS

  TYPE bind_info_rec IS RECORD (
    bind_name VARCHAR2(100),
    bind_position NUMBER,
    bind_type VARCHAR2(30),
    is_bound BOOLEAN DEFAULT FALSE
  );

  TYPE bind_info_tab IS TABLE OF bind_info_rec;

  FUNCTION parse_bind_variables(p_sql VARCHAR2) RETURN bind_info_tab;
  FUNCTION validate_binds(p_sql VARCHAR2, p_bind_count NUMBER) RETURN BOOLEAN;
  PROCEDURE log_bind_error(p_sql VARCHAR2, p_error_msg VARCHAR2);

END bind_validator;
/

CREATE OR REPLACE PACKAGE BODY bind_validator AS

  FUNCTION parse_bind_variables(p_sql VARCHAR2) RETURN bind_info_tab AS
    v_binds bind_info_tab := bind_info_tab();
    v_pattern VARCHAR2(100) := '(:[a-zA-Z0-9_]+)';
    v_match VARCHAR2(100);
    v_position NUMBER := 1;
    v_bind_position NUMBER := 0;
  BEGIN
    -- Find all bind variables
    LOOP
      v_match := REGEXP_SUBSTR(p_sql, v_pattern, v_position);
      EXIT WHEN v_match IS NULL;

      v_bind_position := v_bind_position + 1;
      v_binds.EXTEND;
      v_binds(v_binds.COUNT).bind_name := v_match;
      v_binds(v_binds.COUNT).bind_position := v_bind_position;

      v_position := REGEXP_INSTR(p_sql, v_pattern, v_position) + LENGTH(v_match);
    END LOOP;

    RETURN v_binds;
  END parse_bind_variables;

  FUNCTION validate_binds(p_sql VARCHAR2, p_bind_count NUMBER) RETURN BOOLEAN AS
    v_binds bind_info_tab;
    v_unique_binds NUMBER;
  BEGIN
    v_binds := parse_bind_variables(p_sql);

    -- Count unique bind names
    SELECT COUNT(DISTINCT bind_name)
    INTO v_unique_binds
    FROM TABLE(v_binds);

    -- Check if provided bind count matches
    IF v_binds.COUNT = 0 AND p_bind_count = 0 THEN
      RETURN TRUE;  -- No binds needed
    ELSIF v_binds.COUNT > 0 AND p_bind_count = v_binds.COUNT THEN
      RETURN TRUE;  -- Correct number of binds
    ELSE
      log_bind_error(p_sql,
        'Expected ' || v_binds.COUNT || ' binds, got ' || p_bind_count);
      RETURN FALSE;
    END IF;
  END validate_binds;

  PROCEDURE log_bind_error(p_sql VARCHAR2, p_error_msg VARCHAR2) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO bind_error_log (
      error_time, sql_text, error_message, username
    ) VALUES (
      SYSTIMESTAMP, p_sql, p_error_msg, USER
    );
    COMMIT;
  END log_bind_error;

END bind_validator;
/

-- Create error logging table
CREATE TABLE bind_error_log (
  error_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  error_time TIMESTAMP,
  sql_text CLOB,
  error_message VARCHAR2(4000),
  username VARCHAR2(30)
);

2. Create Reusable Query Builder

Safe Dynamic SQL Builder

-- Create query builder package
CREATE OR REPLACE PACKAGE query_builder AS

  TYPE bind_value_rec IS RECORD (
    bind_name VARCHAR2(100),
    bind_value VARCHAR2(4000),
    bind_type VARCHAR2(30)
  );

  TYPE bind_value_tab IS TABLE OF bind_value_rec;

  FUNCTION build_select(
    p_table VARCHAR2,
    p_columns VARCHAR2 DEFAULT '*',
    p_where VARCHAR2 DEFAULT NULL,
    p_order_by VARCHAR2 DEFAULT NULL
  ) RETURN VARCHAR2;

  FUNCTION execute_query(
    p_sql VARCHAR2,
    p_binds bind_value_tab DEFAULT NULL
  ) RETURN SYS_REFCURSOR;

END query_builder;
/

CREATE OR REPLACE PACKAGE BODY query_builder AS

  FUNCTION build_select(
    p_table VARCHAR2,
    p_columns VARCHAR2 DEFAULT '*',
    p_where VARCHAR2 DEFAULT NULL,
    p_order_by VARCHAR2 DEFAULT NULL
  ) RETURN VARCHAR2 AS
    v_sql VARCHAR2(4000);
  BEGIN
    -- Validate table name
    SELECT 'SELECT ' || p_columns || ' FROM ' || p_table
    INTO v_sql
    FROM user_tables
    WHERE table_name = UPPER(p_table);

    -- Add WHERE clause
    IF p_where IS NOT NULL THEN
      v_sql := v_sql || ' WHERE ' || p_where;
    END IF;

    -- Add ORDER BY
    IF p_order_by IS NOT NULL THEN
      v_sql := v_sql || ' ORDER BY ' || p_order_by;
    END IF;

    RETURN v_sql;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20001, 'Table ' || p_table || ' does not exist');
  END build_select;

  FUNCTION execute_query(
    p_sql VARCHAR2,
    p_binds bind_value_tab DEFAULT NULL
  ) RETURN SYS_REFCURSOR AS
    v_cursor SYS_REFCURSOR;
    v_cursor_id INTEGER;
    v_exec_result INTEGER;
  BEGIN
    -- Use DBMS_SQL for dynamic binding
    v_cursor_id := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_cursor_id, p_sql, DBMS_SQL.NATIVE);

    -- Bind all provided values
    IF p_binds IS NOT NULL AND p_binds.COUNT > 0 THEN
      FOR i IN 1..p_binds.COUNT LOOP
        DBMS_SQL.BIND_VARIABLE(v_cursor_id,
          p_binds(i).bind_name,
          p_binds(i).bind_value);
      END LOOP;
    END IF;

    v_exec_result := DBMS_SQL.EXECUTE(v_cursor_id);

    -- Convert to ref cursor
    v_cursor := DBMS_SQL.TO_REFCURSOR(v_cursor_id);

    RETURN v_cursor;
  EXCEPTION
    WHEN OTHERS THEN
      IF DBMS_SQL.IS_OPEN(v_cursor_id) THEN
        DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
      END IF;
      RAISE;
  END execute_query;

END query_builder;
/

3. Implement Best Practices

Create Standard Procedures

-- Standard procedure template with proper bind handling
CREATE OR REPLACE PROCEDURE process_employee_data(
  p_dept_id IN NUMBER,
  p_min_salary IN NUMBER,
  p_max_salary IN NUMBER DEFAULT NULL,
  p_result OUT SYS_REFCURSOR
) AS
  v_sql VARCHAR2(4000);
  v_bind_count NUMBER := 2;  -- Track bind count
BEGIN
  -- Build SQL with conditional binds
  v_sql := 'SELECT employee_id, first_name, last_name, salary ' ||
           'FROM employees ' ||
           'WHERE department_id = :dept_id ' ||
           'AND salary >= :min_sal';

  -- Add optional condition
  IF p_max_salary IS NOT NULL THEN
    v_sql := v_sql || ' AND salary <= :max_sal';
    v_bind_count := 3;
  END IF;

  -- Execute with correct number of binds
  IF p_max_salary IS NOT NULL THEN
    OPEN p_result FOR v_sql
      USING p_dept_id, p_min_salary, p_max_salary;
  ELSE
    OPEN p_result FOR v_sql
      USING p_dept_id, p_min_salary;
  END IF;

  DBMS_OUTPUT.PUT_LINE('Query executed with ' || v_bind_count || ' bind variables');

EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -1008 THEN
      DBMS_OUTPUT.PUT_LINE('Bind variable mismatch detected');
      DBMS_OUTPUT.PUT_LINE('SQL: ' || v_sql);
      DBMS_OUTPUT.PUT_LINE('Expected binds: ' || v_bind_count);
    END IF;
    RAISE;
END;
/

Prevention Strategies

1. Development Standards

-- Create coding standards view
CREATE OR REPLACE VIEW coding_standards AS
SELECT 'Bind Variable Rules' as category, rule FROM (
  SELECT 1 as seq, 'Always use bind variables for user input' as rule FROM dual
  UNION ALL
  SELECT 2, 'Count binds before executing dynamic SQL' FROM dual
  UNION ALL
  SELECT 3, 'Use named binds for clarity in complex queries' FROM dual
  UNION ALL
  SELECT 4, 'Document number and purpose of each bind' FROM dual
  UNION ALL
  SELECT 5, 'Test with NULL and edge case values' FROM dual
) ORDER BY seq;

-- Automated bind checking trigger
CREATE OR REPLACE TRIGGER check_procedure_binds
BEFORE CREATE OR REPLACE ON SCHEMA
WHEN (ora_dict_obj_type = 'PROCEDURE' OR ora_dict_obj_type = 'FUNCTION')
DECLARE
  v_sql_text VARCHAR2(32000);
  v_bind_count NUMBER;
BEGIN
  -- Get the source code
  v_sql_text := DBMS_METADATA.GET_DDL(ora_dict_obj_type, ora_dict_obj_name);

  -- Check for potential bind issues
  IF REGEXP_LIKE(v_sql_text, 'EXECUTE IMMEDIATE.*:') THEN
    v_bind_count := REGEXP_COUNT(v_sql_text, ':\w+');

    -- Log for review
    INSERT INTO code_review_log (
      object_type, object_name, review_note, review_date
    ) VALUES (
      ora_dict_obj_type,
      ora_dict_obj_name,
      'Contains ' || v_bind_count || ' bind variables - verify USING clause',
      SYSDATE
    );
  END IF;
END;
/

2. Testing Framework

-- Create bind variable test suite
CREATE OR REPLACE PACKAGE bind_test_suite AS

  PROCEDURE test_query_binds(
    p_sql VARCHAR2,
    p_expected_binds NUMBER
  );

  PROCEDURE test_all_procedures;

END bind_test_suite;
/

CREATE OR REPLACE PACKAGE BODY bind_test_suite AS

  PROCEDURE test_query_binds(
    p_sql VARCHAR2,
    p_expected_binds NUMBER
  ) AS
    v_actual_binds NUMBER;
    v_cursor INTEGER;
  BEGIN
    -- Count actual binds in SQL
    v_actual_binds := REGEXP_COUNT(p_sql, ':\w+');

    IF v_actual_binds != p_expected_binds THEN
      RAISE_APPLICATION_ERROR(-20001,
        'Bind count mismatch. Expected: ' || p_expected_binds ||
        ', Actual: ' || v_actual_binds);
    END IF;

    -- Try to parse (will fail if SQL is invalid)
    v_cursor := DBMS_SQL.OPEN_CURSOR;
    BEGIN
      DBMS_SQL.PARSE(v_cursor, p_sql, DBMS_SQL.NATIVE);
      DBMS_OUTPUT.PUT_LINE('✓ SQL parsed successfully with ' ||
                          v_actual_binds || ' bind variables');
    FINALLY
      DBMS_SQL.CLOSE_CURSOR(v_cursor);
    END;
  END test_query_binds;

  PROCEDURE test_all_procedures AS
  BEGIN
    -- Test known procedures with dynamic SQL
    FOR proc IN (
      SELECT object_name
      FROM user_procedures
      WHERE object_type = 'PROCEDURE'
        AND object_name LIKE 'DYN_%'  -- Naming convention for dynamic SQL procs
    ) LOOP
      DBMS_OUTPUT.PUT_LINE('Testing procedure: ' || proc.object_name);
      -- Additional testing logic here
    END LOOP;
  END test_all_procedures;

END bind_test_suite;
/

Related Errors

• ORA-01006 - Bind variable does not exist
• ORA-01007 - Variable not in select list
• ORA-01036 - Illegal variable name/number
• ORA-01745 - Invalid host/bind variable name

Quick Reference

Emergency Response Steps

1. ✓ Identify the SQL statement causing the error
2. ✓ Count bind variables in the SQL
3. ✓ Count values being provided
4. ✓ Match bind variables to provided values
5. ✓ Check for duplicate bind names
6. ✓ Test with simplified query

Quick Commands

-- Count binds in SQL
SELECT REGEXP_COUNT('SELECT * FROM emp WHERE id = :1 AND dept = :2', ':\w+') FROM dual;

-- Test dynamic SQL
DECLARE
  v_sql VARCHAR2(200) := 'SELECT COUNT(*) FROM employees WHERE dept_id = :1';
  v_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE v_sql INTO v_count USING 10;
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
/

-- Check bind capture
SELECT name, position, value_string
FROM v$sql_bind_capture
WHERE sql_id = 'your_sql_id';

Prevention Guidelines

• Always count binds - Verify bind count matches values
• Use named binds - Clearer than positional for complex queries
• Test dynamic SQL - Validate before production deployment
• Document binds - Comment purpose of each bind variable
• Handle duplicates - Provide value for each occurrence
• Use bind arrays - For IN clauses with variable items