SQL & PL/SQL

Hello everybody,

since migrating to Oracle 9i (Release 2 and 9.2.0.3) we have the foll. problem:

The behavior when passing a to_char(null) into a function is different.

This is very problematic when you are passing data from a union select (or view with union) into a function!
You must match the datatype (using the TO_CHAR function) at the UNION-operator (ref. Oracle manual)

We launched the foll. SQL statement (Scripts for table and Package attached):

select decode (ename,'KING',to_char(null),ename)
,ml_pk_debug.debug('With nvl',decode (ename,'KING',to_char(NULL)||'HALLO',ename))
,ml_pk_debug.debug('With to_char',decode (ename,'KING',to_char(null),ename))
,ml_pk_debug.debug('W/O to_char',decode (ename,'KING',null,ename))
,ml_pk_debug.debug('WITH CASE',case when ename = 'KING' then to_char(null) else ename end)
,ml_pk_debug.debug('FM_TEST',decode (ename,'KING',to_char(null,'TEST'),ename))
from emp where ename in ('KING','FORD');


Here the results from a 8.1.7 environment:

DEBUG_DATE APPLICATION DEBUG
21.05.2003 17:05:12 With nvl HALLO
21.05.2003 17:05:12 With to_char
21.05.2003 17:05:12 W/O to_char
21.05.2003 17:05:12 WITH CASE
21.05.2003 17:05:12 FM_TEST
21.05.2003 17:05:12 With nvl FORD
21.05.2003 17:05:12 With to_char FORD
21.05.2003 17:05:12 W/O to_char FORD
21.05.2003 17:05:12 WITH CASE FORD
21.05.2003 17:05:12 FM_TEST FORD


Results from 9i Rel. 2:

DEBUG_DATE APPLICATION DEBUG
21.05.2003 17:05:50 With nvl
21.05.2003 17:05:50 With to_char
21.05.2003 17:05:50 W/O to_char
21.05.2003 17:05:50 WITH CASE
21.05.2003 17:05:50 FM_TEST
21.05.2003 17:05:50 With nvl
21.05.2003 17:05:50 With to_char
21.05.2003 17:05:50 W/O to_char FORD
21.05.2003 17:05:50 WITH CASE
21.05.2003 17:05:50 FM_TEST FORD

Results from 9i Rel. 9.2.0.3:

DEBUG_DATE APPLICATION DEBUG
21.05.2003 17:08:22 With nvl
21.05.2003 17:08:22 With to_char
21.05.2003 17:08:22 W/O to_char
21.05.2003 17:08:22 WITH CASE
21.05.2003 17:08:22 FM_TEST
21.05.2003 17:08:22 With nvl
21.05.2003 17:08:22 With to_char
21.05.2003 17:08:22 W/O to_char FORD
21.05.2003 17:08:22 WITH CASE FORD
21.05.2003 17:08:22 FM_TEST FORD


I lauched exactly the same statement via different releases and platforms with different results.

Has somebody an idea, what happens?

Thanks,

Marcus


Attached Table and Procedure Scripts (for Scott Schema):

CREATE TABLE ML_DEBUG (
DEBUG_DATE DATE,
APPLICATION VARCHAR2 (100),
DEBUG VARCHAR2 (4000) ) ;

CREATE OR REPLACE PACKAGE Ml_Pk_Debug
IS

PROCEDURE debug (i_IDENTIFIER VARCHAR2, i_value VARCHAR2);
FUNCTION debug (i_IDENTIFIER VARCHAR2, i_value VARCHAR2) RETURN VARCHAR2;

END Ml_Pk_Debug;
/

CREATE OR REPLACE PACKAGE BODY Ml_Pk_Debug
IS
PROCEDURE debug (i_IDENTIFIER VARCHAR2, i_value VARCHAR2)
IS
PRAGMA autonomous_transaction;
BEGIN


INSERT INTO ML_DEBUG VALUES
(SYSDATE, i_identifier, i_value);
COMMIT;
END;

FUNCTION debug (i_IDENTIFIER VARCHAR2, i_value VARCHAR2) RETURN VARCHAR2
IS
PRAGMA autonomous_transaction;
BEGIN


INSERT INTO ML_DEBUG VALUES
(SYSDATE, i_identifier, i_value);
COMMIT;
RETURN 'DEBUGGED!';
END;

END Ml_Pk_Debug;
/