Re: returning ResultSet from Java Stored Procedure

From: bung ho <bung_ho_at_hotmail.com>
Date: 9 Jan 2003 20:55:29 -0800
Message-ID: <567a1b1.0301092055.63135fb4@posting.google.com>

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:799226268269

that example, however returns an array of varchar2s. so then, what exactly are you putting in this Vector that you would like to return? since you say that the values aren't all the same type (ps--remember an Object[] can still hold objects of differing types), you'll need to use objects that map to custom SQL types. i really don't know if i am understanding what you are trying to do exactly--but i'll assume you are basically trying to return a resultset that represents the result of a query. to do this you'd need a scalar object type that represents a single row of the results, and then an array type of those scalars for the "resultset."

SQL> create type duhtype as object (x int, y varchar2(50));   2 /

Type created.

SQL> create type duharray as table of duhtype;   2 /

Type created.

SQL> create or replace and compile java source named "Duh" as   2
  3 import oracle.sql.*;
  4 import java.sql.*;
  5 import oracle.jdbc.driver.*;
  6 import java.math.BigDecimal;
  7
  8 public class Duh {
  9
 10
 11 public Duh() {
 12 }
 13
 14 public static ARRAY getStuff() {
 15

 16      try {
 17      Connection conn = new OracleDriver().defaultConnection();
 18      StructDescriptor duhdesc =
 19        StructDescriptor.createDescriptor("DUHTYPE", conn);
 20
 21      // create a fake "row"
 22      Object[] myduh0 = new Object[2];
 23      myduh0[0] = new BigDecimal(10);
 24      myduh0[1] = "woof";
 25
 26      // create another
 27      Object[] myduh1 = new Object[2];
 28      myduh1[0] = new BigDecimal(20);
 29      myduh1[1] = "meow";
 30
 31      // descriptors for the objects
 32      STRUCT duh0 = new STRUCT (duhdesc, conn, myduh0);
 33      STRUCT duh1 = new STRUCT (duhdesc, conn, myduh1);
 34
 35      // make an array of those "rows"
 36      STRUCT[] myduhtable = new STRUCT[2];
 37      myduhtable[0] = duh0;
 38      myduhtable[1] = duh1;
 39
 40      ArrayDescriptor descriptor =
 41        ArrayDescriptor.createDescriptor("DUHARRAY", conn);
 42      return new ARRAY(descriptor, conn, myduhtable);
 43      } catch (Exception ex) {
 44          ex.printStackTrace();
 45          return null;
 46      }

 47
 48
 49 }
 50 }
 51
 52 /

Java created.

SQL> create or replace function get_stuff return duharray as language java
  2 name 'Duh.getStuff() return oracle.sql.ARRAY.ARRAY';   3 /

Function created.

SQL> select * from TABLE(cast(get_stuff() as duharray));

         X Y

---------- --------------------------------------------------
        10 woof
        20 meow

whoop-de-doo, a result set. in PL/SQL, all this nonsense can be replaced with pretty much just

open refcur as 'select ... from ....';

asundaram <member22031_at_dbforums.com> wrote in message news:<2368823.1042144292_at_dbforums.com>...
> I have to user Java Stored Procs. It would seem to me that returning
> multiple rows from a Java Stored Proc would be something that many
> people have done before. Yet I haven't found any examples/documentation
> with regard to this issue. If I were to return a Vector, which would be
> fine, what SQL type would I map a vector object to? I couldn't return an
> array because all the values in my array won't be the same type.
Received on Thu Jan 09 2003 - 22:55:29 CST