SQL & PL/SQL

Hi All,

I'm working on a requirement to fetch JDBC result from Java stored procedure and displaying as plsql function.

Here is my code

Below procedure is responsible for fetching result set from java

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "xxjavajdbcsourcerset"

AS

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class xxjavajdbcsourcerset

{

public static void xxjdbcproc(ResultSet[] rset1)

{

 String s1="";

String driver = "oracle.jdbc.driver.OracleDriver";

String SQLSTMT = 

            "SELECT OWNER,OBJECT\_NAME,OBJECT\_TYPE,LAST\_DDL\_TIME,STATUS FROM all\_objects WHERE rownum\<50'" ;

 Connection conn=null;

          try {

            Class.forName(driver);

            System.out.println(" SearchBtn  Class.forName(driver)");

        } catch (ClassNotFoundException e) {

            // TODO

            System.out.println("   Class.forName(driver)" + e);

        }

        try

        {

conn = DriverManager.getConnection("jdbc:oracle:thin:@10.9.7.88:1521/orcl1d","xx","xx");

}

catch (SQLException sqle)

{

System.out.println(" SQLException Class.forName(driver)" + sqle);

}

if (conn!=null)

{

try{

PreparedStatement pstmt;

pstmt = conn.prepareStatement(SQLSTMT);

pstmt.setFetchSize(10000);

ResultSet rs = pstmt.executeQuery();

rset1[0] = rs;

}

catch (SQLException e)

{

System.out.println(" SQLException Class.forName(driver)" + e);

}

}

}

};

/

SHOW ERR;

/

Now to access above procedure i have created function in below fashion which is giving me error

CREATE PROCEDURE result_set()

RESULT (OWNER person_name_t)

DYNAMIC RESULT SETS 1

EXTERNAL NAME

 'xxjavajdbcsourcerset.xxjdbcproc(\[Ljava/sql/ResultSet;)V'

LANGUAGE JAVA;

/

show err;

/

after creation above function , i have tried to access above function through below query

SELECT apps.result_set() AS "Employees" FROM dual

i end up getting an error