Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
EXECUTE IMMEDIATE 5 51
case statement in where clause 5 40
Converting a row into a column 2 43
execute immediate plsql block 5 33
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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

932 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

12 Experts available now in Live!

Get 1:1 Help Now