Document Type | Technical Information
Field | Migration
Applicable Product Versions | 6FS07, 6FS07PS, 7FS02, 7FS02PS
Document Number | TMITI001
Overview
The XMLTYPE provided by Tibero is a data type for processing XML data, which internally offers two methods based on CLOB and OBJECT.
When migrating databases, to maintain the XMLTYPE data type consistently between the AS-IS and TO-BE environments, the CLOB-based XMLTYPE must be converted to the OBJECT-based XMLTYPE. In Company O, XMLTYPE is stored as the OBJECT type by default, so conversion is required when migrating to Tibero to match this.
Method
XMLTYPE is a data type that stores XML documents as data.
- When defining an XMLTYPE column during table creation, Tibero internally stores the XML document in CLOB form.
- Tibero provides XMLTYPE based on CLOB and OBJECT. When migrating databases, the XML TYPE must be kept consistent between the AS-IS and TO-BE environments.
In the case of Company O migrating to Tibero, Oracle's XMLTYPE is by default an OBJECT type, so installation and migration in Tibero must be done as OBJECT type XMLTYPE.
How to Check XMLTYPE
1. AS-IS DB
$ cd $TB_HOME/scripts/pkg/ $ tbsql sys/tibero @xmltype_install_checker tbSQL 7 TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. Connected to Tibero. XMLTYPE OID(00000000000000000000000000000001) IS VALID ===XMLTYPE(CLOB) DEPENDENCY LIST=== =================================== PLEASE CHECK DEPENDENCY LIST. DEPENDENCY LIST WILL BE INVALIDATED IF YOU INSTALL XMLTYPE(OBJECT) YOU CAN INSTALL XMLTYPE(OBJECT)!! PSM completed.
2. TO-BE DB (same method)
$ cd $TB_HOME/scripts/pkg/ $ tbsql sys/tibero @xmltype_install_checker
When Migrating from Company O to Tibero (Changing TO-BE DB XMLTYPE to OBJECT)
If the TO-BE DB is confirmed to be of CLOB type, the XMLTYPE should be changed to OBJECT.
If it is CLOB type, the following message will be displayed.
$ tbsql sys/tibero @xmltype_install_checker tbSQL 7 TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. Connected to Tibero. XMLTYPE OID(00000000000000000000000000000001) IS VALID ===XMLTYPE(CLOB) DEPENDENCY LIST=== =================================== PLEASE CHECK DEPENDENCY LIST. DEPENDENCY LIST WILL BE INVALIDATED IF YOU INSTALL XMLTYPE(OBJECT) YOU CAN INSTALL XMLTYPE(OBJECT)!! PSM completed.
1. Change XMLTYPE to Object
$ tbsql sys/tibero @install_xmltype tbSQL 7 TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. Connected to Tibero. Type 'XMLTYPE' created. Type 'XMLSEQUENCETYPE' created. Package 'STANDARD_EXTENSION_XML' created. System altered. Type 'SQLPROF_ATTR' created. Synonym 'SQLPROF_ATTR' created. Granted. Package 'DBMS_SQLTUNE' created. Synonym 'DBMS_SQLTUNE' created. Granted.
2. Recheck TO-BE DB XMLTYPE type after change
$ tbsql sys/tibero @xmltype_install_checker tbSQL 7 TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. Connected to Tibero. ===XMLTYPE(OBJECT) IS ALREADY INSTALLED=== ===XMLTYPE(OBJECT) DEPENDENCY LIST=== =================================== PLEASE CHECK DEPENDENCY LIST. DEPENDENCY LIST WILL BE INVALIDATED IF YOU UNINSTALL XMLTYPE(OBJECT) YOU CAN UNINSTALL XMLTYPE(OBJECT)!! PSM completed.