PLS-00417: unable to resolve "COLUMN_NAME" as a column

[MikeJones]

Hi all,

I'm getting the following trying to compile a package:

PLS-00417: unable to resolve "USE_LOCK_YN" as a column

The statement is doing an update setting this value to N, The table belongs to the schema I am logged in as, as does the package I am trying to create.

I've checked the table and the column appears OK when describing the table.

Furthermore, I can do the following

UPDATE my_table
set use_lock_yn = 'N'
/

and this updates all the rows on the table.

I've even created a test procedure which falls foul of the same problem...

SQL> create procedure test_mike_TEST IS
2 CURSOR C_GET_LOCK_VAL IS
3 SELECT USE_lock_yn
4 from my_table;
5 begin
6 null;
7 end;
8 /

Warning: Procedure created with compilation errors.

real: 150
SQL> sho errors
Errors for PROCEDURE TEST_MIKE_TEST:

LINE/COL ERROR
-------- ---------------------------------------------------
3/1 PL/SQL: SQL Statement ignored
3/8 PLS-00201: identifier 'USE_LOCK_YN' must be declared
real: 160
SQL>

I figure it must be something to do with the fact that it's in a package, but I have no idea what, can anyone help?


Mike.

 

[Kenrae]

The only thing I can say is try to use an alias, or replace use_lock_yn with my_table.use_lock_yn... If you haven't thought about it yet.

 

[rishiu]

Mike,
It seems a priviledge problem, I tried this and
goes smoothly! hv u got create and select permissions?

 

[MikeJones]

All the tables / packages are under the same schema and I am logged in as this schema!!

Here are some more details...

I can select/update all the other column on the table fine! and this column (use_lock_yn) was recently added to thew table via the command...

ALTER TABLE my_table ADD (use_lock_yn varchar2(1) default 'N' not null);

ALTER TABLE my_table
ADD CONSTRAINT use_sec_c_ck4 CHECK (use_lock_yn in ('Y','N'));

I'm considering checking myself into the local nutters clinic!!

 

[rishiu]

Which version u r using? try to create or replace
option in the create procedure. I tried all this and
doesn't face any probs.

 

[MikeJones]

This is all on Oracle 8.1.6.0.0 on HP-UX B.11.00 U 9000/800

 

[MikeJones]

For those of you that are interested we discovered the solution to this problem. The following is a straight cut and paste from an SQL Plus session....

SQL> declare
2 cursor c_cur is
3 select use_lock_yn from nim_sec_users;
4 begin
5 null;
6 end;
7 /
select use_lock_yn from nim_sec_users;
*
ERROR at line 3:
ORA-06550: line 3, column 8:
PLS-00201: identifier 'USE_LOCK_YN' must be declared
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored


real: 331
SQL> select use_lock_yn from nim_sec_users;

U
-
N
N
N
....

Clearly something was up with the Oracle data dictionary.

We exported/imported the table and then recompiled the schema, and voila! the problem was no more.