Re: Weird issue with a PACKAGE in 11g

From: Raza Siddiqui <raza.siddiqui_at_oracle.com>
Date: Tue, 25 Aug 2015 16:51:03 -0700
Message-Id: <E329AD98-DF98-41E4-9D22-632A5130D09C_at_oracle.com>

Marcos

You need to check

1. object owner vs schema that will run / invoke the code
2. invoking privs
3. execution path, ie local, application and database (names will usually be resolved in that order)
4. you don't want to set the database parameter to compile pl/SQL code at each call. Check the various plsql compile parameters such as : PLSQL_CODE_TYPE PLSQL_NATIVE_LIBRARY_DIR and many others found in

docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm

This is a good read / reminder

https://oracle-base.com/articles/misc/recompiling-invalid-schema-objects

Reading through your original post, I would also ask the standard question, what changes were implemented just before the issue was observed ? It seems, whatever happened in the Prod environment, eventually propagated in your Dev / Test...so you should be able to back-track, if you have code-management system tracking releases and changes.

Good luck

Raza

On Aug 25, 2015, at 15:16, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> Hi, 
> 
> one further idea: could it be that there two sources (e.g. create scripts) of package a, one containing procedure d and the other one does not?
> Please define recompile. Is it "Alter package a compile;" oder did you do "create or replace package a ...".
> 
> Regards
> 
> Lothar
> 
> On 25.08.2015 23:42, Marcos Colmenares H. wrote:

>> Good Day,
>>
>> Schema would be called xxxxx and he is in fact the owner of the packages xxxx.a.d
>>
>> Best Regards,
>>
>> Marcos Colmenares H
>>
>> --
>>
>> 2015-08-25 17:03 GMT-04:30 TJ Kiernan <tkiernan_at_pti-nps.com>:
>> Is this maybe a name-resolution thing? Do you have a schema called a? Then it’d be looking for an object called d in the a schema.
>>
>>
>>
>> HTH,
>>
>> T. J.
>>
>>
>>
>>
>>
>> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Marcos Colmenares H.
>> Sent: Tuesday, August 25, 2015 4:26 PM
>> To: Raza Siddiqui
>> Cc: oracle-l_at_freelists.org
>> Subject: Re: Weird issue with a PACKAGE in 11g
>>
>>
>>
>> Hi Raza,
>>
>> Thats my main issue actually, it never shows up as invalid... it keeps showing up as valid. In the production enviroment we went as far as doing a mass recompile of all objects, but it happened again.
>>
>> Sounds interesting as a last resort, recompiling all objects every time they are accesed here would be caos.
>>
>>
>>
>> Best Regards,
>>
>> Marcos Colmenares H
>>
>> --
>>
>>
>>
>> 2015-08-25 16:53 GMT-04:30 Raza Siddiqui <raza.siddiqui_at_oracle.com>:
>>
>> Marcos
>>
>>
>>
>> Look at any dependent objects being dropped or altered...that event will cause a "stored procedure" (package spec, body, function or procedure) to become invalid.
>>
>>
>>
>> If memory serves, there's a parameter that can recompile all DB-stored pl/SQL code to be recompiled every time it's invoked....
>>
>>
>>
>> Corrections / additions welcome.
>>
>>
>>
>> Raza
>>
>>
>> On Aug 25, 2015, at 14:12, "Marcos Colmenares H." <mcolmenares_at_newtechsistemas.com.ve> wrote:
>>
>> Good Day,
>>
>> We've been experiencing waht seems to be a bug with a certain package, but because of the way it presents itself im not sure if its really a sole occurence.
>>
>> We have a package named A with an internal procedure called D (a.d). It works as intended and when we query invalid or not compiled objects everything seems OK.
>>
>> Then one of the logs shows us an error message like so
>>
>> ORA-06508: PL/SQL: could not find program unit being called a.d
>>
>>
>>
>>
>>
>> when we re-check for invalid objects (SELECT * FROM all_objects WHERE owner='xxxxx' AND status='INVALID';), everything seems fine and i see no indications of any error whatsoever, but around a month ago trying to fix the issue when it first appeared i decided to re-compile the package just to try if that fixed it, and it did. The re-compilation works without a hitch when the error occurs but its a production enviroment and now its happening on the testing enviroments as well without any indication as to why and to be honest im runing out of ideas.
>>
>>
>>
>> Any of you had a similar situation happen to you?
>>
>>
>>
>>
>> Best Regards,
>>
>> Marcos Colmenares H
>>
>> --
>>
>>
>>
>>

> 
> 
> -- 
> 
> 
> 
> 

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Aug 26 2015 - 01:51:03 CEST