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

Question

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

TimLangner · Answer

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

VinceAngelo · Answer

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 · Answer

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-being-found-ORA-01452

Kind regards

Tim