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.
March 22nd, 2010 at 8:35 am
Thanks friend, i solved my problem by referring this solution.
Well in short , the view that we used in our procedure should have defined data type,need to ensure that accurate number of column name match the selected columns from the table.else all the columns in view will be undefined and produce this error.
August 30th, 2010 at 4:12 pm
Thanks, this solved my problem (and quickly - only had to search Google for a few minutes before stumbling across your post).