Solved

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

Posted on 2007-04-06
4
5,070 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
  • 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
automatic email alert 1 50
Queries 15 38
exp/imp 25 73
Import and exporting Oracle Data with encrypted columns 4 26
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now