ORACLE APPS : Invalid object in Database

Today I had issue where object in EB 11.5.10 are invalid even after recompiling.

The following error message we see when we try to recompile the object

PACKAGE BODY APPS.CZ_RUNTIMEOn line: 1041PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:Text continued in next action...

After looking at code for the pkg body we find the offending SQL is of sub query in column list of select statement.

I also try to copy pkg body form working SID to non working SID. Which leave me to believe that the problem is inside Database binary and patches or database setup specially initialization parameter.

Here is a sample code we wrote to stimulate the issue.

create or replace procedure test_prc ( devl_prj in number)
as
rul_typ number := 200;
dt date;
begin
FOR i in ( SELECT 'A','B', (SELECT SYSDATE FROM DUAL) dt from dual)
loop
select sysdate into dt from dual;
end loop;
end;

This code is working on One environment and not on other.

Here is the v$version from two two Instance

SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 ProductionTNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 – Production


After comparing the initialization file I find that the init file for the SECOND SID have two section for
# Remove after upgrade to 11.5.9 #
event="10932 trace name context level 32768"
event="10933 trace name context level 512"
event="10943 trace name context level 16384"

One was commented out and second uncommented. Looks like during upgrade process APPS DBA (that’s me ) does not expected duplicate block in init file.

Similar issue was also reported in metalink Doc ID 330426.1

After editing the init file and restarting the database. I recompile all invalid objects and it work fine.

Note: Just to Opposite the Issue we had 3 week before. I am going to summaries that one too.