title: Calling an oracle function from Java
body: <p>I have created a oracle function called <code>getEmployee(id in varchar)</code> in my remote database and I'm trying to call it from my local database using database link.</p>
  
  <p>In <code>getEmployee</code>, I'm trying to return a cursor with employee data.(Table: Employee (ID, Name, address)):</p>
  
  <pre><code>FUNCTION LocalGetEmployee(ID in varchar2)
  RETURN Schema.SomeRefCursor AS OUTPUT Schema.SomeRefCursor;
  
  BEGIN 
  
    OUTPUT := schema.getEmployee@dblink(ID) ;    
  
    RETURN OUTPUT;
  
  END;
  </code></pre>
  
  <p>But, when I call this function from Java Code, it gives <code>ORA-24338: statement handle not executed</code> error. Here is my remote function:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Here is my Java Code:</p>
  
  <pre><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()) {
    ......
  }
  </code></pre>