Oracle 10g : How to pass ARRAYS to Oracle using Java?

Here i'm trying to send an array of integers as a parameter to a callablestatement.

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.Types;

import javax.sql.DataSource;

import oracle.sql.ARRAY;

import oracle.sql.ArrayDescriptor;

import oracle.sql.StructDescriptor;

import com.cs.datasource.CardSellerDataSource;

public class TestMessageBroadCasting {

public static void main(String args[]) {

try {

DataSource dataSource = ... ...;

Connection connection = dataSource.getConnection();

String sql = "{call package_name.proc_name(?, ?, ?, ?, ?, ?)}";

Integer [] arr = {1 , 2};

ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(

"RESELLERLIST", connection);

ARRAY array = new ARRAY(arrayDescriptor, connection, arr);

CallableStatement callableStatement = (oracle.jdbc.driver.OracleCallableStatement)connection.prepareCall(sql);

callableStatement.setInt(1, 1);

callableStatement.setString(2, "A for apple"

+ System.currentTimeMillis());

callableStatement.setArray(3, array);

callableStatement.setString(4, "0");

callableStatement.setInt(5, 0);

callableStatement.setString(6, "");

callableStatement.registerOutParameter(4, Types.VARCHAR);

callableStatement.registerOutParameter(5, Types.INTEGER);

callableStatement.registerOutParameter(6, Types.VARCHAR);

callableStatement.execute();

String b = callableStatement.getString(6);

System.out.println("message : " + b);

} catch (SQLException e) {

e.printStackTrace();

}

}

}

So, first define an array of integer

Integer [] arr = {1 , 2};

We need a ArrayDescriptor.

ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(

"VALUELIST", connection);

VALUELIST is the name of the type defined in your database(Oracle)

Two points we need to note are 

1. VALUELIST must be defined in SCHEMA LEVEL rather than PACKAGE LEVEL
2. VALUELIST must always be typed in uppercase letter.

The errors you might get while not defining the name of the type well is

java.sql.SQLException: invalid name pattern: ... ...

|