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: 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 #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: 9077
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 messageGo to next 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?
Re: how to pass any array from java to Oracle [message #580487 is a reply to message #315714] Mon, 25 March 2013 06:48 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Hi All,

How to deal with If I want pass key value pair similar to array of objects.Like
key_name   value_name
10         Accounting
20         Sales
30         Finance



The above example of Barbara is working fine.But how to extend it to achieve this.
Regards,
Nathan
Re: how to pass any array from java to Oracle [message #580563 is a reply to message #580487] Mon, 25 March 2013 16:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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
/

Re: how to pass any array from java to Oracle [message #580783 is a reply to message #580563] Thu, 28 March 2013 02:19 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Thank You very much Barbara madam for such a nice and detailed explanation,It worked fine for me.
I have gone through AskTom site there he mentioned we have to go for JPublisher, apart from that no solution is there.
But you proved him wrong.

Just a personal question in return you are getting nothing,but still helping why? Cool

Regards,
Nathan
Re: how to pass any array from java to Oracle [message #580784 is a reply to message #580783] Thu, 28 March 2013 02:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
sss111ind wrote on Thu, 28 March 2013 00:19

... Just a personal question in return you are getting nothing,but still helping why? ...


I am retired. I like puzzles. To me, solving programming problems is like solving puzzles. I browse the forums and do whatever I have time for that looks interesting.
Re: how to pass any array from java to Oracle [message #580785 is a reply to message #580783] Thu, 28 March 2013 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Just a personal question in return you are getting nothing,but still helping why?


Note that no one helping in OraFAQ get nothing to help people and this is one of the reasons why those who ask for help should always post accordingly to the guide and immediately answer our questions.

Regards
Michel
icon14.gif  Re: how to pass any array from java to Oracle [message #580885 is a reply to message #580785] Fri, 29 March 2013 05:32 Go to previous message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member


We are existing and the world is existing because of some worthful peoples exist.

Regards,
Nathan

[Updated on: Fri, 29 March 2013 09:56]

Report message to a moderator

Previous Topic: Lib path
Next Topic: Require help on Array of Nested tables and Oracle Object type
Goto Forum:
  


Current Time: Fri Mar 29 07:58:27 CDT 2024