Call oracle function from Java

Question

I created an oracle function called getEmployee(id in varchar) in my remote database and I am trying to call it from my local database using the database link.

In getEmployee , I am trying to return a cursor with employee data. (Table: Employee (identifier, name, address)):

 FUNCTION LocalGetEmployee(ID in varchar2) RETURN Schema.SomeRefCursor AS OUTPUT Schema.SomeRefCursor; BEGIN OUTPUT := schema.getEmployee@dblink (ID) ; RETURN OUTPUT; END; 

But when I call this function from Java code, it gives error ORA-24338: statement handle not executed . Here is my remote function:

 CREATE OR REPLACE FUNCTION GETEMPLOYEE ( IN_ID IN VARCHAR2 ) RETURN TYPES.RECORD_CURSOR AS RESULT_CURSOR TYPES.RECORD_CURSOR; BEGIN OPEN RESULT_CURSOR FOR SELECT ID, NAME, ADDRESS FROM EMPLOYEE WHERE ID = IN_ID; RETURN RESULT_CURSOR; END GETEMPLOYEE; 

Here is my Java code:

 String fncall = "{call ? := schema.LocalGetEmployee(?)}"; CallableStatement stm = con.prepareCall(fncall); stm.registerOutParameter(1, Types.CURSOR); stm.setInt(2, 123); stm.execute(); ResultSet rs = (ResultSet) stm.getObject(1); while(rs.next()) { ...... } 

Answer 1

It makes no sense to me to use refcursor created in a remote database. The dblink link will open a session on the remote instance, create a cursor, and immediately exit when the call to the remote function ends. Therefore, your ORA-24338 error in the local instance - refcursor does not indicate anything valid.

Even if you can leave an open session open, what will refcursor refer to? Is there some memory in the remote database?