oracle数据库中的Sys.XMLType 和 XMLType Datatypes

参考自：

Sys.XMLType vs. XMLType Datatypes (文档 ID 279843.1)

PURPOSE
-------

This article explains the difference between sys.xmltype and xmltype datatypes.


SCOPE & APPLICATION
-------------------

This article is written for XML developers and Oracle DBAs.


SYS.XMLType vs. XMLType Datatypes
----------------------------------

When creating a table with an XMLTYPE column,you can create the column as XMLTYPE or as SYS.XMLTYPE

A subsequent DESC on the table will show the column as either XMLTYPE or SYS.XMLTYPE

What is the difference,and when should SYS/XMLTYPE be used and when should XMLTYPE be used ?

There is only one type by the name of '%XMLTYPE%' within the database,for example:

SELECT TYPE_NAME,OWNER FROM DBA_TYPES WHERE TYPE_NAME LIKE '%XMLTYPE';

TYPE_NAME OWNER
------------------------------ --------
XMLTYPE SYS

XMLTYPE is a synonym for SYS.XMLTYPE,as shown below :-

SELECT SYNONYM_NAME,OWNER FROM DBA_SYNONYMS WHERE SYNONYM_NAME LIKE '%XMLTYPE%';

SYNONYM_NAME OWNER
------------------------------ ------------------------------
XMLTYPE PUBLIC

In summary,you may use SYS.XMLTYPE or XMLTYPE in a table column definition.

There is however an exception to the rule in Oracle 9.2,when attempting to use SQLLoader to load an XMLTYPE column,due to Abstract: SQL*LOADER-418 WHILE LOADING XMLTYPE COLUMN BASED ON XMLTYPE SYNONYM (currently not published)
Workaround:
use only sys.xmltype to create xmltype column/table
create table po5(po sys.xmltype);
Fixed In Ver: 10.0.0

RELATED DOCUMENTS
-----------------

Oracle XML Database online documentation

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!