CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Robert Munteanu
Robert Munteanu

Reputation: 68268

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 implementation?

Upvotes: 7

Views: 9988

Answers (5)

JulesLt
JulesLt

Reputation: 1785

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.

Upvotes: 4

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

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.

Upvotes: 1

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36977

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).

Upvotes: 1

Kirill Leontev
Kirill Leontev

Reputation: 10931

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.

Upvotes: 3

Tony Andrews
Tony Andrews

Reputation: 132570

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

Upvotes: 18

Related Questions