| how to pass any array from java to Oracle [message #49351] |
Fri, 04 February 2005 07:31  |
Hina Messages: 51 Registered: February 2005 |
Member |
|
|
Hi,
Here is a problem. I have to pass an array e.g ('Account','Marketing',Computer' ) from java to Oracle 9i.
Would I be able to do in Oracle, if yes then how ?
Thanks
|
|
|
|
|
| Re: how to pass any array from java to Oracle [message #178923 is a reply to message #49378 ] |
Fri, 23 June 2006 07:24   |
a_developer Messages: 194 Registered: January 2006 |
Senior Member |
|
|
Barbara, I followed the example you gave - only I am using an array of Objects:
create or replace type SAMPLE_TYPE is object (person_id number,
birthday date);
create or replace type SAMPLE_TYPE_TAB is table of SAMPLE_TYPE
I modified your code to:
create or replace and compile java source named Person as
import java.util.Date;
public class Person
{ public int person_id;
public Date birthday;
}
and
create or replace and compile java source named arraydemo as
import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class ArrayDemo
{
private final String JAVA_DATE_FORMAT = "dd-MMM-yyyy hh:mm:ss a";
public Date getDate(String strDate) {
Date dt = null;
DateFormat dateFormat = new SimpleDateFormat(JAVA_DATE_FORMAT);
try {
dt = dateFormat.parse(strDate);
} catch (ParseException e) {
}
return dt;
}
public static void passArray() throws SQLException
{
Connection conn = new OracleDriver().defaultConnection();
ArrayDemo a = new ArrayDemo();
Person pn1 = new Person();
pn1.person_id = 1;
pn1.birthday = a.getDate("01-JAN-2000");
Person pn2 = new Person();
pn2.person_id = 2;
pn2.birthday = a.getDate("01-JAN-2001");
Person pn3 = new Person();
pn3.person_id = 31;
pn3.birthday = a.getDate("01-JAN-2002");
Person[] P_arr = {pn1, pn2, pn3};
ArrayDescriptor descriptor =
ArrayDescriptor.createDescriptor( "SAMPLE_TYPE_TAB", conn );
ARRAY array_to_pass =
new ARRAY( descriptor, conn, P_arr);
OraclePreparedStatement ps =
(OraclePreparedStatement)conn.prepareStatement
( "begin give_me_an_array(:x); end;" );
ps.setARRAY( 1, array_to_pass );
ps.execute();
}
}
But I am getting the following error:
ORA-29532: Java call terminated by uncaught Java exception: java.sql.SQLException:Fail to convert to internal representation: Person@e2c7e499
ORA-06512: at "SCOTT.SHOW_JAVA_CALLING_PLSQL",LINE 0
ORA-06512: at line 3
Can you help me resolve this??
I also read in Oracle's documentation:
Oracle9i JDBC Developer's Guide and Reference
Release 2 (9.2)
11 Working with Oracle Collections:
Note
The setARRAY(), setArray(), and setObject() methods of the OraclePreparedStatement class take an object of the type oracle.sql.ARRAY as an argument, not an array of objects.
Does this have anything to do with the error??
|
|
|
| Re: how to pass any array from java to Oracle [message #240787 is a reply to message #49351 ] |
Fri, 25 May 2007 16:24   |
rvedala Messages: 1 Registered: May 2007 |
Junior Member |
|
|
Here is the solution:
You should make the changes to Person object as shown below :
import java.util.Date;
import java.io.Serializable;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
public class Person implements SQLData, Serializable{
public int person_id;
public Date birthday;
// getters and setters
public String getSQLTypeName() throws SQLException {
return "SAMPLE_TYPE";
}
public void readSQL(SQLInput stream, String typeName) throws
SQLException {
}
public void writeSQL(SQLOutput stream) throws SQLException {
}
}
That's it. Enjoy !!
r-a-v-i
|
|
|
| Re: how to pass any array from java to Oracle [message #315714 is a reply to message #49351 ] |
Tue, 22 April 2008 09:18  |
hirschs Messages: 1 Registered: April 2008 |
Junior Member |
|
|
|
I have followed this solution to pass in an array, and it compiles and runs successfully, but the attributes of the array elements are all null when I access them in the stored proc. Any ideas what could be wrong? I was wondering how it maps the attributes of the java object to the PL/SQL object? Is it by exact name match, position in the declaration? I am using Long, BigDecimal and String data types. Could this be a problem?
|
|
|