This discussion is archived
10 Replies Latest reply: Oct 24, 2012 11:42 PM by 970358 RSS

Can't export my database using exp with XMLTYPE member column

597647 Newbie
Currently Being Moderated
Hi,

I have a database that has several tables in it. Some of the tables contain CLOBS, while others contain both CLOBs and XMLTypes. I have also installed XMLDB. After installing XMLDB, I am unable to utilize the export 'exp' utility to export data out of the database.

I am running Oracle 10gR2

If I try to use exp, I get the following:

EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully


I read that prior to 11g, exporting data using the datapump 'expdp' will not work.

--> Exporting XMLType Table in Table mode  (using Oracle exp)

Has anyone else had problems using 'exp' with your database and xmldb? If so, please respond with your comments regarding how you were able to get around the issue.

Many thanks in advance for whatever feedback you can provide!
  • 1. Re: Can't export my database using exp with XMLTYPE member column
    597647 Newbie
    Currently Being Moderated
    Allow me to update my original post...

    I was able to export the database with the XMLTYPE column present. However, I cannot export the database after loading the xml database. Here are the steps I am taking to create the XMLDB:

    create tablespace XML_DATA
    @$ORACLE_HOME/rdbms/admin/catqm.sql <xdb password> XML_DATA TEMP
    @$ORACLE_HOME/rdbms/admin/catxdbj.sql
    alter user xdb identified by <xdb password> account unlock;
    add a single XML DTD file to the root of the repository.

    Once this is complete, I can not longer export my database.

    If I just use the XMLTYPE datatype as one of my columns (which I store as a CLOB) without installing XDB, then I can't write to the XMLTYPE column using a java jdbc connection. I get the error:

    ORA-21700: object do
    es not exist or is marked for delete
    ORA-06512: at "SYS.XMLTYPE", line 254
    ORA-06512: at line 1

    java.sql.SQLException: ORA-21700: object does not exist or is marked for delete
    ORA-06512: at "SYS.XMLTYPE", line 254
    ORA-06512: at line 1

    Again, Oracle 10gR2.
  • 2. Re: Can't export my database using exp with XMLTYPE member column
    597647 Newbie
    Currently Being Moderated
    It feels funny replying to my own posts, but I'm making some progress so I feel the need to post the information.

    I came across the following article in metalink:

    Symptoms
    A full database export from a Oracle10g database aborts with:

    ...
    . exporting cluster definitions
    EXP-00056: ORACLE error 932 encountered
    ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
    EXP-00056: ORACLE error 932 encountered
    ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
    EXP-00000: Export terminated unsuccessfully
    EXP-00000: Export terminated unsuccessfully

    If export was started with SYS schema, a table level export may also fail with:

    ...
    Current user changed to TEST
    . . exporting table DOC_ARCHIVE 16 rows exported
    Current user changed to SYS
    EXP-00011: SYS.; does not exist
    Export terminated successfully with warnings.

    Cause
    Script $ORACLE_HOME/rdbms/admin/catmeta.sql has been run recently.

    There are several invalid SYS.KU$_% views in the dictionary:

    -- invalid objects:
    SET lines 120 pages 2000
    COL status FOR a9
    COL object_type FOR a20;
    COL owner.object FOR a50
    SELECT status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
    FROM dba_objects
    WHERE status != 'VALID' AND object_name NOT LIKE 'BIN$%'
    ORDER BY 4,2;

    STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
    --------- ---------- --------------- --------------------------------
    INVALID 7105 PACKAGE BODY SYS.DBMS_METADATA
    INVALID 6683 VIEW SYS.KU$_10_1_COMMENT_VIEW
    INVALID 6788 VIEW SYS.KU$_10_1_IND_STATS_VIEW
    INVALID 6778 VIEW SYS.KU$_10_1_PIND_STATS_VIEW
    INVALID 6752 VIEW SYS.KU$_10_1_PTAB_STATS_VIEW
    INVALID 6770 VIEW SYS.KU$_10_1_SPIND_STATS_VIEW
    INVALID 6748 VIEW SYS.KU$_10_1_TAB_ONLY_STATS_VIEW
    ... (etc)

    A query in SQL*Plus on sys.ku$_xmlschema_view also fails with ORA-932:

    SET lines 200 pages 2000
    COL url FOR a60 WRA
    SELECT url, local, stripped_val
    FROM sys.ku$_xmlschema_view;

    ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR

    Solution
    For reasons having to do with compatibility, the XDB objects cannot be created by the catproc.sql script. The script catproc.sql therefore calls the catmeta.sql script, which contains fake object views for XDB objects.
    The real object views are defined in the catmetx.sql script (this script is invoked by catxdbv.sql which is invoked by catqm.sql).

    Solution #1
    Run following scripts while connected as SYS user:
    sqlplus /nolog
    SQL> connect / as sysdba
    SQL> @?/rdbms/admin/catmetx.sql
    SQL> @?/rdbms/admin/utlrp.sql

    SQL> exit

    Afterwards, re-run the export.

    or:

    Solution #2
    Run the export with the Export DataPump client. E.g.:
    expdp system/manager directory=my_dir \
    dumpfile=expdp_full.dmp logfile=expdp_full.log full=y



    After performing those steps I was able to use the DataPump to export the database. However, I still could not use 'exp'.

    The article above mentions 'catproc' which I'm not running as part of installing the XDB schema... I only run catproc after I initally create my database. Should I be running it along with the other scripts for XDB?
  • 3. Re: Can't export my database using exp with XMLTYPE member column
    427256 Newbie
    Currently Being Moderated
    Thanks it was gr8 and helpful.
  • 4. Re: Can't export my database using exp with XMLTYPE member column
    588707 Newbie
    Currently Being Moderated
    Thanks for solution #1
    Ivan
  • 5. Re: Can't export my database using exp with XMLTYPE member column
    705722 Newbie
    Currently Being Moderated
    Thanks! That solved my problem too! ;O)
  • 6. Re: Can't export my database using exp with XMLTYPE member column
    user4273027 Newbie
    Currently Being Moderated
    Thanks a lot... that solution worked.. :-)
  • 7. Re: Can't export my database using exp with XMLTYPE member column
    scanbix Newbie
    Currently Being Moderated
    Solution #1 worked on 10g R2
  • 8. Re: Can't export my database using exp with XMLTYPE member column
    892262 Newbie
    Currently Being Moderated
    Thanks a lot , that helped me resolve my problem ........
  • 9. Re: Can't export my database using exp with XMLTYPE member column
    759323 Newbie
    Currently Being Moderated
    Thanks for the solution #1.
  • 10. Re: Can't export my database using exp with XMLTYPE member column
    970358 Newbie
    Currently Being Moderated
    I tried to export xmltype table using following command
    h4. exp xxxxx/yyyyyy FILE=xxx_yyyy_TBLOct25.dmp TABLES=xxxxx.xxx_yyyy log=xxx_yyyy_TBL25.log

    i got following error

    ------
    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
    Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

    About to export specified tables via Conventional Path ...
    Current user changed to xxxxxx
    . . exporting table yyyyyyy
    EXP-00107: Feature (BINARY XML) of column XBRLFILE in table xxxxx.yyyyyyyis not supported. The table will not be exported.
    Export terminated successfully with warnings
    -----

    Any one come across this issue ,please help.