Solved

ORA904 invalid identifier error all of sudden

Posted on 2013-05-19
7
557 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Create index on View 27 52
oracle query help 36 66
Pl/SQL Query 31 61
How to return an OUT parameter from and ORACLE 3 35
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now