通过 dblink 调用存储过程 [英] calling a stored proc over a dblink

问题描述

我正在尝试通过数据库链接调用存储过程.代码如下所示:

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;

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

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.

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

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;

推荐答案

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

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.

如果您确实需要处理本地数据库中的数据并且表必须保留在远程数据库中，那么您可以将包package_name"移动到本地数据库中，并让它对远程表中的表执行查询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.

这篇关于通过 dblink 调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！