Replacing part of an Oracle package

I need to modify one procedure from within a package. I need to touch both the declaration and the implementation. As I am maintaining patch files for each modification, I would like the changes to be minimal.

Can I update the package with just the changed procedure ( if yes, how ? ) , or do I need to supply the complete package definition and implementa开发者_JAVA百科tion?

---

You need to replace the whole package specification and body - you cannot operate on just part of a package.

---

Just to contradict everyone else . . .

Technically you could do it - you could write something that would take in your patch file, retrieve the existing package source from the database (using USER_SOURCE), apply your patch, and then recompile the package using EXECUTE IMMEDIATE.

However, I don't think it would be a very good idea - patch based fixing becomes very difficult to keep track of, especially once multiple patches and multiple databases are involved. Putting the whole file into source control is a lot better - your patch should still be clearly visible.

If the patch is to a third-party package, consider wrapping it - so that everything is a straight call through except your patch. Or put your patch into a standalone package that calls the first one. There is still a danger that a change to the original package could be incompatible with your patch.

---

You cannot. As far as I remember, the only way to avoid referencing objects invalidation is not to touch your package declaration, and perform only CREATE OR REPLACE PACKAGE BODY.

---

Since the declaration changes, you might consider putting the new procedure into a new package, to avoid touching the existing one. Packages using the new version of the procedure must be adapted anyways, to reflect the change in the declaration (unless it's a new parameter with a default value).

---

If you're on Oracle 11g, and you want to minimise invalidations of other objects, make sure to place the new declarations at the end of the package spec.