Solved

ORA904 invalid identifier error all of sudden

Posted on 2013-05-19
7
559 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.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

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