ORA-22922: nonexistent LOB value

chulett · Post by **chulett** » Wed Jan 10, 2007 5:14 pm

Just curious if anyone has ever had an encounter with this Oracle error before? It turned up for the first time in one particular job the other day and I'm not really having any luck determining what might have caused this. My DBAs are a little stumped as well.

We have one table with a CLOB field. Many jobs update this field without issue each day. However, one particular job on one particular day crapped out with this error. This particular job isn't even handling large field values, nothing larger than a Varchar(200) gets put into the CLOB in this job.

The Operators took this job out of rotation until the issue is resolved, but that's complicated by the fact that it never manifested itself in any of the dev or test runs. Of course, it's one of those loverly issues that wait until Production to rear their ugly heads. :roll:

Just wondering if anyone has any pointers on things to check. This is a 7.5.1A job on HP/UX, OCI stage, 9i client, 10g target db btw.

ray.wurlod · Post by **ray.wurlod** » Wed Jan 10, 2007 6:54 pm

No runners left on base? (Sorry, couldn't resist.)

Never encountered this one, so can't really help you.

Guess Time
Has the job tried to load "" into the CLOB? Or a null?

chulett · Post by **chulett** » Wed Jan 10, 2007 7:23 pm

Not a NULL, no - but a closer examination of the log with the dreaded 'The value of the row is' message makes it look like it might have been an empty string:

<snippet>
MODFN_USER = XXXXXXX LDH_CNTNT_VAL = BGN_EFF_DT = XXXXX
</snippet>

LDH_CNTNT_VAL is the CLOB field and it shows no value for it, running right into the next field. Typically Oracle would convert that empty string into a NULL in the database, but perhaps something with the CLOB field causes it to go sideways? Hmm... have to poke around at the data this job sources as it's still staged up.

ps. Took me a minute to grok the Left On Base joke... or two... :wink:

DSguru2B · Post by **DSguru2B** » Wed Jan 10, 2007 7:27 pm

Hmm. Interesting find. Even a search on google didnt help much. Just a bunch of non answered quests. Glad you narrowed it down. How'd you do it, the "take a quick nap on the couch" trick :wink:

chulett · Post by **chulett** » Wed Jan 10, 2007 7:28 pm

That's still in the queue...