| how to pass any array from java to Oracle [message #49351] |
Fri, 04 February 2005 07:31  |
Hina
Messages: 51
Registered: April 2004
|
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?
|
|
|
|
|
|
| Re: how to pass any array from java to Oracle [message #580563 is a reply to message #580487] |
Mon, 25 March 2013 16:56 |
 |
Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
-- demo script:
CREATE OR REPLACE TYPE sample_type AS OBJECT
EXTERNAL NAME 'Keyvaluepair' LANGUAGE JAVA
USING SQLData
(key_name NUMBER(9) EXTERNAL NAME 'key_name',
value_name VARCHAR2(30) EXTERNAL NAME 'value_name')
/
SHOW ERRORS
CREATE OR REPLACE TYPE sample_type_tab AS TABLE OF sample_type;
/
SHOW ERRORS
create or replace and compile java source named Keyvaluepair as
import java.sql.*;
import java.io.*;
public class Keyvaluepair implements SQLData
{
private String sql_type = "SAMPLE_TYPE";
public int key_name;
public String value_name;
public Keyvaluepair () {}
public String getSQLTypeName() throws SQLException { return sql_type; }
public void readSQL(SQLInput stream, String typeName) throws SQLException
{
sql_type = typeName;
key_name = stream.readInt();
value_name = stream.readString();
}
public void writeSQL(SQLOutput stream) throws SQLException
{
stream.writeInt (key_name);
stream.writeString (value_name);
}
}
/
SHOW ERRORS
create or replace and compile java source named arraydemo as
import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class ArrayDemo
{
public static void passArray() throws SQLException
{
Connection conn = new OracleDriver().defaultConnection();
ArrayDemo a = new ArrayDemo();
Keyvaluepair kvp1 = new Keyvaluepair();
kvp1.key_name = 10;
kvp1.value_name = "Accounting";
Keyvaluepair kvp2 = new Keyvaluepair();
kvp2.key_name = 20;
kvp2.value_name = "Sales";
Keyvaluepair kvp3 = new Keyvaluepair();
kvp3.key_name = 30;
kvp3.value_name = "Finance";
Keyvaluepair[] P_arr = {kvp1, kvp2, kvp3};
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();
}
}
/
SHOW ERRORS
CREATE OR REPLACE PROCEDURE give_me_an_array
(p_array in sample_type_tab)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE ('key_name value_name');
FOR i IN 1 .. p_array.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE
(RPAD (p_array(i).key_name, 10) ||
p_array(i).value_name);
END LOOP;
END;
/
SHOW ERRORS
CREATE OR REPLACE PROCEDURE show_java_calling_plsql
AS LANGUAGE JAVA
NAME 'ArrayDemo.passArray()';
/
SHOW ERRORS
SET SERVEROUTPUT ON
EXEC show_java_calling_plsql
-- execution:
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE sample_type AS OBJECT
2 EXTERNAL NAME 'Keyvaluepair' LANGUAGE JAVA
3 USING SQLData
4 (key_name NUMBER(9) EXTERNAL NAME 'key_name',
5 value_name VARCHAR2(30) EXTERNAL NAME 'value_name')
6 /
Type created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE sample_type_tab AS TABLE OF sample_type;
2 /
Type created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> create or replace and compile java source named Keyvaluepair as
2
3 import java.sql.*;
4 import java.io.*;
5
6 public class Keyvaluepair implements SQLData
7 {
8 private String sql_type = "SAMPLE_TYPE";
9
10 public int key_name;
11 public String value_name;
12
13 public Keyvaluepair () {}
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 key_name = stream.readInt();
21 value_name = stream.readString();
22 }
23
24 public void writeSQL(SQLOutput stream) throws SQLException
25 {
26 stream.writeInt (key_name);
27 stream.writeString (value_name);
28 }
29
30 }
31 /
Java created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> create or replace and compile java source named arraydemo as
2 import java.io.*;
3 import java.sql.*;
4 import oracle.sql.*;
5 import oracle.jdbc.*;
6
7 public class ArrayDemo
8 {
9
10 public static void passArray() throws SQLException
11 {
12 Connection conn = new OracleDriver().defaultConnection();
13
14 ArrayDemo a = new ArrayDemo();
15
16 Keyvaluepair kvp1 = new Keyvaluepair();
17 kvp1.key_name = 10;
18 kvp1.value_name = "Accounting";
19
20 Keyvaluepair kvp2 = new Keyvaluepair();
21 kvp2.key_name = 20;
22 kvp2.value_name = "Sales";
23
24 Keyvaluepair kvp3 = new Keyvaluepair();
25 kvp3.key_name = 30;
26 kvp3.value_name = "Finance";
27
28 Keyvaluepair[] P_arr = {kvp1, kvp2, kvp3};
29
30 ArrayDescriptor descriptor =
31 ArrayDescriptor.createDescriptor( "SAMPLE_TYPE_TAB", conn );
32
33 ARRAY array_to_pass =
34 new ARRAY( descriptor, conn, P_arr);
35
36 OraclePreparedStatement ps =
37 (OraclePreparedStatement)conn.prepareStatement
38 ( "begin give_me_an_array(:x); end;" );
39
40 ps.setARRAY( 1, array_to_pass );
41 ps.execute();
42 }
43 }
44 /
Java created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE give_me_an_array
2 (p_array in sample_type_tab)
3 AS
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE ('key_name value_name');
6 FOR i IN 1 .. p_array.COUNT
7 LOOP
8 DBMS_OUTPUT.PUT_LINE
9 (RPAD (p_array(i).key_name, 10) ||
10 p_array(i).value_name);
11 END LOOP;
12 END;
13 /
Procedure created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE show_java_calling_plsql
2 AS LANGUAGE JAVA
3 NAME 'ArrayDemo.passArray()';
4 /
Procedure created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> SET SERVEROUTPUT ON
SCOTT@orcl_11gR2> EXEC show_java_calling_plsql
key_name value_name
10 Accounting
20 Sales
30 Finance
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2>
-- script to remove objects created by script:
drop procedure show_java_calling_plsql
/
drop procedure give_me_an_array
/
drop java source arraydemo
/
drop type sample_type_tab
/
drop type sample_type
/
drop java source Keyvaluepair
/
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
