Different Behavior If Statement After Upgrade To 11g (Doc ID 1392406.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 11.2.0.2 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

After upgrade from 10.2.0.5 to 11.1.0.6 the IF ... THEN statement is not working correctly.
IF condition THEN
statements
END IF;
If there is only one statement in the THEN clause and this statement is "null;" (doing nothing) then it looks as if the condition is not evaluated.
If the condition raises an error, this error is not trapped in the exception handler.

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Solaris: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> set serveroutput on
SQL> DECLARE
2 v_code NUMBER;
3 v_errm VARCHAR2(64);
4 BEGIN
5 IF TO_DATE('20112233','YYYYMMDD')>SYSDATE THEN
6 NULL;
7 END IF;
8 EXCEPTION
9 WHEN OTHERS THEN
10 v_code := SQLCODE;
11 v_errm := SUBSTR(SQLERRM, 1, 64);
12 DBMS_OUTPUT.PUT_LINE('Error code ' || v_code || ': ' || v_errm);
13 END;
14 /

PL/SQL procedure successfully completed.

On 10.2.0.5 it was working fine:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Solaris: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> DECLARE
2 v_code NUMBER;
3 v_errm VARCHAR2(64);
4 BEGIN
5 IF TO_DATE('20112233','YYYYMMDD')>SYSDATE THEN
6 NULL;
7 END IF;
8 EXCEPTION
9 WHEN OTHERS THEN
10 v_code := SQLCODE;
11 v_errm := SUBSTR(SQLERRM, 1, 64);
12 DBMS_OUTPUT.PUT_LINE('Error code ' || v_code || ': ' || v_errm);
13 END;
14 /
Error code -1843: ORA-01843: not a valid month

PL/SQL procedure successfully completed.

The problem happens in all versions 11.1.0.6 up to 11.2.0.3.

Changes

upgrade from 10G to 11G

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