Incorrect PL/SQL Code is Executed After Upgrading to 11G (Doc ID 1061820.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version 11.1.0.7 to 11.2.0.1 [Release 11.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 23-Sep-2013***


Symptoms

A package, referenced by a synonym, is updated but dependency checking bypasses the synonym when in fast-validate mode.  This causes the synonym to reference an incorrect entry point in the package and so the wrong line of code could be incorrectly executed resulting in wrong results or ORA-7445 errors.

There are a number of ways that we can see this happening.  For example, if a package (accessed via a synonym) had either global variables or functions/procedures A, B, C in that order and B had been accessed/invoked in an active session, if the package is recreated to have A, D, B, C then accessing/invoking B again would access/invoke D instead.

The 10928 mcode event trace shows the PL/SQL lines of code which are executed.  When this event is turned on, it is possible to see an incorrect entry point in the code.  For example, to switch on the event run the following in a SQLPlus session:

SQL> alter session set events = '10928 trace name context forever, level 1';


Then run the failing procedure. The mcode will be written to a trace file in the USER_DUMP_DEST directory or BACKGROUND_DUMP_DEST directory if the connection was via shared server.

The following mcode shows a call [Line 4] to synonym.procedure B.
We can see that the next line of code [Line 8] procedure D is invoked and not procedure B.  This shows an incorrect entry point in the code.

[Line 4] syn_test.B;
SCOTT.PKG_TEST2: 00008: XCAL 2, 2
Entry #2
[Line 8] procedure D is
SCOTT.PKG_TEST: 00044: ENTER DS[0]+200 <"PRC_4"#980980e97e42f8ec


We can also see this problem occurring by looking at the last ddl time of the package.  Run the following SQL statement and check the last ddl times on the package specification and body at the point it starts to fail. If it has been recreated and access is via a synonym then you may be hitting this bug.

SQL> SELECT object_name, object_type, owner, status, last_ddl_time
     FROM dba_objects
     WHERE object_name = 'PACKAGE_NAME';

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