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:

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

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

Understanding the Error PLS-00453

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

Objects or user-defined types (specifically, types declared with a SQL CREATE TYPEstatement, 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

1. 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.)
2. Use the PL/SQL type as the return of the function in the remote DB.
3. 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:

1. Create the package spec with PL/SQL type:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

   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.

2. Use the PL/SQL type as function return type:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

   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.

3. Call function from remote DB:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

   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

1. 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.
2. 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:

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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

   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.

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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

   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.