Hi all,
I'm trying to create a procedure on a remote database, that will return a collection (table of), so then I could use it for a REF CURSOR table.
--***************************
create or replace package qry as
type n_qry is record (i number);
type t_qry is table of n_qry;
procedure qry_rem(k in out t_qry);
END lekerd;
--***************************
create or replace package body qry as
procedure qry_rem(k in out t_qry) is
begin
for i in 1 .. 10 loop
k(i).i := i;
end loop;
end qry_rem;
end qry;
--***************************
Than I'm trying to execute this procedure via a database link.
--***************************
create or replace package qry_home as
type ref_qry is ref cursor return qry.n_qry@dblink;
procedure r_qry(resultset in out ref_qry);
end qry_home;
--***************************
create or replace package body qry_home as
procedure r_qry(resultset in out ref_qry) is
t1 gry.t_qry@dblink;
procedure run_rem(t out qry.t_qry@dblink) is
begin
qry.qry_rem@dblink(t);
end;
begin
run_rem(t1);
open resultset for select * from table(t1);
end run_rem;
END qry_home;
--***************************
The problem I'm having is that in this case I always receive error ORA-02055 distributed update operation failed; rollback required. Why is this? Can anyone help me out with this.
The reason I'm trying to do this, is to populate the collection on the remote database, and get the results, and return them from the local database to an Oracle Forms application. I'm trying this way to reduce the load on the local database. Maybe it's not the best way. I'm open to suggestions...
Thanks
