RE: Passing arrays/objects to Java Stored Procedures?

From: Anthony Molinaro <amolinaro_at_wgen.net>
Date: Tue, 29 Mar 2005 09:43:15 -0500
Message-ID: <D17DB304A9F42B4787B68861F9DAE61CD10235@wgdc02.wgenhq.net>

Steve,
  I think the most helpful thing to remember   is things like oracle objects and records can easily   be mimicked with C structs.

  So, when returning an array of those types   you just need to use an array of structs or an   array of objects (you just need to map a java type   to the db type and you're ready to go).

  Here's simple example for JDBC (I don't use java=20   procs in the db, but I imagine to convert   my example to a java stored proc is trivial).

Here is the oracle object, array, and a function That returns the array of objects:

create type demo_obj as object( val1 number, val2 number, val3 number ); /

create type demo_array as table of demo_obj; /

create or replace function f_demo ( p_num number ) return demo_array=20
as=20

    l_array demo_array :=3D demo_array(); begin

    select
demo_obj(round(dbms_random.value(1,1000)),round(dbms_random.value(1,1000 )),round(dbms_random.value(1,1000)))=20

      bulk collect into l_array=20
      from all_objects=20
     where rownum <=3D p_num;=20

    return l_array;
end;
/

here's a little jdbc demo (just a single java class). Pass in a number at the command line to change the number or rows returned.

---

import java.lang.*;
import java.sql.*;
import java.io.*;

import oracle.sql.*;
import oracle.jdbc.*;

public class arrayDemo
{
  public static void main( String args[] ) throws IOException, SQLException
  {
    DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver() );

    Connection conn =3D DriverManager.getConnection ( "jdbc:oracle:oci:@YOUR_DB", "SCOTT", "TIGER" );

    conn.setAutoCommit( false );

    Integer numRows =3D new Integer(args[0]); // variable to accept the number of rows to return (passed at runtime)

    Object attributes[] =3D new Object[3]; // "attributes" of the = demo_obj
in the db

// object demo_obj in the db has 3 fields, all numeric
// create an array of objects which has 3 attributes
// we are building a template of that db object
// the values i pass below are just generic numbers, 1,2,3 mean
nothing really

    attributes[0] =3D new Integer(1);       =20
    attributes[1] =3D new Integer(2);
    attributes[2] =3D new Integer(3);

// this will represent the data type DEMO_OBJ in the database
    Object demo_obj[] =3D new Object[1];     =20
// make the connection between oracle <-> jdbc type
    demo_obj[0] =3D new oracle.sql.STRUCT (new oracle.sql.StructDescriptor("DEMO_OBJ",conn),conn,attributes);

   =20
// the function returns an array (collection) of the demo_obj
// make the connection between that array(demo_array) and a jdbc
array

    oracle.sql.ARRAY demo_array =3D new oracle.sql.ARRAY (new oracle.sql.ArrayDescriptor("DEMO_ARRAY",conn),conn,demo_obj);

// call the plsql function

    OracleCallableStatement cs =3D
(OracleCallableStatement)conn.prepareCall ("BEGIN ? :=3D F_DEMO(?); END;");
// bind variables

    cs.registerOutParameter (1,OracleTypes.ARRAY,"DEMO_ARRAY");     cs.setInt (2,numRows.intValue());

    cs.execute();

// get the results of the oracle array into a local jdbc array=20
    oracle.sql.ARRAY results =3D (oracle.sql.ARRAY)cs.getArray(1);

// flip it into a result set

    ResultSet rs =3D results.getResultSet();

// process the result set

    while (rs.next()) {

        // since it's an array of objects, get and display the value of the underlying object

        oracle.sql.STRUCT obj =3D (STRUCT)rs.getObject(2);

        Object vals[] =3D obj.getAttributes();

        System.out.println (vals[0] + " " + vals[1] + " " + vals[2]);     }

// cleanup

    cs.close();
    conn.close(); =20
  }
}

---

I just ran it, works fine. I imagine it works the same way for a java stored proc with maybe some small modifications.

Good luck,
  Anthony

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Steve Rospo Sent: Monday, March 28, 2005 2:18 PM
To: oracle-l_at_freelists.org
Subject: Passing arrays/objects to Java Stored Procedures?

Does anyone have examples of passing PL/SQL arrays and/or objects into Java Stored Procedures? I can find plenty of examples of passing scalars
but I can't find more complex examples with collections or objects.

--=20
Stephen Rospo Principal Software Architect Vallent Corporation (formerly Watchmark-Comnitel) Stephen.Rospo_at_vallent.com (425)564-8145

This email may contain confidential information. If you received this in error, please notify the sender immediately by return email and delete this
message and any attachments. Thank you.

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 29 2005 - 09:47:01 CST