How to send Java Array to Oracle VARRAY
Hi there
I am using Oracle VARRAYS. And the following is my JDBC Code. How to pass an Array to Oracle Stored Procedure. I am getting an error saying "invalid Column name"
Thanks
S.Srikanthan
Please mail me at sudhasrikanthan@paragonsolutions.comimport java.io.*;
import java.sql.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.sql.*;
class TestOracle {
public static void main(String arg[]) throws Exception {
//---------Conection----------
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@oraserv:1521:orcl","nw","nw");
System.out.println("Conn :"+con);
//---- ADDRESS LISTS ----------
int o_err_no =0;
ARRAY newArray = null;
Address addresselements[] = {new Address("1", "1","Smruti"),new Address("2", "1","Bhavana"),new Address("3", "1","Bobly")};
System.out.println("addresselements :" + (addresselements));
STRUCT str = null;
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("typProviderAddr", con);
System.out.println("ArrayDescriptor :"+desc );
newArray= new ARRAY(desc, con, addresselements);
System.out.println("ARRAY :"+newArray);
String sql = "{call pro_demographics1.INS_PROVIDER_DEMOGRAPHICS(?,?,?,?)}"; //-6
try{
// -------------prepare statement--------------------------
CallableStatement statement = con.prepareCall(sql);
System.out.println("statement :"+statement );
// ------------ Asign values to the SP Parameters -------
((OracleCallableStatement)statement).setString(1,"2342342342");
((OracleCallableStatement)statement).setString(2,"1");
((OracleCallableStatement)statement).setString(3,"PRO_FIRST_NAME");
System.out.println("Before setARRAY");
((OracleCallableStatement)statement).setARRAY(4, newArray);
System.out.println("After setARRAY");
//----------- Call the Stored Procedure --------------------
try{
statement.execute();
statement.close();
}catch(Exception e){
o_err_no = -1;
System.out.println(e);
}
System.out.println("After Execute");
//-------------Commit/Rollback ----------------------------
if(o_err_no == 0){
con.commit(); // COMMIT HAS TO BE DONE FROM JAVA
System.out.println("comitted");
}
}catch(Exception e){
System.out.println(e);
}finally{
//-------------Free connection-------------------------------
try {
con.close();
} catch(Exception e) {
System.out.println("error");
}
}
}
}