RE: Passing arrays/objects to Java Stored Procedures?

  • From: "Anthony Molinaro" <amolinaro@xxxxxxxx>
  • To: <srospo@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 29 Mar 2005 09:43:15 -0500

  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
    l_array demo_array :=3D demo_array();
      bulk collect into l_array=20
      from all_objects=20
     where rownum <=3D p_num;=20
    return l_array;

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 oracle.sql.*;
import oracle.jdbc.*;

public class arrayDemo
  public static void main( String args[] ) throws IOException,
    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 =
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];
    // make the connection between oracle <-> jdbc type
    demo_obj[0] =3D new oracle.sql.STRUCT (new
    // the function returns an array (collection) of the demo_obj
    // make the connection between that array(demo_array) and a jdbc
    oracle.sql.ARRAY demo_array =3D new oracle.sql.ARRAY (new

    // call the plsql function
    OracleCallableStatement cs =3D
(OracleCallableStatement)conn.prepareCall ("BEGIN ? :=3D F_DEMO(?);

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


    // 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 ( {

        // 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
    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,

-----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
but I can't find more complex examples with collections or objects.

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
message and any attachments. Thank you.


Other related posts: