Passing Object Parameters To Functions Remotely In Oracle

Sometimes you need to invoke some functions in remote database via database link. Passing pirimitive types like number or varchar2 are not so dificult. But ow about passing user defined data types? When you encourage this, you may get consider some points.
Firstly, Oracle has some restrictions on object types when passing them remotely. You are not allowed to pass objects remote database's functions via db links. Although you create the same object both two databases, you can not pass parameter.

SQL>con db03/db03
SQL> DROP TYPE test_type;
Type dropped
SQL> CREATE OR REPLACE TYPE test_type IS OBJECT
  2  (
  3    i NUMBER,
  4    z VARCHAR2(12)
  5  )
  6  ;
  7  /
Type created
SQL> CREATE OR REPLACE PROCEDURE set_test_type(  pit_test_type   in    test_type, res out number)
  2  IS
  3  BEGIN
  4    res :=  pit_test_type.i;
  5  END;
  6  /
Procedure created
SQL>
SQL> con db01/db01
SQL> DROP TYPE test_type;
Type dropped
SQL> CREATE OR REPLACE TYPE test_type IS OBJECT
  2  (
  3    i NUMBER,
  4    z VARCHAR2(12)
  5  )
  6  ;
  7  /
Type created
SQL> drop public database link test_link;
Database link dropped
SQL> create public database link test_link
  2    connect to sysadm identified by sysadm
  3    using 'db03';
Database link created

SQL> DECLARE
  2    i NUMBER;
  3    t test_type := test_type(101, 'hello');
  4 
  5  BEGIN
  6    set_test_type@test_link(t, i);
  7    DBMS_OUTPUT.PUT_LINE('i is ' || i);
  8  END;
  9  /
DECLARE
  i NUMBER;
  t test_type := test_type(101, 'hello');
BEGIN
  set_test_type@test_link(t, i);
  DBMS_OUTPUT.PUT_LINE('i is ' || i);
END;
ORA-06550: line 6, column 3:
PLS-00306: wrong number or types of arguments in call to 'SET_TEST_TYPE'
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored



You can create your type only locally. It is also not a solution. Oracle does not know your objects:

SQL>
SQL>con db01/db01
SQL> CREATE OR REPLACE PACKAGE set_test_type_pkg IS
  2    TYPE test_type IS RECORD(
  3      i NUMBER,
  4      z VARCHAR2(12));
  5    PROCEDURE set_test_type(pit_test_type IN test_type, res OUT NUMBER);
  6  END set_test_type_pkg;
  7  /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY set_test_type_pkg IS
  2 
  3    PROCEDURE set_test_type(pit_test_type IN test_type, res OUT NUMBER) IS
  4    BEGIN
  5      res := pit_test_type.i;
  6    END;
  7  END set_test_type_pkg;
  8  /
Package body created
SQL> show err;
No errors for PACKAGE BODY SYSADM.SET_TEST_TYPE_PKG
SQL>con db03/db03
SQL> DECLARE
  2    i NUMBER;
  3    TYPE test_type IS RECORD(
  4      i NUMBER,
  5      z VARCHAR2(12));
  6    tt_test_type test_type;
  7  BEGIN
  8    tt_test_type.i := 101;
  9    tt_test_type.z := 'hiii';
 10    set_test_type_pkg.set_test_type@test_link(tt_test_type, i);
 11    DBMS_OUTPUT.PUT_LINE('i is ' || i);
 12  END;
 13  /
DECLARE
  i NUMBER;
  TYPE test_type IS RECORD(
    i NUMBER,
    z VARCHAR2(12));
  tt_test_type test_type;
BEGIN
  tt_test_type.i := 101;
  tt_test_type.z := 'hiii';
  set_test_type_pkg.set_test_type@test_link(tt_test_type, i);
  DBMS_OUTPUT.PUT_LINE('i is ' || i);
END;
ORA-06550: line 10, column 3:
PLS-00306: wrong number or types of arguments in call to 'SET_TEST_TYPE'
ORA-06550: line 10, column 3:
PL/SQL: Statement ignored
SQL>


You can overcome this situation by referencing a local type both the databases. Important thing is, you have to reference the same type in one database.
SQL>con db01/db01
SQL> DECLARE
  2    i NUMBER;
  3 
  4    tt_test_type set_test_type_pkg.test_type@test_link;
  5  BEGIN
  6    tt_test_type.i := 101;
  7    tt_test_type.z := 'hiii';
  8    set_test_type_pkg.set_test_type@test_link(tt_test_type, i);
  9    DBMS_OUTPUT.PUT_LINE('i is ' || i);
 10  END;
 11  /
i is 101
PL/SQL procedure successfully completed
SQL>