This discussion is archived
10 Replies Latest reply: Nov 19, 2012 1:20 AM by MarcoGralike RSS

export and import XMLType table

970358 Newbie
Currently Being Moderated
Hi ,
I want to export one table which contain xmltype column form oracle 11.2.0.1.0 and import into 11.2.0.2.0 version.

I got following errors when i export the table , when i tried with exp and imp utility

EXP-00107: Feature (BINARY XML) of column ZZZZ in table XXXX.YYYY is not supported. The table will not be exported.

then i tried export and import pump.Exporting pump is working ,following is the log

-----
;;;
Export: Release 11.2.0.1.0 - Production on Wed Oct 17 17:53:41 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
;;; Legacy Mode Active due to the following parameters:
;;; Legacy Mode Parameter: "log=<xxxxx>Oct17.log" Location: Command Line, Replaced with: "logfile=T<xxxxx>_Oct17.log"
;;; Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "<xxxxx>"."SYS_EXPORT_TABLE_01": <xxxxx>/******** DUMPFILE=<xxxxx>Oct172.dmp TABLES=<xxxxx>.<xxxxx> logfile=<xxxxx>Oct17.log reusedumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13.23 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "<xxxxx>"."<xxxxx>" 13.38 GB 223955 rows
Master table "<xxxxx>"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for <xxxxx>.SYS_EXPORT_TABLE_01 is:
E:\ORACLEDB\ADMIN\LOCALORA11G\DPDUMP\<xxxxx>OCT172.DMP
Job "<xxxxx>"."SYS_EXPORT_TABLE_01" successfully completed at 20:30:14
---------

h4. I got error when i import the pump using following command

+impdp sys_dba/***** dumpfile=XYZ_OCT17_2.DMP logfile=import_vmdb_XYZ_Oct17_2.log FROMUSER=XXXX TOUSER=YYYY CONTENT=DATA_ONLY TRANSFORM=oid:n TABLE_EXISTS_ACTION=append;+

error is :

h3. KUP-11007: conversion error loading table "CC_DBA"."XXXX"*
h3. ORA-01403: no data found*

h3. ORA-31693: Table data object "XXX_DBA"."XXXX" failed to load/unload and is being skipped due to error:*

Please help me to get solution for this.
  • 1. Re: export and import XMLType table
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Is the export done via SYS and the import done also with SYS ? Can you elaborate on this?
    Is the column (xmltype column) schemaless or defined with a XSD schema?

    What if you re-create the table with the xmltype column before hand in the 11.2.0.2.0 database and then import via the impdp utility, does that work?
  • 2. Re: export and import XMLType table
    970358 Newbie
    Currently Being Moderated
    Hi Marco Gralike,
    Thanks for your reply.
    h4. Is the export done via SYS and the import done also with SYS ? Can you elaborate on this?
    * Export is done with different shcema and import is done with "sys as sysdba"
    h4. Is the column (xmltype column) schemaless or defined with a XSD schema?
    * It is schemaless below is the script to create the table.

    CREATE TABLE trcb_xbrl(
    +"XBRLFILENAME" VARCHAR2(50 BYTE),+
    +"ARFILING_DATE" DATE,+
    +"FYEAR" VARCHAR2(4 BYTE),+
    +"FMONTH" VARCHAR2(2 BYTE),+
    +"FDAY" VARCHAR2(2 BYTE),+
    +"XBRLVERSION" VARCHAR2(10 BYTE),+
    +"XBRLFILE" "SYS"."XMLTYPE" ,+
    +"LMODIFIED_DATE" DATE,+
    +"FILINGOPTION" VARCHAR2(2 BYTE))+
    XMLTYPE COLUMN xbrlfile STORE AS BINARY XML
    ALLOW ANYSCHEMA
    ALLOW NONSCHEMA

    CREATE UNIQUE INDEX "XXXXX"."XXXX_XBRL_XMLINDEX_IX" ON "CCCC"."XXXX_XBRL" (EXTRACTVALUE(SYS_MAKEXML(128,"SYS_NC00014$"),'/xbrl'))

    h4. What if you re-create the table with the xmltype column before hand in the 11.2.0.2.0 database and then import via the impdp utility, does that work?
    * Still i didn't try this, but when i import using impdp , its creates the table successfully but it fails in importing the data.

    - thees_k
  • 3. Re: export and import XMLType table
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Oracle XMLDB works under the covers, sometimes, with Oracle VPD (dbms_rls) functionality, that's why I asked to create the table first and attempt an import via data only.
  • 4. Re: export and import XMLType table
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Do you were this
    CREATE UNIQUE INDEX "XXXXX"."XXXX_XBRL_XMLINDEX_IX" ON "CCCC"."XXXX_XBRL" (EXTRACTVALUE(SYS_MAKEXML(128,"SYS_NC00014$"),'/xbrl'))
    comes from...?

    SYS_MAKEXML is used by Oracle XMLDB for different purposes, internally, and although I figured out some use-cases, I wonder if this index was handmade or created by Oracle. Or is this "just" a copy/paste text of a tool like Toad. Has the XMLDB database bit (11.2.0.2.0) extended with the Oracle XBRL Extention functionality...?
  • 5. Re: export and import XMLType table
    970358 Newbie
    Currently Being Moderated
    CREATE UNIQUE INDEX "XXXXX"."XXXX_XBRL_XMLINDEX_IX" ON "CCCC"."XXXX_XBRL" (EXTRACTVALUE(SYS_MAKEXML(128,"SYS_NC00014$"),'/xbrl'))
    above index is created by us because we are storing file which like
    <?xml version="1.0" encoding="UTF-8"?>
    <xbrl xmlns="http://www.xbrl.org/2003/instance" xmlns:AAAAAA="http://www.AAAAAA.COM/AAAAAA" xmlns:ddd4217="http://www.xbrl.org/2003/ddd4217" xmlns:link="http://www.xbrl.org/2003/linkbase" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <link:schemaRef xlink:href="http://www.fsm.AAAAAA.COM/AAAAAA-xbrl/v1/AAAAAA-taxonomy-2009-v2.11.xsd" xlink:type="simple" />
    <context id="Company_Current_ForPeriod"> ...
    I tried to export pump with and without using DATA_OPTIONS=XML_CLOBS too.Both time exporting was success but import get same KUP-11007: conversion error loading table "tab_owner"."bbbbb_XBRL" error.

    I tried the import in different ways
    1. Create table first then import data_only (CONTENT=DATA_ONLY)
    2. Import all table

    In both way table ddl is created successfully ,but it fail on import data.Following is the log when i importing
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table "aaaaa"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded
    Starting "aaaaa"."SYS_IMPORT_TABLE_02":  aaaaa/********@vm_dba TABLES=tab_owner.bbbbb_XBRL dumpfile=bbbbb_XBRL_OCT17_2.DMP logfile=import_vmdb
    bbbbb_XBRL_Oct29.log DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS
    Processing object type TABLE_EXPORT/TABLE/TABLE
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    *KUP-11007: conversion error loading table "tab_owner"."bbbbb_XBRL"*
    *ORA-01403: no data found*
    
    ORA-31693: Table data object "tab_owner"."bbbbb_XBRL" failed to load/unload and is being skipped due to error:
    ORA-29913: error in executing ODCIEXTTABLEFETCH callout
    ORA-26062: Can not continue from previous errors.
    Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
    Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
    Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
    Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
    Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    Job "aaaaa"."SYS_IMPORT_TABLE_02" completed with 1 error(s) at 18:42:26
  • 6. Re: export and import XMLType table
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    thees_k wrote:
    CREATE UNIQUE INDEX "XXXXX"."XXXX_XBRL_XMLINDEX_IX" ON "CCCC"."XXXX_XBRL" (EXTRACTVALUE(SYS_MAKEXML(128,"SYS_NC00014$"),'/xbrl'))
    above index is created by us because we are storing file which like
    Even more curious. As mentioned SYS_MAKEXML is an Oracle "internal" function / undocumented. Were you advised by Oracle to do so?
    Searching via Google...let me guess...you guys use Toad...

    Maybe Mark's (mdrake) old but still valid example will work for you: Indexing a XMLType column record (aka recreate the index the proper way or temporary get rid of it before export/import)

    Edited by: Marco Gralike on Oct 30, 2012 2:19 PM
  • 7. Re: export and import XMLType table
    970358 Newbie
    Currently Being Moderated
    Hi Marco Gralike,
    Thanks for your reply.As you suggested ,I have tried without index , it gives the same error.
    In both case when i import pump (with index and without index) table was created successfully only fail on data part.

    Regards,
    Thees.
  • 8. Re: export and import XMLType table
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Create a SR, service request, on support.oracle.com, if you have not already done.
  • 9. Re: export and import XMLType table
    970358 Newbie
    Currently Being Moderated
    Thanks Marco.
    Source dump oracle is installed in windows 7 and destination oracl is linux. I suspect it might be the cause.I created SR with oracle.Thanks again.
  • 10. Re: export and import XMLType table
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Interested in what the outcome will be regarding the SR

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points