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

简介: Query user defined types over database linkQuerying tables over database link is very common today.

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')
目录
相关文章
|
2月前
|
Docker 容器
成功解决:Caused by: ParsingException[Failed to parse object: expecting token of type [START_OBJECT] but
这篇文章讨论了在使用Docker启动Elasticsearch容器时遇到的一个具体问题:由于配置文件`elasticsearch.yml`解析出错导致容器启动失败。文章提供了详细的排查过程,包括查看容器的日志信息、检查并修正配置文件中的错误(特别是空格问题),并最终成功重新启动了容器。
|
2月前
|
JSON 数据格式 Python
【python】解决json.dump(字典)时报错Object of type ‘float32‘ is not JSON serializable
在使用json.dump时遇到的“Object of type ‘float32’ is not JSON serializable”错误的方法,通过自定义一个JSON编码器类来处理NumPy类型的数据。
63 1
|
3月前
|
JSON 前端开发 数据格式
【Python】已解决:TypeError: Object of type JpegImageFile is not JSON serializable
【Python】已解决:TypeError: Object of type JpegImageFile is not JSON serializable
58 0
|
5月前
|
JavaScript
Vue报错 Invalid default value for prop “list“: Props with type Object/Array must use a factory
Vue报错 Invalid default value for prop “list“: Props with type Object/Array must use a factory
277 0
|
5月前
R语言ggsurvplot绘制生存曲线报错 : object of type ‘symbol‘ is not subsettab
R语言ggsurvplot绘制生存曲线报错 : object of type ‘symbol‘ is not subsettab
|
5月前
FeignClient【问题】Cannot deserialize value of type``from Object value (token `JsonToken.START_OBJECT`)
FeignClient【问题】Cannot deserialize value of type``from Object value (token `JsonToken.START_OBJECT`)
680 0
|
5月前
|
JavaScript
[Vue warn]: Method “components“ has type “object“ in the component definition. Did you reference the
[Vue warn]: Method “components“ has type “object“ in the component definition. Did you reference the
|
JSON 数据格式
TypeError: Object of type ‘float32‘ is not JSON serializable
TypeError: Object of type ‘float32‘ is not JSON serializable
191 0
|
Python
AttributeError: type object ‘Image‘ has no attribute ‘open‘
原因分析:Image调用顺序出错,因为第一行的from PIL import Image与第二行tkinter import *冲突,tkinter中也含有Image类,所以你使用的是tkinter.Image
228 0
|
JavaScript
[Vue warn]: Method “components“ has type “object“ in the component definition. Did you reference the
[Vue warn]: Method “components” has type “object” in the component definition. Did you reference the
[Vue warn]: Method “components“ has type “object“ in the component definition. Did you reference the
下一篇
无影云桌面