通过 dblink 调用存储过程

发布于 2024-07-26 17:05:36 字数 1104 浏览 8 评论 0原文

我正在尝试通过数据库链接调用存储过程。 代码看起来像这样:

declare
       symbol_cursor  package_name.record_cursor;
       symbol_record  package_name.record_name;
begin
       symbol_cursor := package_name.function_name('argument');
loop
       fetch symbol_cursor into symbol_record;
       exit when symbol_cursor%notfound;
       -- Do something with each record here, e.g.:
       dbms_output.put_line( symbol_record.field_a );
end loop;

CLOSE symbol_cursor;

当我从 package_name 所属的同一数据库实例和架构运行此代码时,我能够正常运行它。 但是,当我通过数据库链接运行此命令(对存储的过程名称进行必要的修改等)时,我收到 Oracle 错误:ORA-24338:语句句柄未执行。

此代码通过 dblink 的修改版本如下所示:

declare
       symbol_cursor  package_name.record_cursor@db_link_name;
       symbol_record  package_name.record_name@db_link_name;
begin
       symbol_cursor := package_name.function_name@db_link_name('argument');
loop
       fetch symbol_cursor into symbol_record;
       exit when symbol_cursor%notfound;
       -- Do something with each record here, e.g.:
       dbms_output.put_line( symbol_record.field_a );
end loop;

CLOSE symbol_cursor;

I am trying to call a stored procedure over a database link. The code looks something like this:

declare
       symbol_cursor  package_name.record_cursor;
       symbol_record  package_name.record_name;
begin
       symbol_cursor := package_name.function_name('argument');
loop
       fetch symbol_cursor into symbol_record;
       exit when symbol_cursor%notfound;
       -- Do something with each record here, e.g.:
       dbms_output.put_line( symbol_record.field_a );
end loop;

CLOSE symbol_cursor;

When I run this from the same DB instance and schema where package_name belongs to I am able to run it fine. However, when I run this over a database link, (with the required modification to the stored proc name, etc) I get an oracle error: ORA-24338: statement handle not executed.

The modified version of this code over a dblink looks like this:

declare
       symbol_cursor  package_name.record_cursor@db_link_name;
       symbol_record  package_name.record_name@db_link_name;
begin
       symbol_cursor := package_name.function_name@db_link_name('argument');
loop
       fetch symbol_cursor into symbol_record;
       exit when symbol_cursor%notfound;
       -- Do something with each record here, e.g.:
       dbms_output.put_line( symbol_record.field_a );
end loop;

CLOSE symbol_cursor;

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

扫码二维码加入Web技术交流群

发布评论

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

评论(1

绅刃 2024-08-02 17:05:36

从你的另一个问题中,我记得 package_name.record_cursor 是一个引用游标类型。 引用游标是仅在创建它的数据库中有效的内存句柄。换句话说,您无法在远程数据库中创建引用游标并尝试从中获取本地数据库。

如果您确实需要处理本地数据库中的数据并且表必须保留在远程数据库中,那么您可以将包“package_name”移动到本地数据库中,并让它通过以下方式对远程数据库中的表执行查询:数据库链接。

From another of your questions I remember package_name.record_cursor to be a ref cursor type. A ref cursor is a memory handle only valid in the database it was created in. In other words, you cannot create a ref cursor in your remote db and try to fetch from it your local db.

If you really need to process the data in your local db and the tables have to stay in the remote db, then you could move the package "package_name" into your local db and have it execute the query on tables in your remote db via a database link.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文