Solved

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

Posted on 2007-04-06
4
5,125 Views
Last Modified: 2013-12-19
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,
0
Comment
Question by:tdsimpso
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 7

Expert Comment

by:gattu007
ID: 18864617
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
0
 

Author Comment

by:tdsimpso
ID: 18866303
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,
0
 
LVL 3

Accepted Solution

by:
biswaranjan_rath earned 500 total points
ID: 18895987
Additional Info;

You can fetch the datatype from the table ALL_TAB_COLUMNS and query against the condition table_name and column_name.

You can also use TO_CLOB() to get the CLOB value.
0
 

Author Comment

by:tdsimpso
ID: 18909370
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,
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question