Thanks for the question, Sumit.

Asked: February 27, 2016 - 5:55 pm UTC

Last updated: February 29, 2016 - 12:46 am UTC

Version: 11gr2

Viewed 10K+ times! This question is

You Asked

Hello ALL,

Recently our team is struggling to get data from another DB using DB link.

Details as below
We have two DB's say DB1 and DB2.
Now if we want access some data from DB2 in DB1 procedure then we have DB link as DB2Link

While accessing this data we are calling packaged procedure of DB2 using DB link.Procedure present in DB2 takes one input parameter eg. Number and returns output of some user defined type created in DB2 only
So could you please help us with :-
1. Is it possible to access parameterized /non parameterized procedures of DB2 from DB1 using DB link?
If yes,can you supply any sample code to understand how it works.

2. What will happen in case user defined type of DB2 is returned as procedure output for DB2 procedure called over DB link from DB1.
I believe in this ORACLE won't allow DB1 to recognise the user defined type of DB2 and will error out as

Error :-
"ORA-30626: function/procedure parameters of remote object types are not supported".

Even creating synonym won't help.

Let me know when in case you need any more information.
Happy to hear back from you guys.

Regards,
Sumit

and Connor said...

If its a user defined type in the object sense, ie,

create type xxx as object ( ... );

then that wont work as a plsql parameter across database links. The closest you get is defining objects with a common object id, and then using a table as a transfer mechanism

-- db1
SQL> CREATE OR REPLACE TYPE my_obj OID '4AD010BCA3E84CDF99A49143BD444FDE' AS OBJECT (x1 int, y1 int);
  2  /

Type created.

SQL>
SQL> drop table t purge;

Table dropped.

SQL> create table t ( x int, y my_obj);

Table created.

SQL> create or replace
  2  function  my_func return int is
  3  begin
  4    insert into  t values ( 1, my_obj(12,12) );
  5    
  6    return 1;
  7  end;
  8  /

Function created.

and then on your other database

SQL> CREATE OR REPLACE TYPE my_obj OID '4AD010BCA3E84CDF99A49143BD444FDE' AS OBJECT (x1 int, y1 int);
  2  /

Type created.

SQL> create or replace
  2  procedure my_proc is
  3    l_obj my_obj;
  4    l_id int;
  5  begin
  6    l_id := my_func@db11;
  7    select y into l_obj
  8    from t@db11
  9    where x = l_id;
 10  end;
 11  /

Procedure created.

SQL> exec my_proc

PL/SQL procedure successfully completed.

but that seems a bit of a kludge to me. Better to return scalar values, or to use types defined with PL/SQL packages for this purpose. eg

-- db1
SQL> create or replace
  2  package pkg is
  3    type my_obj is record ( x1 int, y1 int);
  4    function f return my_obj;
  5  end;
  6  /

Package created.

SQL>
SQL> create or replace
  2  package body pkg is
  3    function f return my_obj is
  4      r my_obj;
  5    begin
  6      r.x1 := 12;
  7      r.y1 := 12;
  8      return r;
  9    end;
 10  end;
 11  /

Package body created.

-- db2

SQL> create or replace
  2  procedure my_proc is
  3    l_obj pkg.my_obj@db11;
  4  begin
  5    l_obj := pkg.f@db11;
  6  end;
  7  /

Procedure created.

SQL>
SQL> exec my_proc

PL/SQL procedure successfully completed.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks Connor

Sumit Pawar, February 28, 2016 - 6:36 am UTC

Thanksa lot Connor for the response.

Could you please share any example of accessing package.procedure with in and out parameters accessed over DB link.
Seems that it's simple to call function having standard data type and not user defined types.

Regards,
Sumit

February 29, 2016 - 12:46 am UTC

Here's some simple variations

-- db1

SQL> create or replace
  2  package pkg is
  3    type my_obj is record ( x1 int, y1 int);
  4    function f return my_obj;
  5    procedure p1(r in out my_obj);
  6    procedure p2(x1 in out int, y1 in out int);
  7  end;
  8  /

Package created.

SQL>
SQL> create or replace
  2  package body pkg is
  3    function f return my_obj is
  4      r my_obj;
  5    begin
  6      r.x1 := 12;
  7      r.y1 := 12;
  8      return r;
  9    end;
 10
 11    procedure p1(r in out my_obj) is
 12    begin
 13      r.x1 := 12;
 14      r.y1 := 12;
 15    end;
 16
 17    procedure p2(x1 in out int, y1 in out int) is
 18    begin
 19      x1 := 12;
 20      y1 := 12;
 21    end;
 22
 23  end;
 24  /

Package body created.

and then on the other db

SQL> create or replace
  2  procedure my_proc is
  3    l_obj pkg.my_obj@db11;
  4
  5    l_x int;
  6    l_y int;
  7  begin
  8    l_obj := pkg.f@db11;
  9
 10    pkg.p1@db11(l_obj);
 11
 12    pkg.p2@db11(l_obj.x1,l_obj.y1);
 13
 14    pkg.p2@db11(l_x,l_y);
 15  end;
 16  /

Procedure created.

SQL> sho err
No errors.
SQL>
SQL> exec my_proc

PL/SQL procedure successfully completed.

Thanks Connor for such detailed level explanation

Sumit, February 29, 2016 - 5:01 am UTC

Thanks for your dedication and in depth explanation.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thanks Connor

Thanks Connor for such detailed level explanation

More to Explore

PL/SQL demos

PL/SQL docs

Package procedure call not working over DB link

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thanks Connor

Thanks Connor for such detailed level explanation

More to Explore

PL/SQL demos

PL/SQL docs