通过数据库链接获取ResultSet/RefCursor [英] Getting a ResultSet/RefCursor over a database link

问题描述

从答案到通过dblink调用存储的proc 如果通过远程DB链接进行SP调用，似乎不可能调用存储过程并取回ResultSet/RefCursor.我们也在使用Oracle 10g.

From the answers to 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.

我们可以成功地通过链接获取单值结果，并且可以成功调用SP并在本地获取结果，但是当从远程数据库读取ResultSet时，会出现相同的"ORA-24338:语句句柄未执行"错误.

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?

样本存储过程:

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

在本地但不能在远程工作的Java代码示例:

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

    }

推荐答案

选项1.进行从Java到远程数据库的直接连接，而不是通过本地数据库.比较简单，但是由应用程序来协调两个单独的事务.如果一个数据库仅用于读取而不是写入，那么我会走这条路.

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.

您可以与直接查询或存储过程和ref游标一起使用.除非有充分的理由添加存储过程层，否则我通常会选择前者.

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.

选项2.使用数据库链接在本地数据库中直接查询.

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

选项3.与(2)一样，但是将查询隐藏在本地数据库中存储的视图(或同义词)中.

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

选项4.如果结果集足够小，则可以在本地数据库上有一个过程在远程数据库上有一个过程.远程过程将结果返回为XML或结构化的CLOB(例如JSON)，可以由本地过程或Java层解码".

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.

这篇关于通过数据库链接获取ResultSet/RefCursor的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！