Returning a result set from a Java stored procedure through SQL "select * from"

Question

Is it possible to get the result from the java (oracle) stored procedure directly through the SQL select * from statement?

In the database, I will have a java stored procedure / function that, when called, returns a multi-column multi-line result set.
I would like to access these results directly through the select * from [table] statement.

Thus, the Java stored procedure should behave like a table.
In MySQL, it should be possible (but not Java stored procedures): SELECT col1 FROM (EXEC proc1)

Is this possible in oracle where proc1 is a Java stored procedure?

Answer 1

This answer on another forum may help you.

Look at the sample at the bottom of the post to find out how to choose from the collection returned by the Java method (it can also be a Java stored procedure).

Here is an example of how to do this with a Java stored procedure

1) Create a DB object to determine the type of returned rows:

 create type try_obj as object ( field_a number, field_b varchar2(10) ) / create type try_obj_tab as table of try_obj / 

2) Create a Java class in the database with a static method (GetSampleResult) that returns a collection

 create or replace and compile java source named QueryReturn as import java.sql.*; import java.util.*; import oracle.jdbc.*; import oracle.jdbc.pool.OracleDataSource; import oracle.sql.*; public class QueryReturn implements ORADataFactory,ORAData{ private NUMBER field1; private CHAR field2; public QueryReturn(OracleConnection conn,int n,String c) throws SQLException { field1 = new NUMBER(n); field2 = new CHAR(c,oracle.sql.CharacterSet.make(conn.getStructAttrCsId())); } public QueryReturn(NUMBER n, CHAR c) { field1 = n; field2 = c; } public QueryReturn(Object[] attributes) { this( (NUMBER) attributes[0], (CHAR) attributes[1] ); } public QueryReturn(Datum d) throws SQLException { this(((STRUCT) d).getOracleAttributes()); } public ORAData create(Datum d, int sqlType) throws SQLException { if (d == null) return null; else { return new QueryReturn(d); } } public STRUCT toSTRUCT(Connection conn) throws SQLException { StructDescriptor sd = StructDescriptor.createDescriptor("TRY_OBJ", conn); Object [] attributes = { field1,field2 }; return new STRUCT(sd, conn, attributes); } public Datum toDatum(Connection conn) throws SQLException { return toSTRUCT(conn); } public static ARRAY GetSampleResult() throws SQLException, ClassNotFoundException { // initialize the connection OracleConnection conn = null; conn = (OracleConnection) (new oracle.jdbc.OracleDriver()).defaultConnection(); // create the return java array // There will be two Rows // 1 abc // 2 dce QueryReturn javaArray[] = { new QueryReturn(conn,1,"abc"), new QueryReturn(conn,2,"dce") }; // Map the java class to the Oracle type Map map = conn.getTypeMap(); map.put("TRY_OBJ", Class.forName("QueryReturn")); ArrayDescriptor jTryObjArrayDesc = ArrayDescriptor.createDescriptor ( "TRY_OBJ_TAB", conn ); // create an Oracle collection on client side to use as parameter ARRAY oracleCollection = new ARRAY(jTryObjArrayDesc,conn,javaArray); return oracleCollection; } } 

3) Create a Wrap to use the Java stored procedure in a function

 create or replace function GetSampleResult return try_obj_tab AS LANGUAGE JAVA NAME 'QueryReturn.GetSampleResult() return oracle.sql.ARRAY'; 

4) Show result

 SQL> select * 2 from table(GetSampleResult()) 3 / FIELD_A FIELD_B ---------- ---------- 1 abc 2 dce SQL> 

Answer 2

Write SSP (SQL Stored Procedure) to invoke JSP (Java Stored Procedure), and then use that SSP in the query. Simple eh.

Next, see CallableStatement .

Answer 3

I have never used it in conjunction with Java calling a stored procedure, but I assume that it should be possible to use pipelined functionality in recent Oracle databases.

See here or google / bing to find out more.

Answer 4

Tom Kite says: " You cannot return the result set from the java stored procedure in plsql. "

Answer 5

I am afraid that this is impossible. But if your database supports the functionality of selecting data from a stored procedure as you mentioned, you can create a view and select from it.

In either case, Oracle can return the result set from the stored procedure, and you can access it from Java. See this link