使用数据库链接调用oracle函数

发布于 2024-11-05 04:01:31 字数 610 浏览 4 评论 0原文

我在远程数据库中创建了一个名为 getEmployee(id in varchar) 的 Oracle 函数，并尝试使用数据库链接从本地数据库调用它。

在getEmployee中，我试图返回包含员工数据的游标。（表：员工（ID，姓名，地址））：

SELECT schema.getEmployee@dblink(id) 
  FROM DUAL;

如何获取包含列名称（ID，姓名，地址）？

根据 Contrad 的说法，我这样改变了我的本地功能；

FUNCTION LocalGetEmployee(ID in varchar2)
RETURN Schema.SomeRefCursor
AS  

OUTPUT Schema.SomeRefCursor;

BEGIN 

  OUTPUT := schema.getEmployee@dblink(ID);

  RETURN OUTPUT;
END;

但是，当我从 Java 代码调用此函数时，会出现以下错误：

“ORA-24338: 语句句柄未执行”

原文

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.

In getEmployee, I'm trying to return a cursor with employee data.(Table: Employee (ID, Name, address)):

SELECT schema.getEmployee@dblink(id) 
  FROM DUAL;

How can I get the result set with column name (ID, Name, address)?

According to Contrad, I changed my local function like this;

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:

"ORA-24338: statement handle not executed"

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

策马西风 2024-11-12 04:01:31

在远程站点获取引用游标：

假设我们有两个站点涉及分布式事务：Server1 和 Server2。在 Server1 过程中打开的引用游标无法在 Server2 站点获取。如果我们尝试获取此游标，oracle 会引发异常：

[ORA-02055: distributed update operation failed; rollback required
 ORA-24338: statement handle not executed]

“我们无法通过 DBLink 使用引用游标”

解决方案：

使用 PL-SQL 数据表。或者
提供选择授权并通过 DBLink 使用选择命令
启动器站点而不是打开光标。

来源：Oracle 中的分布式事务（通过 Oracle DBLink）

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:

[ORA-02055: distributed update operation failed; rollback required
 ORA-24338: statement handle not executed]

“We cannot use the Ref Cursor over DBLink”

Solutions:

Use PL-SQL Data table. OR
Provide select grant and use select command over DBLink from
initiator site instead of opening the Cursor.

Source: Distributed transaction in Oracle (Over Oracle DBLink)

回复收藏 0 原文

囚你心 2024-11-12 04:01:31

据我所知，您的问题实际上并不是关于数据库链接，而是如何从 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);

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:

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

回复收藏 0 原文

~没有更多了~