HI, i have a problem with Oracle SP.....

[somaiyatushar11]

i have a simple SP which gives me information from 5 different tables... This SP compiles successfully... But sometimes SP gets dropped automatically.. or i don't know what happens but i have to recompile it and then execute it...this happens very regularly....

i don't know if anything is wrong with the SP or server settings or what.. because other 40 SPs run fine. i have this peculier problem with this SP only...

if anybody knows why this happens or / and how to solve it then please let me know. if required i can send the SP code to you.

 

[carp]

As was mentioned in your identical post in another forum - if any of the tables and/or procedures/functions your procedure references get dropped or modified, your procedure will become invalid and need to be recompiled (and possibly rewritten).

 

[dshachar]

if your procedure A is calling another procedure B.
and you make change on B, you have to compile A to. the solution is to use a package and insert all the joined procedure into this package.