SQL 使用数据库链接调用oracle函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5899798/
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
Calling oracle function using database link
提问by Kapila Witharana
I have created a oracle function called getEmployee(id in varchar)in my remote database and I'm trying to call it from my local database using database link.
我创建了一个getEmployee(id in varchar)在我的远程数据库中调用的 oracle 函数，我正在尝试使用数据库链接从我的本地数据库中调用它。
In getEmployee, I'm trying to return a cursor with employee data.(Table: Employee (ID, Name, address)):
在 中getEmployee，我试图返回一个带有员工数据的游标。（表：员工（ID、姓名、地址））：
SELECT schema.getEmployee@dblink(id)
FROM DUAL;
How can I get the result set with column name (ID, Name, address)?
如何获取带有列名（ID、名称、地址）的结果集？
According to Contrad, I changed my local function like this;
根据 Contrad 的说法，我像这样更改了本地函数；
FUNCTION LocalGetEmployee(ID in varchar2)
RETURN Schema.SomeRefCursor
AS
OUTPUT Schema.SomeRefCursor;
BEGIN
OUTPUT := schema.getEmployee@dblink(ID);
RETURN OUTPUT;
END;
But, when I call this function from Java Code, the following error is raised:
但是，当我从 Java 代码调用此函数时，会引发以下错误：
"ORA-24338: statement handle not executed"
“ORA-24338: 语句句柄未执行”
回答by Andrew
Fetching the Ref Cursor at Remote site:
在远程站点获取参考光标：
Let's say we have two sites involved in Distributed transaction, Server1 and Server2. The Ref Cursor opened on Server1 procedure, cannot be fetched at Server2 site. If we try to fetch this cursor oracle raises an exception:
假设我们有两个站点涉及分布式事务，Server1 和 Server2。在 Server1 过程中打开的 Ref Cursor，无法在 Server2 站点上获取。如果我们尝试获取此游标，oracle 会引发异常：
[ORA-02055: distributed update operation failed; rollback required
ORA-24338: statement handle not executed]
“We cannot use the Ref Cursor over DBLink”
“我们不能通过 DBLink 使用 Ref Cursor”
Solutions:
解决方案：
- Use PL-SQL Data table. OR
- Provide select grant and use select command over DBLink from initiator site instead of opening the Cursor.
- 使用 PL-SQL 数据表。或者
- 从启动器站点提供选择授权并在 DBLink 上使用选择命令，而不是打开光标。
Source: Distributed transaction in Oracle (Over Oracle DBLink)
回答by Dan
As far as I can tell your question isn't really about database links but rather how, from a Java client, to call a function that returns a cursor and retrieve the data from that cursor. I believe the only way to do this in Java is to wrap the function call in a bit of "procedural" code. I don't have Oracle in front of me so this is some guesswork:
据我所知，您的问题实际上并不是关于数据库链接，而是如何从 Java 客户端调用返回游标的函数并从该游标中检索数据。我相信在 Java 中做到这一点的唯一方法是将函数调用包装在一些“过程”代码中。我面前没有 Oracle，所以这是一些猜测：
String fncall = "begin ? := schema.getEmployee@dblink(?) end";
CallableStatement stm = con.prepareCall(fncall);
stm.registerOutParameter(1, Types.CURSOR);
stm.setInt(2, 123);
stm.execute();
ResultSet rs = (ResultSet) stm.getObject(1);
