Re: PL/SQL Procedures Get Invalidated

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sat, 22 Apr 2006 01:44:27 -0400
Message-Id: <1145684667l.3467l.0l@medo.noip.com>

On 04/21/2006 12:43:22 PM, Sam Bootsma wrote:
> We have an inconsistent, but frequent, problem in our non-production
> environment. Our web application, accessing a non-production database,
> will return a "page not found" error. The problem is always traced to
> one or more PL/SQL packages or procedures that are invalidated.
> Manually compiling the invalid object fixes the problem. Then, after a
> while, the package is invalidated again. The QA has also traced the
> source of the problem to clicking a back arrow on the web application
> (however, this does not always cause the problem, only sometimes).
>
>
>
> This problem exists only for databases on our TEST box, and all these
> databases share the same Oracle Home on this box. The PROD database, by
> itself on a separate box, does not have this problem (whew!). I tried
> to get our QA or Systems Analyst to manually run the Oracle procedure
> from Sql*Plus, but I am told "the package can not be run directly from
> sql*plus since some build in package such as OWA, which is heavily
> depend on http context, is meant to be called from http
> request/response."
>
>
>
> Can anybody provide me with a clue as to what is happening and what I
> can do to fix it? Why do we have packages that sometimes become
> invalidated?

Sam, the first thing to do would be to turn on auditing. That would help you catch the operation which invalidates the package. I obviously don't have enough information to figure that out here, so allow me to speculate:

Somebody is changing an object referenced by a package, or an object referenced by an object referenced by a package.
Somebody is gathering statistics diligently. Too diligently. I've seen people calculating statistics within the ETL script and wondering why do all procedures become invalid.
There is dynamic DDL somewhere in the loop. DDL commands like truncate or analyze executed on the tables on which the given procedure depends will normally invalidate the procedure.

In connection with that, in 10.2, I am unable to invalidate PL/SQL object, even with copious amount of DDL:

SQL> begin
  2 dbms_output.put_line(sum_mgr(10));
  3 end;
  4 /
12600

PL/SQL procedure successfully completed.

SQL> create index emp1_deptno_i on emp1(deptno);

Index created.

SQL> analyze table emp1 compute statistics for table for all indexed columns 2 /

Table analyzed.

SQL> @/tmp/1

OBJECT_NAME                         STATUS
----------------------------------- -------
EMP1                                VALID
SUM_MGR                             VALID

SQL> begin
  2 dbms_output.put_line(sum_mgr(10));
  3 end;
  4 /
12600

PL/SQL procedure successfully completed.

SQL> truncate table emp1;

Table truncated.

SQL> @/tmp/1

OBJECT_NAME                         STATUS
----------------------------------- -------
EMP1                                VALID
SUM_MGR                             VALID

SUM_MGR is a trivial function which has a hard reference to EMP table. Here is the source code:

CREATE OR REPLACE FUNCTION "SCOTT"."SUM_MGR" (d number) return number as
total_sal number(10,3);
begin
select sum(sal) into total_sal
from emp1
where deptno=d and
job != 'MANAGER';
return(total_sal);
end;
/

I created index, analyzed table and truncated it, while the function did not change the status to 'INVALID'. Personally, I find that extremely strange. In version 9.2, the one you are using, I believe that the function would go invalid. Is anybody here aware of any changes in 10.2, with respect to dependency tracking? This dependency was properly recorded:

1 select referenced_owner, referenced_type,referenced_name,dependency_type 2 from user_dependencies
3* where name='SUM_MGR'
SQL> / REFERENCED_OWNER REFERENCED_TYPE

------------------------------ -----------------
REFERENCED_NAME                                                  DEPE
---------------------------------------------------------------- ----
SYS                            PACKAGE
STANDARD                                                         HARD

SYS                            PACKAGE
SYS_STUB_FOR_PURITY_ANALYSIS                                     HARD

SCOTT                          TABLE

EMP1 What the heck is "SYS_STUB_FOR_PURITY_ANALYSIS"? I know about the dependency on package STANDARD and dependency on table EMP1. I believe that part of the answer lies there. Sam, do you see where did an attempt to answer your question lead me? To a completely new problem! Shame on you!

-- 
Mladen Gogala
http://www.mgogala.com

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Apr 22 2006 - 00:44:27 CDT