What does following mean? ORA-00932: inconsistent datatypes: expected - got NCLOB

[TimLangner]

Hi there

I have a feature class which I created thorugh ArcSDE 9.3.1, using ArcCatalog 10. This is stored in Oracle 10g.

One of the columns is a string column of 1073741822 characters. This is stored in Oracle as a NCLOB column. I choose this number because I didn't know what my longest string in ArcMap would be.

When I try to sort on this column I am getting the following error:
ORA-00932: inconsistent datatypes: expected - got NCLOB.

Can anyone tell me why that occurs?

Kind regards

Tim

[VinceAngelo]

In Oracle no string can exceed 4000 bytes (4000 ASCII/UTF-8 characters or 2000 UTF-16
characters). That's probably four times higher than I'd allow. If you have strings that could
stretch two gigabytes in length, they are not strings, they're documents. Documents are
stored in LOBs (CLOB for UTF-8, NCLOB for UTF-16). When LOBs are tossed around, you
wind up needing several copies of the bytestream as it propagates into the database.
Your 2Gb doc could easily use 4-18Gb of RAM each time it was touched, but it could only
do so if you raised the default limits on document size of 1-4Mb.

The error message you specified is an Oracle one, but should only occur when the app
and database disagree on datatypes, which makes me wonder if you've been editing the
table schema outside of ArcGIS' ken.

- V

[TimLangner]

Hi vangelo

Thank you for your reply. Sorry for not replying sooner but I appear to have missed reading your reply on here before now.

I haven't been editing the tables outside of ArcMap. I only choose that large figure to make sure I didn't ever ran out of space when typing in text. As ArcCatalog only displays the column type as text I didn't consider what impact it would have since I knew I would never reach that amount or even come close. I don't think it was a random number. From memory I think it was the largest one I could enter into ArcCatlog. However colleague was told that in theory any number could be entered for an NCLOB so I may be misremembering.

I didn't know what was going on underneath when I created it because ArcSDE hides all of the underlying data types and just lumps them all under the term text. I just assumed it would be okay because it is only a text column I am creating. However it appears not to have been okay because it is stored as NCLOB and that is causing problems as I am now finding out.

I've currently got a support call with ESRI UK technical support and now appears that this error is related to that support call, not that I knew it at the time I raised that call. I have another forum thread I posted, which now is likely to be linked to this issue. I am about to update that thread in case it helps other people:
http://forums.arcgis.com/threads/68053-Feature-class-stuck-in-LOAD-ONLY-mode-due-to-duplicate-keys-b...

Kind regards

Tim

[VinceAngelo]

It's not ArcSDE which is hiding the types involved -- the API is quite clear on which is which type
(just run 'sdetable -o describe') -- it's actually ArcGIS which is doing the generalization.

The actual Oracle CLOB maximum is 4Gb, but ArcSDE uses the SE_CLOB_INFO storage structure,
which limits the absolute length to 2^31-1. 

As you can see, arbitrary length LOB types are difficult to use at times; at a minimum, you need to
work with a different set of helper functions than the usual string operators (and there's fewer LOB
functions).

I don't see any correlation between these two very different problems (besides low-level corruption
of your Oracle instance) -- they might share the same cause, but one doesn't cause the other.

- V

[TimLangner]

It's not ArcSDE which is hiding the types involved -- the API is quite clear on which is which type
(just run 'sdetable -o describe') -- it's actually ArcGIS which is doing the generalization.

The actual Oracle CLOB maximum is 4Gb, but ArcSDE uses the SE_CLOB_INFO storage structure,
which limits the absolute length to 2^31-1. 

As you can see, arbitrary length LOB types are difficult to use at times; at a minimum, you need to
work with a different set of helper functions than the usual string operators (and there's fewer LOB
functions).

I don't see any correlation between these two very different problems (besides low-level corruption
of your Oracle instance) -- they might share the same cause, but one doesn't cause the other.

- V

Thank you for your reply. That is really interesting and useful information. I don't have access to the sdetable command myself because I am not the administrator but a colleague has access.

I guess it would be helpful if ArcGIS didn't generalise the terms. Had I been presented with the underlying Oracle terms I would have seen the word NCLOB and though is this what I actually require? I would have then looked up the format and probably decided no. However as I was presented with the word text, I didn't look anything up as I didn't see that I would need to.

I guess giving the actual format names would make a good idea for the ESRI ideas site, so I will post something when I get time.

Kind regards

Tim