SQL & PL/SQL

We recently switched to Oracle 11g, and with the switch some of our users started complaining that one of our reports no longer worked correctly. The report is a Crystal Report that calls a PLSQL stored procedure with several parameters. The procedure itself collects records into a global temporary table and then reports from that table.

The main collection step is a UNION of two SQL queries. Both queries are of the form

SELECT t1.field1, t1.field2,...
FROM table1 t1
JOIN table2 t2
ON t2.key = t1.key
...
WHERE TRIM(t1.field1) LIKE variable1
AND TRIM(t2.field1) LIKE variable2
...

After some investigation I determined that the first of the two queries didn't work, and that TRIM(t2.field1) LIKE variable2 was the problem.

Variable2 is a VARCHAR2, and it is set to '%'. The contents of t2.field1 is never null or blank.

I found the following behavior. When I commented out TRIM(t2.field1) LIKE variable2, the query ran correctly. When I removed the TRIM, the query ran correctly. When I replaced the variable with the string token '%', the query ran correctly.

I should note that the second of the two queries worked correctly, even though it uses the same code (it references different tables).

I do not understand what PLSQL or SQL rules govern this behavior. Can someone explain it to me? Also, since this broke with the move to 11g, is there a db setting we should be investigating? Thanks.
--
Helge Moulding
University of Utah Development Office
Information Services
801-585-0756

Edited by: user2515538 on May 10, 2013 10:18 AM

Edited by: user2515538 on May 10, 2013 10:20 AM