ORA904 invalid identifier error all of sudden

We have a strange issue -

we have a user_authentication package in which below code is a part:
****
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;
*****

The below part of the code snippet started failing all of sudden from 0hrs GMT on 19th May 2013 with ORA 904 invalid identifier error.

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);


we are using oracle 10g standard edition - no upgrade happened in recent past on either our oracle server or our schema.

Strange thing is above SQL works fine if we run it autonomously. However, if we run the package it is resulting ORA 904 error.

Greatly appreciate a quick feedback on this.

Thanks in advance.
damarasaAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I tend to agree with ajexpert that it is a permission issue.  Just because it runs from a sqlplus or similar session doesn't help much.  Packages are executed by the Oracle engine and need explicit grants.

Also, I think the new code is ,much less efficient than the old code.  You don't need the loop.    If multiple records can exist, just select one of them.

BEGIN
      --in it's own pl/sql block to handle the local exception
      begin
      SELECT 1 into po_error_code
      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)
      WHERE ROWNUM=1;

      exception when no_data_found then
           po_error_code=0;
      end;
 END;

Open in new window

0
 
ajexpertCommented:
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
0
 
damarasaAuthor Commented:
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...
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ajexpertCommented:
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?
0
 
ajexpertCommented:
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.
0
 
damarasaAuthor Commented:
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.
0
 
damarasaAuthor Commented:
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???)
0
All Courses

From novice to tech pro — start learning today.