Calling an oracle function from Java

I have created a oracle function called getEmployee(id in varchar) in my remote database and I'm trying to call it from my local database using database link.

In getEmployee, I'm trying to return a curs开发者_如何转开发or with employee data.(Table: Employee (ID, 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 ORA-24338: statement handle not executed error. 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()) {
  ......
}

It makes no sense to me to use a refcursor created in a remote database. The dblink reference will open a session on the remote instance, create the cursor, and promptly go away when the remote function call terminates. Hence your ORA-24338 error on the local instance - the refcursor doesn't point to anything valid.

Even if you could leave the remote session open, what would the refcursor be pointing to? Some memory area on a remote database?