471,075 Members | 1,284 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

PL/SQL package gets invalidated seemingly at random

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
Jul 19 '05 #1
3 11623

"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
Jul 19 '05 #2
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

Jul 19 '05 #3
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


Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Roman Suzi | last post: by
20 posts views Thread by Joe | last post: by
104 posts views Thread by jayapal | last post: by
12 posts views Thread by Fett | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.