Dear team,
We are working on java stored procedure that returns a java.sql.ResultSet.below is the one.
set define off
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "CursorTry"
AS
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.io.FileInputStream;
import oracle.jdbc.*;
public class CursorTry
{
public static ResultSet test()
{
String userName =null;
String passCode = null;
String url = null;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
Properties localProperties = new Properties();
localProperties.load(new FileInputStream("/opt/CompDb.properties"));
String str1 = localProperties.getProperty("driver");
if (str1 != null) {
Class.forName(str1);
}
url = localProperties.getProperty("url");
userName = localProperties.getProperty("username");
passCode = localProperties.getProperty("password");
conn = DriverManager.getConnection(url, userName, passCode);
((OracleConnection)conn).setCreateStatementAsRefCursor(true);
stmt = conn.createStatement();
((OracleStatement)stmt).setRowPrefetch(1);
rs = stmt.executeQuery("SELECT ERROR_MESSAGE FROM V_TRANSACTIONS");
int rows = 0;
if(rs.next())
{
System.out.println(rs.getString(1));
}
return rs;
}
catch (Exception e)
{
return null;
}
}
}
/
the above procedure is invoked from pl/sql function.
create or replace package testTypes
as
type cursortestType is ref cursor;
function test return cursortestType;
end testTypes;
/
create or replace package body testTypes
as
function test return cursortestType
as
language java
name 'CursorTry.test() return java.sql.ResultSet';
end testTypes;
/
When the above function is invoked from anonymous block we are gettign error "ORA-01001: invalid cursor".Please suggest
declare
type cursortestType is ref cursor;
rc cursortestType;
v_ret_1 varchar2(100);
v_ret_2 varchar2(100);
begin
rc:=testTypes.test();
loop
fetch rc into v_ret_1 ;
exit when rc%notfound;
end loop;
close rc;
exception
when others then
dbms_output.put_line(SQLERRM);
end;
/