asked on

Insert/Select statement generates error: ORA-00932: inconsistent datatypes: expected CLOB got -

I have a query that looks like this and it works fine. The Large XML document breaks down into individual rows the way I like it.

SELECT VALUE(p)
FROM load_pubmed_xml_tmp w,
TABLE(XMLSEQUENCE(EXTRACT(w.xml_data, '/PubmedArticleSet/*'))) p
WHERE w.name = 'TW_2007.xml';

But when I try to insert the rows into another table with this statement, I get an error.
insert into load_pubs_xml_tmp (pub)
SELECT VALUE(p)
FROM load_pubmed_xml_tmp w,
TABLE(XMLSEQUENCE(EXTRACT(w.xml_data, '/PubmedArticleSet/*'))) p
WHERE w.name = 'TW_2007.xml';

The Error is: "ORA-00932: inconsistent datatypes: expected CLOB got -"

1. I can't figure out what datatype of "VALUE(p)" is.
2. If "VALUE(p) is not a CLOB, then how can I cast it into a clob?

Thanks,

gattu007

the below metalink note may help

Subject: Ora-00932 inconsistent datatypes: expected BLOB, CLOB got CHAR during export
Doc ID: Note:396454.1

Cause

Problem is due to two related bugs, Bug 5566304 and Bug 4886376 currently still being resolved.

If database was created with nls_length_semantics='CHAR' and multibyte character set (eg: AL32UTF8) then export will give these errors or
select * from SYS.KU$_XMLSCHEMA_VIEW; also returns same errors.
Solution

Workarounds:

1. Create database again with NLS_LENGTH_SEMANTICS=byte
After database creation, change NLS_LENGTH_SEMANTICS=CHAR

or try following workaround

2.

1. Deinstall XDB with catnoqm.sql.
Run $ORACLE_HOME/rdbms/admin/catnoqm.sql as sys user

2. Set NLS_LENGTH_SEMANTICS=byte in initsid.ora or alter spfile and reinstall XDB views with catqm.sql
Run following connected as sys user

catqm.sql <XDB_password> <XDB_TS_NAME> <TEMP_TS_NAME>
example

3. Now try the query. It should return proper results this time.

select * from SYS.KU$_XMLSCHEMA_VIEW;

4. If you wish, change NLS_LENGTH_SEMANTICS=char
and restart the database.

select * from SYS.KU$_XMLSCHEMA_VIEW; should still work and export should no longer give the same errors.
References
Bug 4886376 - INVALID SYS.KU$_XMLSCHEMA_VIEW AFTER CREATING DB WITH NLS_CHARSEMANTICS=CHAR
Bug 5566304 - EXPORT FAILS WITH ORA-00932 AFTER CREATING DB WITH NLS_LENGTH_SEMANTICS=CHAR

tdsimpso

ASKER

Recreating the database or reinstalling the XDB is not really an option for me. Additionally, I think these two options are unrelated overkill for my problem.

The following statement performed the results that I expected.

insert into load_pubs_xml_tmp (pub)
SELECT VALUE(p).getClobVal()
FROM load_pubmed_xml_tmp w,
TABLE(XMLSEQUENCE(EXTRACT(w.xml_data, '/PubmedArticleSet/*'))) p
WHERE w.name = 'TW_2007.xml';

I added the getClobVal(). It appears that VALUE(p) returns an XMLType.

Thanks,

ASKER CERTIFIED SOLUTION

Biswaranjan Rath

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tdsimpso

ASKER

biswaranjan_rath,
I understand the use of TO_CLOB(), but how would fetching the datatype from ALL_TAB_COLUMNS help? How would I write that?
Thanks,