oracle 通过 dblink 调用存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1122175/
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 a stored proc over a dblink
提问by neesh
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.
当我从 package_name 所属的同一个数据库实例和模式运行它时，我能够正常运行它。但是，当我通过数据库链接运行它时（对存储的 proc 名称进行必要的修改等），我收到一个 oracle 错误：ORA-24338：语句句柄未执行。
The modified version of this code over a dblink looks like this:
此代码通过 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;
采纳答案by Juergen Hartelt
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.record_cursor 是一个引用游标类型。引用游标是仅在创建它的数据库中有效的内存句柄。换句话说，您不能在远程数据库中创建引用游标并尝试从中获取本地数据库。
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.
如果您确实需要处理本地数据库中的数据并且表必须保留在远程数据库中，那么您可以将包“package_name”移动到本地数据库中，并让它通过远程数据库中的表执行查询数据库链接。
