ORA904 invalid identifier error all of sudden

Access should be directly granted to fn_encrypt_user_pwd not via ROLE.

Here is reason:

“Oracle roles have some limitations. In particular object privileges are granted through Oracle roles can not be used when writing PL/SQL code. When writing PL/SQL code, you must have direct grants to the objects in the database that your code is accessing.”

Check the following links for more information:

http://oraclepoint.com/oralife/2011/03/14/granting-object-privilege-in-plsql-via-role-in-oracle-answer-is-no/

http://www.dba-oracle.com/concepts/roles_security.htm

Thank you for the response. I guess, it does not seem to be a privilege issue because the same SQL block is working fine when we changed from ORIGINAL CODE to NEW CODE as shown below

********
ORIGINAL CODE
BEGIN
       SELECT force_chang_pwd_yn
       INTO lv_force_change_pwd_yn
       FROM ums_mt_user
       WHERE network_id = pi_network_id
       AND login_id = pi_loginid
       AND encrypted_password_text = fn_encrypt_user_pwd (pi_network_id, pi_password);
 
       IF lv_force_change_pwd_yn = 'Y'
       THEN
             po_error_code := 1;
       END IF;
 END;
**********
NEW CODE
BEGIN
      FOR rec IN (SELECT 1
      FROM ums_mt_user
      WHERE network_id = pi_network_id
      AND login_id = pi_loginid
      AND force_chang_pwd_yn = 'Y'
      AND encrypted_password_text = fn_encrypt_user_pwd (pi_network_id, pi_password))
      LOOP
       po_error_code := 1;
       END LOOP;
 END;
*********
One more observation is - the SELECT in the ORIGINAL CODE is working fine when run independently, but fails with ORA 904 when it is in a package.

Also kindly note that ORIGINAL CODE has been running for long in our production environment and suddenly start failing with ORA 904 starting starting from 0 hrs May 19 2013 GMT
Any thoughts on sudden change of behavior...

Do you know if any change happened like, new code promotion to production OR creation of new user/schema, grants , synonymn script was executed by someone?

Any background job that ran during this timeframe?

Could you please check if this link is of any help?

http://www.techonthenet.com/oracle/errors/ora00904.php




You may also check if the function and the package are in valid state and there are no compilation errors.

ajexpert sir, as already explained the ORIGINAL CODE in above posts has been working fine for several months - so no question of basic syntax failure.

Also, all the functions and packages were in compiled state (including the package affected).

We development team does not have access to production DB. As per our production DB team NO trace of any activity that were suggested to check.

******
Do you know if any change happened like, new code promotion to production OR creation of new user/schema, grants , synonymn script was executed by someone?
Any background job that ran during this timeframe?
******

And our production environment is located in GMT+8 time zone, so we can expect any DB management activity around the time the issue started (i.e. 8am ) as it is a regular business hour.

Small correction in my previous post -
.....
.....
And our production environment is located in GMT+8 time zone, so we can NOT expect any DB management activity around the time the issue started (i.e. 8am ) as it is a regular business hour.
.....
.....

Could any one throw some light on possibility of getting the packages corrupted in run time.

If possible, what are all could be the reasons (table space, swap memory, parallel multiple accessing of same package???)