a development team dedicated to Oracle APEX

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

with 2 comments

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

February 19th, 2009 at 11:06 am

Posted in Uncategorized

Tagged with , , ,

2 Responses to 'Underscore acts as wildcard in PL/SQL INSTR function for CLOB'

Subscribe to comments with RSS or TrackBack to 'Underscore acts as wildcard in PL/SQL INSTR function for CLOB'.

  1. How do you replace data in CLOB which is bigger then 32k in size? SUBSTR function is not able to do that.
    Any quick hint but to write own function to do that?
    Damir Vadas


    3 Apr 11 at 23:52

  2. We use the Oracle REPLACE function – works fine with a CLOB > 32k on 10g.


    6 Apr 11 at 14:59

Leave a Reply