Home » Developer & Programmer » JDeveloper, Java & XML » Return a ResultSet as a ref cursor (DB11.2.0.3)
Return a ResultSet as a ref cursor [message #596231] Thu, 19 September 2013 05:23 Go to next message
John Watson
Messages: 4672
Registered: January 2010
Location: Global Village
Senior Member
I need to design a PL/SQL procedure for running arbitrary SELECT, DML, or DDL statements against a remote instance. I can't use database links, because as well as querying RDBMS instances I also need to query an ASM instance as SYSASM, and you can't do that through a link. So I have to do it with Java.
So far, I can log onto the remote instance (ASM or RDBMS), and run any SQL no problem. I can pass the username, password, role, connect string, and the SQL statement itself into the the PL/SQL procedure as IN parameters, and then use them as java.lang.String[] arguments in the Java code to connect and execute.
The problem I have is getting the result back to PL/SQL. If I use the executeQuery() method, the query returns as a ResultSet object, and within the Java code I can use it, with System.out.println for example. Is it possible to return a ResultSet to the calling PL/SQL procedure, so that I can use it as an OUT parameter? I was wondering about using a weakly typed ref cursor?
Please excuse me if this is a stupid question, my Java skills are minimal and my PL/SQL not much better. I don't need code, just suggestions about how to proceed.

[Updated on: Thu, 19 September 2013 05:24]

Report message to a moderator

icon5.gif  Re: Return a ResultSet as a ref cursor [message #596232 is a reply to message #596231] Thu, 19 September 2013 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If I understand, you want to set a ref cursor in Java and pass it to a PL/SQL procedure.
If the ref cursor is on another database than the one where the PL/SQL procedure execute then it is not possible. You cannot use a REF CURSOR from another database (and it is also true through a database link).

If I also understand the underlying goal, you want from a database, say an administrator one, do some things in other databases. My question is then, "why do you want to do it from a database; can't you do it at OS level?" and in this case Perl (or another scripting language you want) would be a better choice than Java.

Re: Return a ResultSet as a ref cursor [message #596239 is a reply to message #596232] Thu, 19 September 2013 06:39 Go to previous messageGo to next message
John Watson
Messages: 4672
Registered: January 2010
Location: Global Village
Senior Member
Thank you for replying. It doesn't surprise me that I'm misunderstanding the technology.

Here's what I'm doing:

I have a Java procedure that logs on to a remote database, and runs a query. Java puts the query results into a ResultSet object. I want to get the results (as, I suppose, a set of rows: some sort of two dimensional structure) back to the pl/sql procedure that calls the Java procedure.

I have this working:
orcl>
orcl> create or replace procedure twocolquery(s1 varchar2,s2 varchar2,s3 varchar2,s4 varchar2)
as language java
name 'twocolquery.main(java.lang.String[])';
/  2    3    4

Procedure created.

orcl> set serveroutput on
set long 5000
call dbms_java.set_output(5000);orcl> orcl>

Call completed.

orcl> exec twocolquery('SYS AS SYSASM','oracle','jdbc:oracle:thin:@//localhost:1521/+ASM','select name,value from v$parameter where rownum < 5')
lock_name_space  null
processes  120
sessions  202
timed_statistics  TRUE

PL/SQL procedure successfully completed.

orcl> exec twocolquery('SYSTEM','oracle','jdbc:oracle:thin:@//localhost:1521/orcl','select empno,ename from scott.emp')
7369  SMITH
7499  ALLEN
7521  WARD
7566  JONES
7654  MARTIN
7698  BLAKE
7782  CLARK
7788  SCOTT
7839  KING
7844  TURNER
7876  ADAMS
7900  JAMES
7902  FORD
7934  MILLER

PL/SQL procedure successfully completed.

orcl>
To get the output, in my twocolquery.java I'm doing this:
       ResultSet rset = stmt.executeQuery(args[3]);
       try {
         while (rset.next())
           System.out.println (rset.getString(1) + "  " + rset.getString(2));   
       }
       finally {
          try { rset.close(); } catch (Exception ignore) {}
       }
but I need to do is to return the data in the ResultSet object to the pl/sql environment, perhaps as an OUT parameter of some type.

I can't do it using any OS code, because it needs to be invocable from within an application: think of a user interface, with a "system admin" menu option.
icon3.gif  Re: Return a ResultSet as a ref cursor [message #596245 is a reply to message #596239] Thu, 19 September 2013 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said you cannot manage (execute/fetch/close) a ref cursor from another database.
In this case, you can try to return a collection (but I'm not sure you can do it too, I never checked this).

Re: Return a ResultSet as a ref cursor [message #596247 is a reply to message #596239] Thu, 19 September 2013 08:07 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2444
Registered: May 2013
Location: World Wide on the Web
Senior Member
John Watson wrote on Thu, 19 September 2013 17:09
but I need to do is to return the data in the ResultSet object to the pl/sql environment, perhaps as an OUT parameter of some type.


Hi John,

I am sure that it can't be done that way, since, Callable statement doesn't have any method to set the resultset as parameter.

I have come across such situation where I demanded a Java developer to pass the resultset object as IN parameter to the PL/SQL procedure, I thought in Java there would be a method to pass as a collection(may be Oracle.table.type sort of), but it didn't work out. Let me try to get in touch with the Java developers if they have had found any alternative.

Regards,
Lalit
Re: Return a ResultSet as a ref cursor [message #596258 is a reply to message #596247] Thu, 19 September 2013 10:06 Go to previous messageGo to next message
John Watson
Messages: 4672
Registered: January 2010
Location: Global Village
Senior Member
Thank you for replying. I found this
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "CursorTry"
AS
import oracle.jdbc.*;
import java.sql.*;
public class CursorTry {
public static OracleResultSet getCursor() {
  try {
    OracleConnection conn = (OracleConnection)(new oracle.jdbc.driver.OracleDriver()).defaultConnection();
    conn.setCreateStatementAsRefCursor(true);
    OracleStatement stat = (OracleStatement)conn.createStatement();
    return (OracleResultSet)stat.executeQuery("select * from dual");
      } catch (SQLException e) {
      return null;
    } 
  }
}
/


create or replace FUNCTION  get_java_cursor
RETURN sys_refcursor AS LANGUAGE JAVA
NAME  'CursorTry.getCursor() return oracle.jdbc.OracleResultSet';
/


declare
c sys_refcursor;
v dual%rowtype;
begin
c := get_java_cursor;
fetch c into v;
dbms_output.put_line(v.dummy);
end;
/
which certainly works as written. But from you both say it won't do what I need. There must be some way to call a Java procedure, and get something back from it. That's all I want to do!
Thanks for your time.
icon5.gif  Re: Return a ResultSet as a ref cursor [message #596263 is a reply to message #596258] Thu, 19 September 2013 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But from you both say it won't do what I need.


Yes, it will... but, as far as I know, not with a remote connection (but I didn't test it in 11.2).

icon13.gif  Re: Return a ResultSet as a ref cursor [message #596273 is a reply to message #596258] Thu, 19 September 2013 14:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I tried many things but nothing worked.
Here's a summary; my local DB is MIKB and the remote one is MIKA.
First the thing that works locally: kpbr driver:
SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "CursorTry"
  2  AS
  3  import oracle.jdbc.*;
  4  import java.sql.*;
  5  public class CursorTry {
  6    public static ResultSet getCursor () throws SQLException {
  7      Connection conn = null;
  8      conn = DriverManager.getConnection("jdbc:oracle:kprb:");
  9      ((OracleConnection)conn).setCreateStatementAsRefCursor(true);
 10      Statement stmt = conn.createStatement();
 11      ResultSet rset = stmt.executeQuery("select name from v$database");
 12      return rset;
 13    }
 14  }
 15  /

Java created.

SQL> create or replace FUNCTION  get_java_cursor
  2  RETURN sys_refcursor AS LANGUAGE JAVA
  3  NAME  'CursorTry.getCursor() return ResultSet';
  4  /

Function created.

SQL> declare
  2    c sys_refcursor;
  3    v v$database.name%type;
  4  begin
  5    c := get_java_cursor;
  6    fetch c into v;
  7    dbms_output.put_line(v);
  8  end;
  9  /
MIKB2

PL/SQL procedure successfully completed.

Note: To simplify I removed all the failed cases because some Java privileges are missing or "Java session state cleared" between tests, there is no problem to fix if you encounter them.

Unfortunately "kpbr" is only local and cannot reach another database or even connection in the same database, if you try to provide other credentials it simply ignore them:
SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "CursorTry"
  2  AS
  3  import oracle.jdbc.*;
  4  import java.sql.*;
  5  public class CursorTry {
  6    public static ResultSet getCursor () throws SQLException {
  7      Connection conn = null;
  8      conn = DriverManager.getConnection("jdbc:oracle:kprb:@idontcare", "idontcare", "idontcare");
  9      ((OracleConnection)conn).setCreateStatementAsRefCursor(true);
 10      Statement stmt = conn.createStatement();
 11      ResultSet rset = stmt.executeQuery("select name from v$database");
 12      return rset;
 13    }
 14  }
 15  /

Java created.

SQL> declare
  2    c sys_refcursor;
  3    v v$database.name%type;
  4  begin
  5    c := get_java_cursor;
  6    fetch c into v;
  7    dbms_output.put_line(v);
  8  end;
  9  /
MIKB2

PL/SQL procedure successfully completed.

Let's try "thin" driver.
Unfortunately, it seems to not support REF CURSOR with both standard Java result set or Oracle one:
SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "CursorTry"
  2  AS
  3  import oracle.jdbc.*;
  4  import java.sql.*;
  5  public class CursorTry {
  6    public static ResultSet getCursor () throws SQLException {
  7      Connection conn = null;
  8      conn = DriverManager.getConnection("jdbc:oracle:thin:@//XXX:1531/mikb2.xxx", "MICHEL", "michel");
  9      ((OracleConnection)conn).setCreateStatementAsRefCursor(true);
 10      Statement stmt = conn.createStatement();
 11      ResultSet rset = stmt.executeQuery("select name from v$database");
 12      return rset;
 13    }
 14  }
 15  /

Java created.

SQL> declare
  2    c sys_refcursor;
  3    v v$database.name%type;
  4  begin
  5    c := get_java_cursor;
  6    fetch c into v;
  7    dbms_output.put_line(v);
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected a return value that is a java.sql.ResultSet 
got a java.sql.ResultSet that can not be used as a REF CURSOR

SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "CursorTry"
  2  AS
  3  import oracle.jdbc.*;
  4  import java.sql.*;
  5  public class CursorTry {
  6    public static oracle.jdbc.OracleResultSet getCursor () throws SQLException {
  7      Connection conn = null;
  8      conn = DriverManager.getConnection("jdbc:oracle:thin:@//XXX:1531/mikb2.xxx", "MICHEL", "michel");
  9      ((OracleConnection)conn).setCreateStatementAsRefCursor(true);
 10      Statement stmt = conn.createStatement();
 11      oracle.jdbc.OracleResultSet rset =
 12        (OracleResultSet)stmt.executeQuery("select name from v$database");
 13      return rset;
 14    }
 15  }
 16  /

Java created.

SQL> create or replace FUNCTION  get_java_cursor
  2  RETURN sys_refcursor AS LANGUAGE JAVA
  3  NAME  'CursorTry.getCursor() return oracle.jdbc.OracleResultSet';
  4  /

Function created.

SQL> declare
  2    c sys_refcursor;
  3    v v$database.name%type;
  4  begin
  5    c := get_java_cursor;
  6    fetch c into v;
  7    dbms_output.put_line(v);
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected a return value that is a sqlj.runtime.ResultSetIterator 
got a sqlj.runtime.ResultSetIterator that can not be used as a REF CURSOR

icon9.gif  Re: Return a ResultSet as a ref cursor [message #596277 is a reply to message #596273] Thu, 19 September 2013 14:49 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I tried to do it with "oci" driver but anyway I turn it I always got an "Invalid Oracle URL" which I don't understand as this is the one I have used for many years:
SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "CursorTry"
  2  AS
  3  import oracle.jdbc.*;
  4  import java.sql.*;
  5  public class CursorTry {
  6    public static oracle.jdbc.OracleResultSet getCursor () throws SQLException {
  7      Connection conn = null;
  8      conn = DriverManager.getConnection("jdbc:oracle:oci8:@mika", "michel", "michel");
  9      ((OracleConnection)conn).setCreateStatementAsRefCursor(true);
 10      Statement stmt = conn.createStatement();
 11      oracle.jdbc.OracleResultSet rset =
 12        (OracleResultSet)stmt.executeQuery("select name from v$database");
 13      return rset;
 14    }
 15  }
 16  /

Java created.

SQL> declare
  2    c sys_refcursor;
  3    v v$database.name%type;
  4  begin
  5    c := get_java_cursor;
  6    fetch c into v;
  7    dbms_output.put_line(v);
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.sql.SQLException: 
Invalid Oracle URL specified
ORA-06512: at "MICHEL.GET_JAVA_CURSOR", line 1
ORA-06512: at line 5

SQL> host tnsping mika

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 19-SEPT.-2013 21:49:26

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
E:\oracle\ora112\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = XXX)(PORT = 1531))) 
(CONNECT_DATA = (SERVICE_NAME = MIKA.XXX) (SERVER = DEDICATED)))
OK (60 msec)


[Updated on: Thu, 19 September 2013 14:50]

Report message to a moderator

Re: Return a ResultSet as a ref cursor [message #596306 is a reply to message #596277] Fri, 20 September 2013 03:05 Go to previous messageGo to next message
John Watson
Messages: 4672
Registered: January 2010
Location: Global Village
Senior Member
It looks as though I am going to have to say that there is a technical limitation with this approach. OEM can do it, which is why I assumed it was possible. But the OEM agent that connects and runs the SQL is not itself a pl/sql procedure.
Thank you for taking the trouble to do these tests. I hope you got something out of it, and that I can return the favour some time.
icon12.gif  Re: Return a ResultSet as a ref cursor [message #596308 is a reply to message #596306] Fri, 20 September 2013 03:22 Go to previous message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, this is far easier when you have an agent on each server...

You can do the thing, if you accept to have a PL/SQL procedure running in each database (though it cannot work with ASM one) using a pipe and waiting for your command and returning back the result through another pipe (see dbms_pipe).

Or you can write your own agent for the same thing...

Previous Topic: XML FOREST
Next Topic: XML Decode Statement
Goto Forum:
  


Current Time: Mon Oct 20 18:26:22 CDT 2014

Total time taken to generate the page: 0.08453 seconds