passing arrays to oracle

[Stu Johns]

Hi,
I am trying to pass an array of Strings to insert into an oracle table in 1 hit.
I have defined types in oracle:

   
create table CHAR_ARRAY_TABLE(col1 varchar2(1000));

CREATE TYPE person AS object 
            (c1 VARCHAR2(30)); 
/

CREATE TYPE array_of_persons AS varray(10)
            OF person; 
/
 

and have written the code:

  

	  public static void main(String[] args) throws Exception {
	    Connection conn = null;
	    PreparedStatement pstmt = null;
	    java.sql.Array sqlArray = null;
	    DatabaseTransaction dt = new DatabaseTransaction();
	    conn = dt.getConnection();
	    // For oracle you need an array descriptor specifying
	    // the type of the array and a connection to the database
	    // the first parameter must match with the SQL ARRAY type created
	    ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("ARRAY_OF_PERSONS", conn);
	    // then obtain an Array filled with the content below
	    String[] content = { "v4" };
	    
	    sqlArray = new oracle.sql.ARRAY(arrayDescriptor, (OracleConnection)conn, content);

	    String query = "insert into CHAR_ARRAY_TABLE(col1) values( ?)";

	    pstmt = conn.prepareStatement(query);
	    //pstmt.setString(1, "0001");
	    pstmt.setArray(1, sqlArray);

	    int rowCount = pstmt.executeUpdate();
	    
	    conn.commit();
	    
	    System.out.println("rowCount=" + rowCount);
	    System.out.println("--Demo_PreparedStatement_SetArray end--");
	    pstmt.close();
	    conn.close();
	  }
  

However I get an error when i try and run it:
Exception in thread "main" java.sql.SQLException: Fail to convert to internal representation: v4
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.oracore.OracleTypeADT.toDatum(OracleTypeADT.java:239)
at oracle.jdbc.oracore.OracleTypeADT.toDatumArray(OracleTypeADT.java:274)
at oracle.jdbc.oracore.OracleTypeUPT.toDatumArray(OracleTypeUPT.java:115)
at oracle.sql.ArrayDescriptor.toOracleArray(ArrayDescriptor.java:1314)
at oracle.sql.ARRAY.<init>(ARRAY.java:152)
at TestArray.main(TestArray.java:63)


Anyone got any ideas how to do an insert with an array in oracle?

thanks

[Lorand Komaromi]

Stu Johns wrote:
I have defined types in oracle:

   
create table CHAR_ARRAY_TABLE(col1 varchar2(1000));

CREATE TYPE person AS object 
            (c1 VARCHAR2(30)); 
/

CREATE TYPE array_of_persons AS varray(10)
            OF person; 
/

I don't have much experience with Oracle, so this could be wrong, but shouldn't col1 be of type array_of_persons..?

[Anil Kumar Saha]

Have a look into it. It may help
http://agilej.blogspot.com/2009/05/passing-oraclesqlarray-to-stored.html

[Stu Johns]

Thanks folks.
Lorand you were right the columns didnt match.
Anil your example was a great link, I have decided to go the package route for my solution.