Oracle in-list bind

Oracle and JDBC Array binding for a statement with IN list version 1.0 Franck Pachot contact@pachot.net

http://docs.google.com/Doc?id=ddbk8jd6_385crzqtdgq

Introduction

Usage of bind variable instead of literals is a key to scalability.
However, we have no mean to use bind variables for a statement like the following:

SELECT ename,empno FROM emp where empno in (7369,7566,7782);

Especially when we have a variable number of elements in the list.

This document will show how to use an oracle collection as a bind variable, whith an example in Java.

Description

Nested Table

Oracle has several 2 types of collections that can be used in a SQL statement: nested tables and VARRAYS.
Here we will use a nested table to have a one dimension collection of unbounded homogeneous elements. This is a database object created with CREATE TYPE.

As we want a list of integers, we will use:
CREATE OR REPLACE TYPE NUMBER_LIST_TYPE AS TABLE OF number;

To use that collection, our statement is a bit different from a IN ( value, value , … ):
We will need to

1. cast the array that comes from jdbc to our collection type
2. transform our collection to a sql table
3. change our IN(list) to a IN(subquery)

So we transform the following query

SELECT ename,empno FROM emp where empno in (7369,7566,7782);

in:

SELECT ename,empno FROM emp
WHERE empno IN (
SELECT * FROM TABLE( CAST ( ? as NUMBER_LIST_TYPE ) )
)

JDBC array

To be able to transform a java array ( such as Int[] in our example ) into a bind variable that can be used as our nested table type, we will:

1. get a descriptor for our nested table: oracle.sql.ArrayDescriptor. It is created once (as the statement is parsed once).
2. create an array that is a jdbc datatype: oracle.sql ARRAY. It is created with the descriptor, and the java array.
3. we will the use PreparedStatement.setObject() to bind our variable for each execution.

Example

We suppose that the EMP table is already created
We have one sql file for the DDL that creates our collection type:

Test.sql:

CREATE OR REPLACE TYPE NUMBER_LIST_TYPE AS TABLE OF number;
/

And our java source code that

1. connects to the database
2. parse the statement and describe the collection
3. executes 2 times the statement with a different list.

Test.java:

import java.sql.*;
import oracle.sql.ArrayDescriptor;
import oracle.sql.ARRAY;
import oracle.jdbc.*;

/*
The following DDL must be run to create the oracle collection:
CREATE OR REPLACE TYPE NUMBER_LIST_TYPE AS TABLE OF number;
*/

public class Test{

static public void main(String[] args) throws SQLException {

/* CONNECTION ******************************************************************************* */

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection(“jdbc:oracle:thin:@host:port:sid”,”SCOTT”,”TIGER”);

/* PARSING ********************************************************************************** */

/* The oracle collection is described */
ArrayDescriptor oracleCollection = ArrayDescriptor.createDescriptor(”NUMBER_LIST_TYPE“,conn);

/*
The sql statement uses IN ( subselect ) syntax.
the input parameter is cast to our table type (NUMBER_LIST_TYPE)
then transformed as a table so we can select on it
*/
PreparedStatement stmt = conn.prepareStatement(
“ SELECT ename,empno FROM emp “
+” WHERE empno IN ( “
+” SELECT * FROM TABLE( CAST ( ? as NUMBER_LIST_TYPE ) ) “
+” ) “);

/* EXECUTION 1 ****************************************************************************** */

System.out.println(”1st execution:”);

/* define our java array */
int[] javaArray1 = { 7369,7566,7782 };

/* define our oracle array */
ARRAY jdbcArray1 = new ARRAY ( oracleCollection , conn , javaArray1 ) ;

/* bind that array to our statement bind variable */
stmt.setObject(1,jdbcArray1);

/* execute the query and browse the result */
ResultSet r=stmt.executeQuery(); while(r.next()){ System.out.println(”t”+”t”+r.getString(2)+”: “+r.getString(1)); }

/* EXECUTION 2 ****************************************************************************** */

System.out.println(”2st execution:”);

int[] javaArray2 = { 7900,7902 };
ARRAY jdbcArray2 = new ARRAY ( oracleCollection , conn , javaArray2 ) ;
//stmt.setObject(1,jdbcArray2,OracleTypes.ARRAY);
stmt.setObject(1,jdbcArray2);

/* execute the query and browse the result */
r=stmt.executeQuery(); while(r.next()){ System.out.println(”t”+”t”+r.getString(2)+”: “+r.getString(1)); }

/* END */

stmt.close();
conn.close();

}
}

The output is:

1st execution:
7369: SMITH
7566: JONES
7782: CLARK
2st execution:
7900: JAMES
7902: FORD

the statement has been parsed only once, and can be executed several times with a different array of values.

Remarks

• We used ‘SELECT * FROM TABLE()’. The name of the column returned by a nested table type is: COLUMN_VALUE
• We can use an array of character strings (String[]). The collection will be a TABLE OF VARCHAR2(…)
  Note that I had a problem when not having nls_charset12.jar in the CLASSPATH (all strings where null)
• That is an introduction of passing arrays, structures objects between java and a SQL statement, or a stored procedure.
  

References

• Oracle JDBC FAQ
• AskTom answer on ” How can I do a variable ‘in list’ “

All tests and documentation that helped building this document were related to 10gR2 Oracle version. All comments are welcome at contact@pachot.net