A Cause of ORA-38435 in Evaluate()

I’m part way through an implementation of some ELT code that uses expression filters to parse records and assign zero-to-many “inboxes” to each row, and as is often the case with the less well-explored parts of the system it often feels like I’m hacking through the undergrowth in unknown territory.

Under such conditions it’s not surprising to hit an error that few others seem to have hit before, and for which the majority of Google hits are for that irritating type of site that merely lists all of the Oracle errors in the documentation whether or not they have information on how to fix them.

Thus it is with “ORA-38435: missing elementary attribute value or invalid name-value pairs”.

Here’s how I found the root cause of the error in my case.

It was thrown when running a SELECT statement with the EVALUATE condition. The code looks something like this (slightly obfuscated for no real reason other than paranoia):

select
 i.inbox_name,
 D.*
from
 INBOX_EXPRESSIONS i,
 DATA_TABLE D
where
 evaluate (
   I.INBOX_EXPRESSION,
     My_Event(
       D.SERVICE,
       D.FILTER,
       ... blah blah blah ...
       D.SUMMARY,
       D.IDENTIFIER).getVarChar()
 )= 1
/

Firstly, the error was thrown in a predictable fashion after something like the 450th row of the result set had been returned. I re-executed it through SQL*Plus with an arraysize of 1, slightly modified to just return the ROWID of DATA_TABLE.

This result set showed ascending values of ROWID, which agreed with the execution plan that showed a full table scan of DATA_TABLE.

The error was thrown after the ROWID “AABonGAAJAAH1UeAAA” was returned.

I modified the query to something like:

select
 i.inbox_name,
 D.ROWID
from
 INBOX_EXPRESSIONS i,
 (SELECT * FROM DATA_TABLE WHERE ROWID = 'AABonGAAJAAH1UeAAA') D
where
 evaluate (
   I.INBOX_EXPRESSION,
     My_Event(
       D.SERVICE,
       D.FILTER,
       ... blah blah blah ...
       D.SUMMARY,
       D.IDENTIFIER).getVarChar()
 )= 1
/

This returned a correct row.

I then incremented the ROWID to 'AABonGAAJAAH1UeAAB' and re-executed, getting 0 rows. I kept incrementing until I reached 'AABonGAAJAAH1UeAAF' at which point the error was thrown.

Ah hah.

From the error stack it looked as if the error was raised by Evaluate, rather than the function My_Event.GatVarChar(), so I tested that by including the call to My_Event.GetVarchar() in a query against the columns of DATA_TABLE, and to see what it was returning for each row. Hopefully I'd spot something that looked like bad syntax on the error-raising row that might trip up the Evaluate() function.

And there it was:

SERVICE=>to_number('0'),FILTER=>'abc', ... ,SUMMARY=>'System's IPs are not responding.',...'

The inclusion of a single quote in the value of the column SUMMARY caused the GetVarchar function to construct an invalid string that was then passed to Evaluate().

The fix was fairly straightforward of course -- replace every occurrence of a single single-quote with two single-quotes in the string columns.

select
 i.inbox_name,
 D.*
from
 INBOX_EXPRESSIONS i,
 DATA_TABLE D
where
 evaluate (
   I.INBOX_EXPRESSION,
     My_Event(
       D.SERVICE,
       Replace(D.FILTER,'''',''''''),
       ... blah blah blah ...
       Replace(D.SUMMARY,'''',''''''),
       Replace(D.IDENTIFIER,'''','''''')).getVarChar()
 )= 1
/

I'd not be surprised if there were other potential causes.

And by the way the handsome list of single-quotes reminds me of this:

James, while John had had "had", had had "had had"; "had had" had had a better effect on the teacher

My_Event.GetVarchar()