Window 2 World

1) create an array type in oracle
----------------------------------

create or replace type NUM_ARRAY as table of number(10);

2)create oracle procedure
--------------------------

CREATE OR REPLACE PROCEDURE TEST_procedure(p_array in num_array,ERR_MSG OUT VARCHAR2)
IS
BEGIN
for i in 1 .. p_array.count
loop
insert into TESTTABLE values('T' || i,'TEST');
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
BEGIN
ROLLBACK;
ERR_MSG := SQLERRM(SQLCODE);-- send the error message back if there any exeptions.
END;
END;

3)java program
---------------
import java.sql.Connection;
import java.sql.SQLException;

import oracle.jdbc.driver.OraclePreparedStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

import org.hibernate.HibernateException;

public class CallSP{
public static void main(String args[]){

try {
int intArray[] = { 1,2,3,4,5,6 };
// from any connection pool get the connection
Connection conn =HibernateUtil.currentSession().connection();

OraclePreparedStatement ps =
(OraclePreparedStatement)conn.prepareStatement
( "begin TEST_procedure(:x,:outparam); end;" );

ArrayDescriptor descriptor =
ArrayDescriptor.createDescriptor( "NUM_ARRAY", ps.getConnection());

ARRAY array_to_pass = new ARRAY( descriptor, conn, intArray );
ps.setARRAY( 1, array_to_pass );
ps.registerOutParameter(2,Types.VARCHAR);
ps.execute();
String strResult = ps.getString(2);

} catch (HibernateException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}

}
}// end of : CallSP class