OTBI – ERROR ORA-64204: encountered partial multibyte character

We encountered a mysterious error during processing. Here is how we investigated it using sqlXtract, and resolve the issue.

Here is the error report

Using sqlXtract we investigate the table where the data is being fetched:

In the first SQL statement we hunt down the multi byte characters, with the help of ASK TOM

https://asktom.oracle.com/ords/asktom.search?tag=identify-multi-byte-characters

with c as (select GRANT_GUID, ENTERPRISE_ID, SANDBOX_ID, NAME || ',' || DESCRIPTION || ',' || ROLE_NAME
as combined from fnd_grants)
select GRANT_GUID from c where lengthb(combined) > length(combined)

So lets pick one line and hunt down the character position

select c, length(c), lengthb(c), l from (
  select substr(s, level, 1) c, level l
  from (
    select NAME s from fnd_grants where GRANT_GUID = 'AFA102D92BF4E31AE0539E90DF0A6170'
  )
  connect by level <= length(s)
)
where lengthb(c) > 1

So the multibyte character is the 16th character in the Name. In this case ‘ – ‘ or UNISTR(‘\2013’).

This happens because the role name in Oracle Fusion 24C has a multi byte in its name:

So now we have tracked down the role being granted, the SOAP call SQL could be altered to exclude the character and resolve the issue.

More general solution

In Unicode terms, [^\x00-\x7F] matches any character with a code point from U+0080 and above, which includes all multi-byte characters and other non-ASCII characters.

Using the Regex replace for all the range of multi bytes:

Thats it. We look forward to seeing you again in our next post!