Getting a ResultSet/RefCursor over a database link

Question

From the answers to http://stackoverflow.com/questions/1122175/calling-a-stored-proc-over-a-dblink it seems that it is not possible to call a stored procedure and get the ResultSet/RefCursor back if you are making the SP call across a remote DB link. We are also using Oracle 10g.

We can successfully get single value results across the link, and can successfully call the SP and get the results locally but we get the same 'ORA-24338: statement handle not executed' error when reading the ResultSet from the remote DB.

My question - is there any workaround to using the stored procedure? Is a shared view a better solution? Piped rows?

Sample 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;
/

Sample 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" ));
        }

    }

Answer 1

Option 1. Go for a direct connection from Java to the remote database rather than going through the local database. Simpler, but it is up to the application to co-ordinate the two separate transactions. If one database is just used for reads and not writes, I'd go this route.

You can use with a straight query or a stored procedure and ref cursor. I'd generally go with the former unless there is a good reason to add in a stored procedure layer.

Option 2. Go for a direct query in the local database using the database link.

Option 3. As (2), but hide the query in a view (or synonym) stored on the local database.

Option 4. If the result set is small enough, you could have a procedure on the local database call a procedure on the remote database. The remote procedure would return the result as XML or a structured CLOB (eg JSON) which could be 'decoded' by either the local procedure or the java layer.