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