From the responses to the stored procedure call via dblink, it seems that it is impossible to call the stored procedure and return ResultSet / RefCursor if you are making an SP call on a remote database link. We also use Oracle 10g.
We can successfully get the results by a single value by reference and we can successfully call SP and get the results locally, but we get the same error "ORA-24338: statement handle not execute" when reading the ResultSet from the remote database.
My question is, are there any workarounds for a stored procedure? Is a general idea the best solution? Piping Rows?
An example of a stored procedure:
CREATE OR REPLACE PACKAGE BODY example_SP
IS
PROCEDURE get_terminals(p_CD_community IN community.CD_community%TYPE,
p_cursor OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_cursor FOR
SELECT cd_terminal
FROM terminal t, community c
WHERE c.cd_community = p_CD_community
AND t.id_community = c.id_community;
END;
END example_SP;
/
Example Java code that works locally but not remotely:
Connection conn = DBConnectionManagerFactory.getDBConnectionManager().getConnection();
CallableStatement cstmt = null;
ResultSet rs = null;
String community = "EXAMPLE";
try
{
cstmt = conn.prepareCall("{call example_SP.get_terminals@remote_address(?,?)}");
cstmt.setString(1, community);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.execute();
rs = (ResultSet)cstmt.getObject(2);
while (rs.next())
{
LogUtil.getLog().logInfo("Terminal code=" + rs.getString( "cd_terminal" ));
}
}