Named Parameters Defined

Named Parameters are especially useful when a procedure has many parameters with default values. With this new feature we need to specify only the values for parameters that have no default value, hence the ease of use and flexibility. Prior to JDBC 3.0, the way to set a parameter in a stored procedure was by specifying the parameter's index, not its name.

Benefits of Using Named Parameters

For JDBC applications, until JDBC 3.0, positional or the ordinal binding was the only way to bind the parameters. Previous versions of Oracle JDBC drivers conditionally allowed binding
statement variables by name when using the setXXX methods. However, this capability to use setXXX methods to bind by name is not supported by Oracle, and neither is it part if the JDBC specification. The JDBC drivers can throw a SQLException or produce unexpected results. In Release 10 (10.1) JDBC drivers, bind by name is supported using the setXXXAtName methods.

The following are some of the benefits of using the Named Parameters feature:

public void registerOutParameter(String parameterName, int sqlType) throws SQLException;

The OracleCallableStatement Interface

The oracle.jdbc.OracleCallableStatement interface extends the OraclePreparedStatement interface (which extends the OracleStatement interface) and incorporates standard JDBC callable statement functionality. This means that all the proprietary methods that are available in OracleStatement and OraclePreparedStatement are also available in Oracle-CallableStatement.

To create a CallableStatement object, use the Connection.prepareCall() method. You need to pass it with just one parameter -- a string -- describing how the procedure or function is invoked. For example:

CallableStatement cstmt = null;
     try {
     cstmt = conn.prepareCall("{ ? = call validate( ? ) }");
     . . .
     } 
     In this example, the following string was passed as an argument to prepareCall(      ): 

{ ? = call tonumberfun( ? ) }

Next, let's look at some possible errors that you might encounter when creating a CallableStatement object.

Handling Errors

As with the Statement and PreparedStatement objects we have already covered, if there is something wrong with your stored procedure, callable-statement syntax, the call to your Connection object's prepareStatement() method will throw a SQLException. The most common SQLException occurs when a procedure or function does not exist:

SQL Error: ORA-06550: line 1, column 13:
PLS-00201: identifier 'TONUMBERFUN' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

After you create a CallableStatement object, you need to register any OUT parameters before executing the statement. Next, let's see how you do that.

Registering OUT Parameters

To get the results from a stored procedure call, you must register any OUT or IN OUT parameters before executing the CallableStatement. This can be done by calling the CallableStatement object's registerOutParameter() method, and passing it the position of the placeholder character in the callable-statement string starting at 1 and moving from left to right (just as you do for the other accessor methods). Additionally, you'll need to specify a java.sql.Types constant to specify the SQL data type that will be returned.

There are two applicable signatures for the registerOutParameter( ) method. For VARCHAR2 and DATE parameters, use the following signature:

registerOutParameter(
int parameterIndex, 
int sqlType)
throws SQLException

registerOutParameter(
int parameterIndex, 
int sqlType, 
int scale)
throws SQLException

The scale in the second signature allows you to control the number of significant digits to the right of the decimal point that are returned.

Setting IN Parameters

Setting IN or IN OUT parameter for CallableStatements is similar to how it's done for prepared statements. Use the setXXX() accessor methods, passing the position of the placeholder character in the callable-statement string (starting from 1, counting from left to right) along with an appropriate Java data type. When determining the ordinal position of a parameter, start counting with the left-most placeholder and count each placeholder in the statement until you get to the parameter that is being set. Also, ensure to count each placeholder, irrespective of whether it's an IN, OUT, or IN OUT parameter.

But what if you need to set a parameter value to NULL? There is a special setNULL( ) method for setting an IN parameter to NULL values.

Oracle JDBC 10g support for Named parameters

Oracle JDBC 10g supports Named parameters by providing two sets of APIs:

set<datatype>(String, ...) is part of JDBC 3.0 named parameter for the CallableStatement feature and can be used against CallableStatement only. The parameter names are the actual names of the IN, OUT or INOUT parameters. You can refer this OTN sample: 'NamedParameters' that demonstrates the usage of Named parameters in a CallableStatement.

set<datatype>AtName(String,...) is part of Oracle Database 10g JDBC driver; these are Oracle Specific APIs used with a PreparedStatement. The SQL syntax for the parameter should be, for example, ":num" for a parameter named "num".

"SELECT * FROM table1 WHERE column3 < :num AND column5 > :num"    

The advantage of using set<datatype>AtName(String,...) is, if the variable shows up more than once, you need to bind it only once. For the SQL statement stated earlier, you need to call setIntAtName("num", 100) only once.