Unanswered: Invalidating the dependent procedures..
We have a package, which generates procedures dynamically depending on the user input and these newly generated procedures are used by some other package. Whenever this new generation of procedures happens, my dependent package is becoming invalid and is not able to use this. To use it all I need to do is just recompile the procedure. Is there anyway where in I force Oracle try to compile invalid procedures when there is a call to procedure and throw the error only if it is invalid still?
Here we are giving a free hand to the user to create his own conditions and creating formulae. To enable this feature we need to necessarily create procedures dynamically depending upon the user input.
If those procedures could vary in name and/or parameter lists, then you couldn't call them statically from existing procedures anyway - so presumably the names and parameter lists are fixed? If so, wrapping them in a package will solve your problem.