a development team dedicated to Oracle APEX

Underscore acts as wildcard in PL/SQL INSTR function for CLOB

leave a comment »

Oracle, Windows 32-bit, NLS_CHARACTERSET AL32UTF8.
We have a PL/SQL utility function that retrieves the nth item from a delimited string. The string parameter is a CLOB so that it can be used with input of any length. Normally the delimiters are commas, and the function has worked fine for years.
An APEX user’s sort order preference for a static report is stored in column WWV_FLOW_PREFERENCES$.ATTRIBUTE_VALUE as a string that looks something like fsp_sort_1_desc. We used our function to retrieve the various parts of this string, with ‘_’ as the delimiter, but it always returned a null. To our surprise it turned out that the underscore acted as a wildcard and matched any character in the string. That is expected behaviour in a SQL LIKE clause, but should not happen in PL/SQL. There is in fact a bug report in Oracle Metalink which describes this behaviour. There is no fix for Oracle version 10g – the bug is said to be fixed in Oracle 11.2. Our workaround was to replace all the underscores with another delimiter before calling the function.

Written by Roger

19 February, 2009 at 11:06 am

Posted in Uncategorized

Tagged with , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: