Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: returning ResultSet from Java Stored Procedure
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 }
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