Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Passing arrays/objects to Java Stored Procedures?

RE: Passing arrays/objects to Java Stored Procedures?

From: Anthony Molinaro <>
Date: Tue, 29 Mar 2005 09:43:15 -0500
Message-ID: <>

  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(); begin

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;

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, 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);

// 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 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());


// 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-----
[] On Behalf Of Steve Rospo Sent: Monday, March 28, 2005 2:18 PM
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.

Stephen Rospo Principal Software Architect Vallent Corporation (formerly Watchmark-Comnitel) (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.

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

Original text of this message