Solved

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

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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‚Ķ
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

837 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