Query With Like in Where Clause Returns No Rows After Upgrade to 9.2.0.7

(Doc ID 550123.1)

Last updated on MARCH 17, 2008

Applies to:

Oracle Financial Services Applications (OFSA) 4.5.39
Oracle Financial Data Manager - Version: 4.5.39
This problem can occur on any platform.

Symptoms

After upgrading the OFSA database from Oracle 9.2.0.5 to 9.2.0.7, a SQL query that uses "like" in its where clause against a column with a datatype of CHAR returns no rows in SQL Talk. The same query returned rows in Oracle 9.2.0.5.

Example:

select to_char(CURRENT_EOM,'mm/dd/yyyy') from naa_processdate where APPLICATION = 'ALLOCATION';
-- Returns rows

select to_char(CURRENT_EOM,'mm/dd/yyyy') from naa_processdate where APPLICATION LIKE 'ALLOCATION';
-- No rows returned in Oracle 9.2.0.7

The APPLICATION column has the following definition:
APPLICATION NOT NULL CHAR(15)

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms