PL/SQL package gets invalidated seemingly at random

[Javier Sanchez]

Hi all,
I am experiencing a problem whereby a PL/SQL package is valid for
some time (and runs succesfully a few times) and then suddenly becomes
invalid in DB. If I recompile this package it works again for some
time and then again gets invalidated. Does anyone have a suggestion or
idea of what might cause such strange behavior?

Also, does anyone know a way of obtaining error information from DB
that reports why a package was flagged as invalid?
Finally, I would appreciate help on how to obtain dependencies (both
direct and indirect) for a package? (I read in the documentation that
a change in a dependency can cause a package to be invalid)

Thanks for your help, regards,

-Javier

[Mark C. Stock]

"Javier Sanchez" <ja********************@hotmail.com> wrote in message
news:97**************************@posting.google.c om...
| Hi all,
| I am experiencing a problem whereby a PL/SQL package is valid for
| some time (and runs succesfully a few times) and then suddenly becomes
| invalid in DB. If I recompile this package it works again for some
| time and then again gets invalidated. Does anyone have a suggestion or
| idea of what might cause such strange behavior?
|
| Also, does anyone know a way of obtaining error information from DB
| that reports why a package was flagged as invalid?
| Finally, I would appreciate help on how to obtain dependencies (both
| direct and indirect) for a package? (I read in the documentation that
| a change in a dependency can cause a package to be invalid)
|
| Thanks for your help, regards,
|
| -Javier
a change in dependencies is the typical reason

use the data dictionary view USER_DEPENDENCIES or DBA_DEPENDENCIES to
(recursively) examine objects that a package depends on

whenever possible, avoid making changes to the package specification -- only
work with the package body, this reduces invalidation of dependent packages
and procedures

-- mcs

[babu]

You can try setting the dependencies_mode from time_stamp to signature.

Babu
"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message
news:8u********************@comcast.com...

"Javier Sanchez" <ja********************@hotmail.com> wrote in message
news:97**************************@posting.google.c om...
| Hi all,
| I am experiencing a problem whereby a PL/SQL package is valid for
| some time (and runs succesfully a few times) and then suddenly becomes
| invalid in DB. If I recompile this package it works again for some
| time and then again gets invalidated. Does anyone have a suggestion or
| idea of what might cause such strange behavior?
|
| Also, does anyone know a way of obtaining error information from DB
| that reports why a package was flagged as invalid?
| Finally, I would appreciate help on how to obtain dependencies (both
| direct and indirect) for a package? (I read in the documentation that
| a change in a dependency can cause a package to be invalid)
|
| Thanks for your help, regards,
|
| -Javier
a change in dependencies is the typical reason

use the data dictionary view USER_DEPENDENCIES or DBA_DEPENDENCIES to
(recursively) examine objects that a package depends on

whenever possible, avoid making changes to the package specification -- only work with the package body, this reduces invalidation of dependent packages and procedures

-- mcs

[Kev]

The reason for the package becoming invaild should be logged into the
v$errors table as well.
"babu" <ba**@insightbb.com> wrote in message
news:B8gMb.25838$nt4.49443@attbi_s51...

You can try setting the dependencies_mode from time_stamp to signature.

Babu
"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message
news:8u********************@comcast.com...

"Javier Sanchez" <ja********************@hotmail.com> wrote in message
news:97**************************@posting.google.c om...
| Hi all,
| I am experiencing a problem whereby a PL/SQL package is valid for
| some time (and runs succesfully a few times) and then suddenly becomes
| invalid in DB. If I recompile this package it works again for some
| time and then again gets invalidated. Does anyone have a suggestion or
| idea of what might cause such strange behavior?
|
| Also, does anyone know a way of obtaining error information from DB
| that reports why a package was flagged as invalid?
| Finally, I would appreciate help on how to obtain dependencies (both
| direct and indirect) for a package? (I read in the documentation that
| a change in a dependency can cause a package to be invalid)
|
| Thanks for your help, regards,
|
| -Javier
a change in dependencies is the typical reason

use the data dictionary view USER_DEPENDENCIES or DBA_DEPENDENCIES to
(recursively) examine objects that a package depends on

whenever possible, avoid making changes to the package specification --

only

work with the package body, this reduces invalidation of dependent

packages

and procedures

-- mcs