Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 8.1.7 to 9.2.0.3 and db-link
Hello All,
While upgrading from 8.1.7 to 9.2.0.3.0 I experienced a problem
The below-given example shows how it worked on 8.1.7.
But on 9.2.0.3 when user t2 tries to fetch cursor received by t1.p1.f3 he gets an exception "ORA-02019 connection description for remote database not found". :(
connect sys_at_SID as sysdba
drop user t1 cascade;
create user t1 identified by t1;
grant connect,resource to t1;
drop user t2 cascade;
create user t2 identified by t2;
grant connect,resource to t2;
connect t1/t1_at_SID
create database link d1
connect to t1 identified by t1
using 'SID';
create package p1 as
type r1 is record (dummy varchar2(100));
type c1 is ref cursor return r1;
function f1 return varchar2;
function f2 return c1;
function f3 return c1;
end;
/
create package body p1 as
function f1 return varchar2
as
r varchar2(100);
begin
select * into r from dual_at_d1;
return r;
end;
function f2 return c1
as
r c1;
begin
open r for
select * from dual;
return r;
end;
function f3 return c1
as
r c1;
begin
open r for
select * from dual_at_d1;
return r;
end;
end;
/
grant all on p1 to t2 with grant option;
connect t2/t2_at_SID
set serveroutput on;
--create database link d1
begin
dbms_output.put_line('Single value');
dbms_output.put_line('Result1 |=>'||t1.p1.f1);
end;
/
declare
c1 t1.p1.c1;
r1 t1.p1.r1;
begin
dbms_output.put_line('Local cursor');
c1:=t1.p1.f2;
fetch c1 into r1;
dbms_output.put_line('Result1 |=>'||r1.dummy);
end;
/
declare
c1 t1.p1.c1;
r1 t1.p1.r1;
begin
dbms_output.put_line('Remote cursor');
c1:=t1.p1.f3;
fetch c1 into r1;
dbms_output.put_line('Result1 |=>'||r1.dummy);
end;
/
If I get it right, the problem lies in the cursor because the function f1, based on the same query and returning a single value works OK.
As well as uncommenting the lines that create the db-link for the end-user t2 make it work all right.
And making an authorized public db-link also helps.
But this is a security-breach.
Could you please help to solve the problem.
regards,
Serj Received on Mon Jun 23 2003 - 00:02:23 CDT