?
Solved

ORA904 invalid identifier error all of sudden

Posted on 2013-05-19
7
Medium Priority
?
576 Views
Last Modified: 2013-08-26
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.
0
Comment
Question by:damarasa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 14

Expert Comment

by:ajexpert
ID: 39179772
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
 

Author Comment

by:damarasa
ID: 39179897
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
 
LVL 14

Expert Comment

by:ajexpert
ID: 39179929
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 14

Expert Comment

by:ajexpert
ID: 39179977
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
 

Author Comment

by:damarasa
ID: 39180118
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
 

Author Comment

by:damarasa
ID: 39180123
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
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 39180850
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

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question