Home » SQL & PL/SQL » SQL & PL/SQL » Passing an array from Java to Oracle procedure..
Passing an array from Java to Oracle procedure.. [message #180569] Tue, 04 July 2006 04:10 Go to next message
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 Go to previous messageGo to next message
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.


icon7.gif  Re: Passing an array from Java to Oracle procedure.. [message #180601 is a reply to message #180589] Tue, 04 July 2006 07:06 Go to previous message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Thanks hobbes! I tried to use dates also in my object -- it's working! Smile
Previous Topic: Display Columns into Rows
Next Topic: Problems with precision and scale of a number
Goto Forum:
  


Current Time: Sun Jul 20 00:31:36 CDT 2025