Home » Developer & Programmer » JDeveloper, Java & XML » how to pass any array from java to Oracle
how to pass any array from java to Oracle [message #49351] Fri, 04 February 2005 07:31 Go to next message
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 #49352 is a reply to message #49351 ] Fri, 04 February 2005 07:51 Go to previous messageGo to next message
MikeL
Messages: 15
Registered: January 2005
Junior Member
http://javaalmanac.com/egs/java.sql/InsertArrayPs.html?l=rel
Re: how to pass any array from java to Oracle [message #49378 is a reply to message #49351 ] Fri, 04 February 2005 23:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 3940
Registered: November 2002
Location: California, USA
Senior Member
The following is the same modification of Tom Kyte's example that I posted on the PL/SQL Pipelines. I am posting it again here for the benefit of anyone else who may be searching for an answer to the same question and come upon this thread.

scott@ORA92> create or replace
  2  and compile java source named "ArrayDemo"
  3  as
  4  import java.io.*;
  5  import java.sql.*;
  6  import oracle.sql.*;
  7  import oracle.jdbc.driver.*;
  8  
  9  public class ArrayDemo
 10  {
 11  public static void passArray() throws SQLException
 12  {
 13  	 Connection conn =
 14  	    new OracleDriver().defaultConnection();
 15  
 16  	 String strArray[] = { "Account","Marketing","Computer" };
 17  
 18  	 ArrayDescriptor descriptor =
 19  	    ArrayDescriptor.createDescriptor( "STRING_ARRAY", conn );
 20  
 21  	 ARRAY array_to_pass =
 22  	    new ARRAY( descriptor, conn, strArray );
 23  
 24  	 OraclePreparedStatement ps =
 25  	     (OraclePreparedStatement)conn.prepareStatement
 26  	     ( "begin give_me_an_array(:x); end;" );
 27  
 28  	 ps.setARRAY( 1, array_to_pass );
 29  
 30  	 ps.execute();
 31  
 32  }
 33  
 34  }
 35  /

Java created.

scott@ORA92> show errors
No errors.
scott@ORA92> create or replace type STRING_ARRAY as table of varchar2(30);
  2  /

Type created.

scott@ORA92> show errors
No errors.
scott@ORA92> create or replace
  2  procedure give_me_an_array( p_array in string_array )
  3  as
  4  begin
  5  	 for i in 1 .. p_array.count
  6  	 loop
  7  	     dbms_output.put_line( p_array(i) );
  8  	 end loop;
  9  end;
 10  /

Procedure created.

scott@ORA92> show errors
No errors.
scott@ORA92> create or replace
  2  procedure show_java_calling_plsql
  3  as language java
  4  name 'ArrayDemo.passArray()';
  5  /

Procedure created.

scott@ORA92> show errors
No errors.
scott@ORA92> set serveroutput on
scott@ORA92> exec show_java_calling_plsql
Account
Marketing
Computer

PL/SQL procedure successfully completed.
Re: how to pass any array from java to Oracle [message #178923 is a reply to message #49378 ] Fri, 23 June 2006 07:24 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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?
Previous Topic:BIP Extension
Next Topic:log file writing
Goto Forum:
  


Current Time: Sat May 17 05:04:25 CDT 2008

Total time taken to generate the page: 0.13401 seconds
.:: Forum Home :: Site Home :: Wiki Home :: Contact :: Privacy ::.