Query user defined types over database link
Querying tables over database link is very common today. But what happens when we have a table that depends on one or more user defined types? Oracle needs to know the structure of our table and it’s columns when it receives it over the DB link. However, it cannot retrieve that information from remote server (although it would be nice to have this in future) and that’s why it raises an error: ORA-22804: remote operations not permitted on object tables or user-defined type columns.


However if we are able to tell oracle on our side of database link what the types look like it would be possible for it to interpret the data. And we can do this by creating all used UDTs on our side taking following into consideration:


 Types need to have exact names as ones on the remote server (although they don’t need to be in the same schema)
Types need to have exact OID as ones on the remote server.
Types don’t need to have member functions implemented as on the remote server. You can either omit them completely if you don’t use or need them. Or you can implement them in your custom way if you want.
Here is an rough example of how it works. We’ll start on remote server and create needed objects (type and table).


-- REMOTE SERVER PART
 
CREATE OR REPLACE TYPE ContactInfo AS OBJECT (
  phoneNumber varchar2(50),
  address     varchar2(500),
  mail        varchar2(100)
);
 
CREATE TABLE users (
  id       NUMBER(10) NOT NULL,
  username varchar2(20),
  contact  ContactInfo
);
 
-- insert one record:
INSERT INTO users
VALUES (1,'uuser',ContactInfo('+385123456789','Somewhere, Atlantis 21314', 'uuser@atlantis.com'));
COMMIT;
 
-- We are going to need OID from remote server later, so let's get it right away.
SELECT type_name, type_oid FROM dba_types WHERE type_name='CONTACTINFO';
 
TYPE_NAME            TYPE_OID
-------------------- -------------------------------------
CONTACTINFO          582FAF525C684D7DB094F959FC667063
 
.
Now we’ll switch to our server. Let’s assume database link REMOTEDB is already created and goes straight to our remote user.


-- HOME SERVER PART
-- first let's try to query our remote table:
 
SELECT * FROM users@"REMOTEDB";
 
SQL Error: ORA-22804: remote operations NOT permitted ON object TABLES OR user-defined TYPE COLUMNS
22804. 00000 -  "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.
 
-- now let's try to tell oracle what our UDT looks like
CREATE OR REPLACE TYPE ContactInfo oid '582FAF525C684D7DB094F959FC667063' AS OBJECT (
  phoneNumber varchar2(50),
  address     varchar2(500),
  mail        varchar2(100)
);
/
 
-- and query the table again:
SELECT * FROM people@"REMOTEDB";
 
     ID USERNAME     CONTACT
------- ------------ ------------------------------------------------------------------------------------
      1 uuser        CONTACTINFO('+385123456789','Somewhere, Atlantis 21314','uuser@atlantis.com')