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>

       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) {}
       }

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

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.

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.

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

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)