PLS-00201: identifier 'DIRECTION' must be declared

I have had an odd effect, which I cannot explain.

In three tables in my database, I added a new column "Direction" with Alter Table. This worked fine. I was able to insert, update and select on this column.

However, when I tried to use it in a stored procedure, I got the message PLS-00201: identifier 'DIRECTION' must be declared but only on one of the tables. Essentially the same SQL statement on one of the other tables compiled OK.

My initial research indicated that the error might be related to roles not being defined for the package. This was not the case.

Finally, I tried to repeat the effect by adding another column, in this case called Axis, with the same definition. I found that it I refered to this new column, my stored procedure compiled. As a check, I switched back to the column name Direction and, lo and behold, this also compiled.

What is even stranger is, that I then dropped the column Axis and still the stored procedure compiled. Thus, by adding a new column and droping it, I fixed the compilation problem.

We have now seen exactly the same effect on a second Oracle server. In both cases it was Oracle 8.1.6.

What the hell is happening here?

Phil Jollans Received on Mon Oct 29 2001 - 08:00:48 CST