我在远程数据库中创建了一个名为getEmployee(id in varchar)的oracle函数,我正试图使用数据库链接从本地数据库调用它。
在getEmployee中,我试图返回一个包含员工数据的游标(表: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;但是,当我从Java代码调用这个函数时,它会给出ORA-24338: statement handle not executed错误。这是我的远程功能:
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;这是我的Java代码:
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()) {
......
}