Incorrect PL/SQL Code is Executed After Upgrading to 11G
Last updated on JULY 05, 2017
Applies to:PL/SQL - Version 22.214.171.124 to 126.96.36.199 [Release 11.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 23-Sep-2013***
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:
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.
SCOTT.PKG_TEST2: 00008: XCAL 2, 2
[Line 8] procedure D is
SCOTT.PKG_TEST: 00044: ENTER DS+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.
WHERE object_name = 'PACKAGE_NAME';
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms