通过dblink调用存储的proc [英] 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所属的同一数据库实例和模式运行此程序时，我可以正常运行它.但是，当我通过数据库链接运行它时(对存储的过程名称进行了必要的修改等)，我得到一个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调用存储的proc的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！