Oracle PL/SQL Compilation Error (PLS-201 or PLS-00201)
(1 votes, average: 5 out of 5)
Loading ...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 ;
(We saw the error message in the format PLS-201; I have also seen it in the format PLS-00201.) 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.
April 15th, 2007 at 11:21 am
Hi patrick,
This is a nice article u poseted and i solved my problem by reading this article.
Thanks
February 29th, 2008 at 5:43 pm
Hi Patrick
Very helpful.
Thanks
Kiran
March 12th, 2008 at 9:47 pm
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!
March 12th, 2008 at 11:09 pm
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.
May 30th, 2008 at 5:10 am
Thanks Dude…
You did a great job!!!
It helped me a lot.
November 25th, 2008 at 11:15 am
Thanks! I had almost same problem, however only your post helped.
I had a view like select a.* from table1 a, and package which used it didn’t compile.
February 19th, 2009 at 11:57 pm
Thanks for the explanation, it was logical but not easy to find. I hate that programmers usually do that.
April 7th, 2009 at 10:45 am
Thanks a lot for locating the error and debugging the code.It worked and I gained information.
August 11th, 2009 at 7:46 pm
awesome blog man! had the same problem. my problem would have taken ages to solve it wouldn’t have been for this article.
August 29th, 2009 at 6:52 am
A life saver .. took hours to try to solve it. THANK YOU
October 26th, 2009 at 8:31 am
Using OWB this type of error - refering to an item ‘UNDEFINED’ which is not present in a mapping is unusual - and therefore hard to find. Google - and your solution as one of the top hits made my day.