OR7 -vs- OR8 PL/SQL Functions within Selects issue/bug??

This is a little involved, but here goes...

HP System
Just converted from Oracle 7.3.4 to 8.0.5

We have a PL/SQL function that returns a code value. This function happens to be within a select in a few packages... (yes we do issue pragma restrict_references)

The function is passed a few variables, which within the function are used to lookup the appropriate code value. The variables passed in are 'typed' off of tables that have a CHAR(4) datatype.
The function checks the variables passed in against an IF/ELSIF list of hard-coded values to then determine the value to pass back.

EXAMPLE OF FUNCTION STUFF (with in_variable = 'ABC ' *** thats four characters there friend an A, B, C and a BLANK that you get from the automatic padding effect of using a char field.)

IF in_variable = 'ABC' then

     set_return_variable = 'DEF'
ELSIF in_variable = 'GH' then

    set_return_variable = 'IJK'
and so forth...

With me so far?? :)

Thus PL/SQL is doing an IMPLICIT CONVERSION (as all the doco says it will) and IS matching up 'ABC ' with 'ABC'.

Inside Oracle 7.3.x this worked fine for the past year or so. Whether we executed the function by itself, or from within a select.... Inside Oracle 8.05 this works ONLY by executing the function itself. Executing the function from within a select does not work. The only way to make it work is to RTRIM the in-variables within the IF/ELSIF statement. (which we can do, but we have mucho code and we like coding with functions)

Since it works by executing the function by itself (in 7.x & 8.0.5) why oh why, does it not work when that same function is used within a SELECT clause?????

Has anyone else experienced this?
I tend to think this is a bug, since it works fine when calling the function (thus it's not ORACLE becomming less forgiving on syntax) but doesn't work when that same function is imbedded within a SELECT clause.

LASTLY, please remove "gotakeahike" from my email address before responding. I really don't like junk email. THANKS Received on Fri Dec 17 1999 - 12:55:03 CST