techblog 

We recently encountered an unusual problem when applying a new Oracle PL/SQL release. One of the stored procedures, a package specification, failed to compile with the following error:

CREATE OR REPLACE PACKAGE EXAMPLE_PKG AS
*
ERROR at line 1:
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-201: identifier 'UNDEFINED' must be declared
;

The main problem debugging this was that the identifier 'UNDEFINED' text gave no clues as to where the problem was. After a good deal of investigation and fruitless internet searching (which is why I am posting this) we solved the problem.

Example_pkg contains a ref cursor:

TYPE EXAMPLE_CURSOR IS REF CURSOR RETURN EXAMPLE2_VIEW%ROWTYPE;

Example2_view is a view which contains select * from another view (N.B. this is actually bad practice; the columns in the select statement should be listed explicitly):

CREATE OR REPLACE VIEW EXAMPLE2_VIEW AS
SELECT * FROM EXAMPLE1_VIEW WHERE …………;

The problem arose because the definition of Example1_view had changed in the release. Attempting to re-compile Example2_view gave compilation errors. We needed to completely re-run the create or replace .... statement for Example2_view.

After doing this the create or replace .... statements for the package specification and body succeeded.

This problem presumably arose because, even though the Example2_view definition stated select * from .... Oracle expanded this at the time this view was created and “hard-coded” the list of fields into its dictionary. After the release, the list of fields held in the dictionary no longer matched valid field names in Example1_view, which in turn caused problems for the ref cursor. Unfortunately the Oracle error message did not state which object had the problem. I believe the problem would also have occurred if Example1_view had been a table.