[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2007-04-06
4
Medium Priority
?
5,215 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 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

656 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