oracle 通过数据库链接获取 ResultSet/RefCursor
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2549417/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting a ResultSet/RefCursor over a database link
提问by JonathanJ
From the answers to calling a stored proc over a dblinkit 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.
从通过 dblink调用存储过程的答案来看，如果您通过远程数据库链接进行 SP 调用，似乎不可能调用存储过程并取回 ResultSet/RefCursor。我们也在使用 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.
我们可以通过链接成功获取单值结果，并且可以成功调用 SP 并在本地获取结果，但是当从远程 DB 读取 ResultSet 时，我们得到相同的“ORA-24338：语句句柄未执行”错误。
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:
可在本地运行但不能远程运行的示例 Java 代码：
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" ));
}
}
采纳答案by Gary Myers
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.
选项 1. 从 Java 直接连接到远程数据库，而不是通过本地数据库。更简单，但由应用程序来协调两个单独的事务。如果一个数据库只用于读取而不是写入，我会走这条路。
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.
选项 2. 使用数据库链接在本地数据库中进行直接查询。
Option 3. As (2), but hide the query in a view (or synonym) stored on the local database.
选项 3. 与 (2) 相同，但将查询隐藏在存储在本地数据库中的视图（或同义词）中。
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.
选项 4。如果结果集足够小，您可以让本地数据库上的过程调用远程数据库上的过程。远程过程会将结果作为 XML 或结构化 CLOB（例如 JSON）返回，这些结果可以由本地过程或 java 层“解码”。
