random technical thoughts from the Nominet technical team

Oracle PL/SQL Compilation Error (PLS-201)

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5 out of 5)
Loading ... Loading ...
Posted by patrick on Jan 30th, 2006

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.

5 Responses

  1. Santosh jilhewar Says:

    Hi patrick,
    This is a nice article u poseted and i solved my problem by reading this article.
    Thanks

  2. Kiran Says:

    Hi Patrick

    Very helpful.

    Thanks
    Kiran

  3. costa Says:

    Thanks for this message, man. I saved me some grief while solving a production issue. I added some columns to a view which invalidated an existing view, which invalidated a package. I recompiled the view from a script (without the view columns list ) and then it worked!

  4. Patrick Says:

    Hi Costa, Kiran and Santosh, I’m really happy that this post helped you solve your problems and hopefully saved you spending as much time on it as I did. It’s fantastic that we can use the internet to do this: share knowledge, save each other having to solve the same problems.

  5. Tarun Sengar Says:

    Thanks Dude…
    You did a great job!!!
    It helped me a lot.

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.

Recent Posts

Highest Rated

Categories

Archives

Meta: