RE: Passing arrays/objects to Java Stored Procedures?

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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Steve Rospo
Sent: Monday, March 28, 2005 2:18 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx           (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

Other related posts: