What is Ordinal binding and Named binding in JDBC

Question

What is Ordinal binding and Named binding in JDBC? while calling a PL/SQL procedure i am getting an exception

java.sql.SQLException: operation not allowed: Ordinal binding and Named binding cannot be combined!

Answer 1

In Oracle, for example, you can write a stored procedure with PL/SQL code like:

CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
    tot_emps NUMBER;
    BEGIN
    DELETE FROM employees
    WHERE employees.employee_id = remove_emp.employee_id;
    tot_emps := tot_emps - 1;
END;
/

When you call this from Java, you use code like:

CallableStatement cs = conn.prepareCall("{call remove_emp(employee_id)}");
cs.setInt(1, 42);             // ordinal binding, or
cs.setInt("employee_id", 42); // named binding

With only one argument, the choice doesn't matter. However, you can't mix techniques.

CallableStatement cs = conn.prepareCall("{call xyzzy(plugh, bedquilt)]");
cs.setInt(1, 42);         // ordinal binding, and
cs.setInt("plugh", 1729); // named binding
cs.executeQuery();        // throws

Answer 2

Ordinal means by index. Named means by name.

Google search reveals that this could come up in a number of ways to misuse the API, so it's impossible to guess which one you fell into without seeing your code.