0

Trying to import an Oracle EBS database (currently in a 19c staging database) into 19c Autonomous Data Warehouse, which won't accept XMLTYPE tables with "STORE AS BASICFILE LOB", but will with "STORE AS SECUREFILE BINARY XML".

So need to change storage of XMLTYPE columns from "STORE AS BASICFILE LOB" to "STORE AS SECUREFILE BINARY XML" with as few changes to syntax as possible. Reason is there are 80 tables to be changed, all with several columns, and all different.

Original DDL to create table;

CREATE TABLE APPS.cpLoadBalance1588_TAB OF XMLTYPE
  XMLSCHEMA "http://xmlns.oracle.com/CRM/Scheduler/Rules" ELEMENT "cpLoadBalance"
 VARRAY "XMLEXTRA"."NAMESPACES" STORE AS BASICFILE LOB "NAMESPACES2143_L"
 VARRAY "XMLEXTRA"."EXTRADATA" STORE AS BASICFILE LOB "EXTRADATA2142_L"
 VARRAY "XMLDATA"."SYS_XDBPD$" STORE AS BASICFILE LOB "SYS_XDBPD$2141_L";

So tried this but get error "ORA-00922: missing or invalid option";

CREATE TABLE APPS.cpLoadBalance1588_TAB OF XMLTYPE
  XMLSCHEMA "http://xmlns.oracle.com/CRM/Scheduler/Rules" ELEMENT "cpLoadBalance"
 VARRAY "XMLEXTRA"."NAMESPACES" STORE AS SECUREFILE BINARY XML "NAMESPACES2143_L"
 VARRAY "XMLEXTRA"."EXTRADATA" STORE AS SECUREFILE BINARY XML "EXTRADATA2142_L"
 VARRAY "XMLDATA"."SYS_XDBPD$" STORE SECUREFILE BINARY XML "SYS_XDBPD$2141_L";

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.