Passing an array from Java to Oracle procedure.. [message #180569] |
Tue, 04 July 2006 04:10  |
a_developer
Messages: 194 Registered: January 2006
|
Senior Member |
|
|
This is a follow-up on the thread: http://www.orafaq.com/forum/m/178923/81982/#msg_178923
Anyone who happens to know how to pass an array of Objects using Java??
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;
Wrote a code (rather, revised the one given by Barbara):
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 anyone help me resolve this??
|
|
|
Re: Passing an array from Java to Oracle procedure.. [message #180589 is a reply to message #180569] |
Tue, 04 July 2006 06:28   |
hobbes
Messages: 173 Registered: January 2006
|
Senior Member |
|
|
To use an array of custom objects you need to create a mapping between the SQL object type and the corresponding Java class.
Oracle documentation suggests various ways to create this mapping, and recommends using JPublisher.
The following is one way to do it: it uses an SQLJ Type in the database to map to the Java class.
SQL> create or replace and compile java source named Person as
2
3 import java.sql.*;
4 import java.io.*;
5
6 public class Person implements SQLData
7 {
8 private String sql_type = "PERSON_T";
9
10 public int person_id;
11 public String person_name;
12
13 public Person () {}
14
15 public String getSQLTypeName() throws SQLException { return sql_type; }
16
17 public void readSQL(SQLInput stream, String typeName) throws SQLException
18 {
19 sql_type = typeName;
20 person_id = stream.readInt();
21 person_name = stream.readString();
22 }
23
24 public void writeSQL(SQLOutput stream) throws SQLException
25 {
26 stream.writeInt (person_id);
27 stream.writeString (person_name);
28 }
29
30 }
31 /
Java created.
SQL> CREATE TYPE person_t AS OBJECT
2 EXTERNAL NAME 'Person' LANGUAGE JAVA
3 USING SQLData (
4 person_id NUMBER(9) EXTERNAL NAME 'person_id',
5 person_name VARCHAR2(30) EXTERNAL NAME 'person_name'
6 )
7 /
Type created.
SQL> CREATE TYPE person_tab IS TABLE OF person_t;
2 /
Type created.
SQL> create or replace and compile java source named arraydemo as
2 import java.util.Date;
3 import java.io.*;
4 import java.sql.*;
5 import oracle.sql.*;
6 import oracle.jdbc.driver.*;
7 import java.text.DateFormat;
8 import java.text.ParseException;
9 import java.text.SimpleDateFormat;
10
11 public class ArrayDemo
12 {
13
14 public static void passArray() throws SQLException
15 {
16 Connection conn = new OracleDriver().defaultConnection();
17
18 ArrayDemo a = new ArrayDemo();
19
20 Person pn1 = new Person();
21 pn1.person_id = 1;
22 pn1.person_name = "TestName1";
23
24 Person pn2 = new Person();
25 pn2.person_id = 2;
26 pn2.person_name = "TestName2";
27
28 Person pn3 = new Person();
29 pn3.person_id = 31;
30 pn3.person_name = "TestName3";
31
32 Person[] P_arr = {pn1, pn2, pn3};
33
34 ArrayDescriptor descriptor =
35 ArrayDescriptor.createDescriptor( "PERSON_TAB", conn );
36
37 ARRAY array_to_pass =
38 new ARRAY( descriptor, conn, P_arr);
39
40 OraclePreparedStatement ps =
41 (OraclePreparedStatement)conn.prepareStatement
42 ( "begin give_me_an_array(:x); end;" );
43
44 ps.setARRAY( 1, array_to_pass );
45 ps.execute();
46 }
47 }
48 /
Java created.
SQL> create or replace
2 procedure give_me_an_array( p_array in person_tab )
3 as
4 begin
5 for i in 1 .. p_array.count
6 loop
7 dbms_output.put_line('Person Id:'|| p_array(i).person_id||' Name: '||p_array(i).person_name);
8 end loop;
9 end;
10 /
Procedure created.
SQL> create or replace
2 procedure show_java_calling_plsql
3 as language java
4 name 'ArrayDemo.passArray()';
5 /
Procedure created.
SQL>
SQL> set serveroutput on
SQL> exec show_java_calling_plsql
Person Id:1 Name: TestName1
Person Id:2 Name: TestName2
Person Id:31 Name: TestName3
PL/SQL procedure successfully completed.
|
|
|
|