CODEWITHSUNDEEP
oracle-databaseexceptionplsql
user3249281
user3249281

Reputation: 535

ORA-01422 error on recompilation/revalidation (NOT EXECUTION) of package/trigger

I'm running into a problem where I'm trying to grant execution on a package to another schema.

GRANT EXECUTE ON PP.PKG_PROF TO PPSERVICE;

Looks like Oracle attempts to recompile/revalidate the package before making the grant. However it is failing with the following error:

GRANT EXECUTE ON PP.PKG_PROF TO PPSERVICE
Error report -
SQL Error: ORA-04045: errors during recompilation/revalidation of PP.PKG_PROF
ORA-20000: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 83
04045. 00000 -  "errors during recompilation/revalidation of %s.%s"
*Cause:    This message indicates the object to which the following
       errors apply.  The errors occurred during implicit
       recompilation/revalidation of the object.    
*Action:   Check the following errors for more information, and
       make the necessary corrections to the object.

But if i look at the code on line 83, it executes the following query:

select 'x' into vtemp 
from cust_field_vals
where cust_fields = vin_cust_fields
and userid = vin_user_id;

vin_cust_fields and vin_user_id are parameter based values that are provided when the procedure in the package gets called.

My question is: what in the world is oracle doing? I understand that a "SELECT INTO" can theoretically return more than the requested number of rows (which would need to be one in this case), but since it doesn't know what my vin parameters are, how can it make that assessment? Why is a recompilation/revalidation throwing what essentially amounts to an exception for a data anomaly which it shouldn't even be looking at for what I'm trying to do (ie: i'm not trying to actually execute the procedure).

This is not the first time I've seen this, and if I remember correctly, I even think it's happened on recompilation of triggers as well (not when inserting data).

Any thoughts? Thanks!

Upvotes: 1

Views: 3427

Answers (1)

user3249281
user3249281

Reputation: 535

apparently, the problem was the package i was trying to GRANT execute on was "invalid" even though there were no actual code errors. Compiling the body first was generating the above mentioned error. If I manually compiled the package spec first, then the body, the error went away and the grant executed normally. No code errors were present in the package, it was just stuck in an invalid state it could not get out of.

Upvotes: 1

Related Questions