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

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

Restriction on Using User-Defined Types with a Remote Database:

One of my application team lead called me... Raja, we have faced DB link issue while accessing the table from remote database.

“While accessing the table from remote database & they got the below error”

ERROR at line 1: ORA-22804: remote operations not permitted on object tables or
User-defined type columns

I have verified the database link & its working fine. Also I am able to access some of the tables from remote database.

After I am digging the issue, I found “that table contains user defined data type column, while accessing the table from remote database having a problem”.

This issue is new to me & they provided some URL for my reference.

I have searched several URL & they suggested using GUID in oracle.

Some details about OID & GUID ...

What is OID (Object Identifier)?

Whenever we create a user defined data type, Oracle will create internally OID (object identifier) for each object.

Each type has an OID. If you create an object type and do not specify an OID, Oracle generates an OID and assigns it to the type. Oracle uses the OID internally for operations pertaining to that type. Using the same OID for a type is important if you plan to share instances of the type across databases for such operations as export/import and distributed queries.

What is GUID (Global unique identifier)?

"A Globally Unique Identifier or GUID is a pseudo-random number used in software applications. Each generated GUID is "mathematically guaranteed" to be unique. This is based on the simple principal that the total number of unique keys (2^64 ).

SQL> SELECT SYS_OP_GUID() FROM DUAL;

SYS_OP_GUID()
--------------------------------
A02BFD5FB5502E59E04400144F81DDE6

GUID will change every time.

Test Case 1
----------------------------------

I have created one table with user defined data type column. I am trying to access the table from another database using database link.

Source Database Name: Source
Target Database Name: Target

SOURCE database (login as raja user)

Steps:

1.Create user defined data type.
2.Create a table with user defined data type column.
3.Insert some records in created table.

--create the type object

SQL> create type raja.testlinktype as object
( v1 varchar2(10) ,
v2 varchar2(20) );

Type created.

-- Create a table with user defined data type column.

SQL> create table raja.testlinktable
( name testlinktype);

Table created.

SQL> insert into testlinktable values (testlinktype ('RC','AB'));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from Raja.testlinktable;

NAME(V1, V2)
--------------------------------------------------------------------------------
TESTLINKTYPE('RC', 'AB')

TARGET database (login as raja user)

Steps:

1.Create the database link.
2.Accessing the source db objects using db link.

--create the database link

SQL> create database link source.world
connect to raja
identified by raja123
using 'source';

Database link created.

SQL> select * from tab@source;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TESTLINKTABLE TABLE

SQL> select * from TESTLINKTABLE@source;
select * from TESTLINKTABLE@source
*
ERROR at line 1:
ORA-22804: remote operations not permitted on object tables or user-defined
type columns

------------------------------------------------

Test Case 2:

SOURCE database (login as raja user)

Steps:

1.Drop the user defined type & table.
2.Recreate the TYPE object using GUID.
3.Create a table with user defined data type column
4.Insert some records in created table.

--Drop the user defined type & table.

SQL> drop table raja.testlinktable;

Table dropped.

SQL> drop type raja.testlinktype;

Type dropped.

SQL> SELECT SYS_OP_GUID() FROM DUAL;

SYS_OP_GUID()
--------------------------------
A02C034A402B460FE04400144FC85792

GUID will change every time.

--create the type object using GUID

SQL> create type raja.testlinktype oid 'A02C034A402B460FE04400144FC85792' as object
( v1 varchar2(10) , v2 varchar2(20) );

Type created.

-- Create a table with user defined data type column.

SQL> create table raja.testlinktable (name testlinktype);

Table created.

SQL> insert into raja.testlinktable values (testlinktype ('RC','AB'));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from raja.testlinktable;

NAME(V1, V2)
--------------------------------------------------------------------------------
TESTLINKTYPE('RC', 'AB')

TARGET database (login as raja user)

SQL> select * from tab@source;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TESTLINKTABLE TABLE

SQL> select * from TESTLINKTABLE@source;

select * from TESTLINKTABLE@source
*
ERROR at line 1:
ORA-22804: remote operations not permitted on object tables or user-defined
type columns

Note:

We should create the type object with GUID in target database also.

--create the same type object with GUID in target database

SQL> create type raja.testlinktype oid 'A02C034A402B460FE04400144FC85792' as object
( v1 varchar2(10) , v2 varchar2(20) );

Type created.

SQL> select * from TESTLINKTABLE@source
2 ;

NAME(V1, V2)
--------------------------------------------------------------------------------
TESTLINKTYPE('RC', 'AB')

-----------------------------------------------------

Suppose if we want to access more than one user defined data types across the database, we should follow the below steps.

Test Case 3:

SOURCE database (login as raja user)

We generate the GUID dynamically.

SQL> SELECT SYS_OP_GUID() FROM DUAL;

SYS_OP_GUID()
--------------------------------
A02C034A4030460FE04400144FC85792

--create the type object

SQL> create type raja.testlinktype_1 oid 'A02C034A4030460FE04400144FC85792' as object
( v1 varchar2(10) , v2 varchar2(20) );

Type created.

-- Create a table with user defined data type column.

SQL> create table raja.testlinktable_1
( name testlinktype_1);

Table created.

SQL> insert into raja.testlinktable_1 values (testlinktype_1 ('RC','AB'));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from raja.testlinktable_1;

NAME(V1, V2)
--------------------------------------------------------------------------------
TESTLINKTYPE('RC', 'AB')

TARGET database (login as raja user)

SQL> select * from tab@source;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TESTLINKTABLE_1 TABLE
TESTLINKTABLE TABLE

--create the same type object with GUID in target database

SQL> create type Raja.testlinktype_1 oid 'A02C034A4030460FE04400144FC85792' as object
( v1 varchar2(10) , v2 varchar2(20) );

Type created.

Note:

We should create the type object using destination database GUID.

SQL> select * from testlinktable_1@source;

NAME(V1, V2)
--------------------------------------------------------------------------------
TESTLINKTYPE_1('RC', 'AB')

SQL> select * from TESTLINKTABLE@source ;

NAME(V1, V2)
--------------------------------------------------------------------------------
TESTLINKTYPE('RC', 'AB')

I Hope this article helped to you. I am expecting your suggestions/feedback.
It will help to motivate me to write more articles.

Best Regards
Rajabaskar Thangaraj
WWW.dbarajabaskar.blogspot.com