Referencing User Defined Types over DBLink: Problem and Alternatives

Referencing User Defined Types over DBLink PLS-00453

Referencing User Defined Types over DBLink
Consider a PL/SQL call to a function which returns a user-defined type (UDT) – say, a SQL Type object. This works well as long as the called function resides in the same database as the calling PL/SQL, but if the called function is in a remote database, the PL/SQL call via DBLink end ups with the following error:

Error: PLS-00453: remote operations not permitted on object tables or user-defined type columns

This has often led to issues discovered too late for developers, who successfully tested function calls over multiple schemas simulating remote calls, but when the function databases were separated in later stages of testing, the PL/SQL call no longer worked.

Here’s a test case that demonstrates the problem. This was tested on a DB link from an 11.2.0.3 database to a 10.2.0.5 database.

The Problem: UDT across DBLink, and PLS-00453

Consider a user-defined type user_obj, existing in two DBs connected via DBLink from DB1 to DB2:

SQL> -- Object used in both DBs
SQL> -- as function return type
SQL> create or replace type
  2                user_obj
  3  as object
  4  (
  5    user_id number(4)
  6  , user_name varchar2(50)
  7  );
  8  /

Type created.

DB2 contains a table user_map, and a function get_user that fetches DB2 user data based on an input DB1_user_id:

SQL> -- Table used for the test case
SQL> select * from user_map;

DB1_USER_ID DB2_USER_ID DB2_USER_NAME
----------- ----------- -------
          1          11 USR11
          2          12 USR12

SQL> -- Function in called DB
SQL> -- returning user-defined type
SQL> create or replace function
  2                  get_user
  3  (
  4    p_req_id  in number
  5  , p_user_id in number
  6  )
  7  return user_obj
  8  as
  9    v_user_obj  user_obj;
 10  begin
 11    -- Get the data to be passed on
 12    select user_obj(db2_user_id
 13                  , db2_user_name)
 14    into v_user_obj
 15    from user_map
 16    where db1_user_id = p_user_id;
 17
 18    return v_user_obj;
 19  end;
 20  /

Function created.

When get_user is called over a DBLink from DB1 to DB2, this happens:

SQL> -- Invoking function with UDT
SQL> -- return type, from remote DB
SQL> declare
  2    v_user_obj   user_obj;
  3  begin
  4    v_user_obj :=
  5      get_user@DB2(999, 2);
  6  end;
  7  /
    get_user@DB2(999, 2);
    *
ERROR at line 5:
ORA-06550: line 5, column 5:
PLS-00453: remote operations not permitted on object tables or user-defined
type columns
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

Understanding the Error PLS-00453

Oracle ® Database Object-Relational Developer’s Guide 12c Release 1 (12.1) has a section Restriction on Using User-Defined Types with a Remote Database", which says:

Objects or user-defined types (specifically, types declared with a SQL CREATE TYPE statement, as opposed to types declared within a PL/SQL package) are currently useful only within a single database.

You cannot connect to a remote database to select, insert, or update a user-defined type or an object REF on a remote table.

You can use the CREATE TYPE statement with the optional keyword OID to create a user-specified object identifier (OID) that allows an object type to be used in multiple databases. See the discussion on assigning an OID to an object type in the Oracle Database Data Cartridge Developer’s Guide.

You cannot use database links within PL/SQL code to declare a local variable of a remote user-defined type.

You cannot convey a user-defined type argument or return value in a PL/SQL remote procedure call.

Oracle ® Database Error Messages 12c Release 1 (12.1) has this entry on ORA-, the underlying cause of PLS-00453.

ORA-22804: remote operations not permitted on object tables or user-defined type columns

Cause: An attempt was made to perform queries or DML operations on remote object tables or on remote table columns whose type is one of object, REF, nested table or VARRAY.

Action: None

That pretty clearly sums it up — no UDTs as function return value across DB Link. But there are alternatives.

Workaround 1: Substitute PL/SQL Types for SQL Types

Create a PL/SQL type in a package spec in the remote database, matching with the SQL Type. (If you’re wondering how PL/SQL Type and SQL Type are different, see difference between record and object type.)
Use the PL/SQL type as the return of the function in the remote DB.
In the calling PL/SQL, assign the return of the function into a variable of PL/SQL type as defined in the remote DB.

See it in action:

Create the package spec with PL/SQL type:

SQL> -- Package specs to hold PL/SQL
SQL> -- return types for use across
SQL> -- remote DB calls.
SQL> create or replace package type_def
  2  as
  3   -- Record type used in both DBs
  4  -- as function return type
  5    type user_obj
  6    is record
  7    (
  8    user_id number(4)
  9  , user_name varchar2(50)
 10    );
 11  end type_def;
 12  /

Package created.

Use the PL/SQL type as function return type:

SQL> -- Function in called DB
SQL> -- returning record
SQL> create or replace function
  2                  get_user
  3  (
  4    p_req_id  in number
  5  , p_user_id in number
  6  )
  7  return type_def.user_obj
  8  as
  9    v_user_obj type_def.user_obj;
 10  begin
 11    -- Get the data to be passed on
 12    select db2_user_id
 13          , db2_user_name
 14    into v_user_obj
 15    from user_map
 16    where db1_user_id = p_user_id;
 17
 18    return v_user_obj;
 19  end;
 20  /

Function created.

Call function from remote DB:

SQL> -- Invoking function from remote DB
SQL> -- & assigning its return PL/SQL
SQL> -- record to SQL Type object
SQL> declare
  2    v_remote_user_obj type_def.user_obj@DB2;
  3    v_user_obj user_obj;
  4  begin
  5
  6    -- Call remote function, assign
  7    -- return value to PL/SQL type
  8    v_remote_user_obj :=
  9      get_user@DB2(999, 2);
 10
 11    -- Convert PL/SQL type to SQL type
 12    -- after the call
 13
 14    v_user_obj :=
 15      user_obj(
 16        v_remote_user_obj.user_id
 17      , v_remote_user_obj.user_name
 18              );
 19
 20
 21    -- That's the output
 22    dbms_output.put_line('user id: '
 23     ||v_user_obj.user_id);
 24    dbms_output.put_line('user name: '
 25     ||v_user_obj.user_name);
 26  end;
 27  /
user id: 12
user name: USR12

PL/SQL procedure successfully completed.

VoilÃ ! No PLS-00453 this time.

The same logic can be extended to substitute a PL/SQL nested table for a nested table of objects.

Workaround 2: Use XML-Encoded Wrapper over SQL Type

Build an XML-encoded string using the attributes of the SQL Type object, and assign the string to a VARCHAR2 variable. Use the VARCHAR2 variable as the return of the function in the remote DB.
In the calling PL/SQL, assign the return of the function into a variable of XML Type and cast it back as SQL Type.

See it in action:

Build XML-encoded string wrapped over the SQL Type object, and use that as function return value:

SQL> -- Function in called DB
SQL> -- returning XML-encoded string
SQL> -- wrapped over UDT contents
SQL> create or replace function
  2                  get_user
  3  (
  4    p_req_id  in number
  5  , p_user_id in number
  6  )
  7  return varchar2
  8  as
  9    v_user_obj  user_obj;
 10    v_ret  varchar2(4000);
 11  begin
 12    -- Get the data in the UDT
 13    select user_obj(db2_user_id
 14                  , db2_user_name)
 15    into v_user_obj
 16    from user_map
 17    where db1_user_id = p_user_id;
 18
 19    -- Convert UDT to XML string
 20
 21    v_ret := '<USER_OBJ>'
 22     ||'<USER_ID>'||v_user_obj.user_id
 23     ||'</USER_ID>'
 24     ||'<USER_NAME>'||v_user_obj.user_name
 25     ||'</USER_NAME>'
 26     ||'</USER_OBJ>';
 27
 28    -- Return the XML string
 29    return v_ret;
 30  end;
 31  /

Function created.

After function call, convert XML Type string to SQL Type:

SQL> -- Invoking function from remote DB
SQL> -- & assigning its return XML-encoded
SQL> -- string to SQL Type object
SQL> declare
  2    v_xmltype   xmltype;
  3    v_user_obj  user_obj;
  4  begin
  5
  6    -- Call remote function, assign
  7    -- XML-encoded string return
  8    -- value to XML Type after call
  9
 10    v_xmltype :=
 11      xmltype(
 12        get_user@DB2(999, 2)
 13              );
 14
 15    -- Convert XML type to SQL type
 16    v_xmltype.toObject(v_user_obj);
 17
 18    -- That's the output
 19    dbms_output.put_line('user id: '
 20     ||v_user_obj.user_id);
 21    dbms_output.put_line('user name: '
 22     ||v_user_obj.user_name);
 23
 24  end;
 25  /
user id: 12
user name: USR12

PL/SQL procedure successfully completed.

Workaround that did not work: Create UDT with OID

Oracle Database Data Cartridge Developer’s Guide gives hope that assigning an "OID" to the UDT would allow the object type get used in multiple databases. To test this, the type definition was changed as below:

SQL> -- GUID from remote DB 
SQL> -- to use as OID
SQL> select sys_op_guid()
  2  from dual;

SYS_OP_GUID()
--------------------------------
FAC1422FDEEA7F06E040FE89F5A32441

SQL> -- Object used in both DBs
SQL> -- as function return type
SQL> create or replace type
  2                user_obj
  3  oid 'FAC1422FDEEA7F06E040FE89F5A32441'
  4  as object
  5  (
  6    user_id number(4)
  7  , user_name varchar2(50)
  8  );
  9  /

Type created.

But the function call via DBLink ended up with the original problem — the same PLS-00453 error:

SQL> -- Invoking function with UDT
SQL> -- return type, from remote DB
SQL> declare
  2    v_user_obj   user_obj;
  3  begin
  4    v_user_obj :=
  5      get_user@DB2(999, 2);
  6  end;
  7  /
    get_user@DB2(999, 2);
    *
ERROR at line 5:
ORA-06550: line 5, column 5:
PLS-00453: remote operations not permitted on object tables or user-defined
type columns
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

Other Workarounds

1. Use the individual elements of the SQL Type as primitive datatypes, each an OUT variable of a procedure. In this example, user_id and user_name would be two separate OUT variables of a procedure.

2. A suggested workaround, which I have not tried, is using Java Stored Procedures instead of conventional PL/SQL. Here are more details of the suggestion.