If I deploy N pl/sql packages to Oracle DB, can I make their compilation atomic i.e. the changes in these packages will be applied after the successful compilation of all packages?
+2
A:
CREATE OR REPLACE and ALTER PACKAGE are DDL statements, and each single DDL statement is a discrete transaction. A COMMIT is issued before and after each DDL command; that is why there is no rollback for DDL.
It seems to me that you have a configuration management issue. And configuration management, plus source control, is the way to fix it. Keep all your PL/SQL scripts (heck , just all your scripts) under version control. When you deploy a new version of some PL/SQL programs check out the previous versions too (into a separate sub-directory, or whatever makes sense under your deploymenet regime). Then if there are any problems with the new versions of your packages it is a cinch to re-deploy the old versions.
APC
2010-04-23 09:18:10
Agree, seems a test environment would be a good idea!
Paul James
2010-04-23 10:11:07
True, but the problem is, environments are like hedge funds. Just because something deployed neatly in Test doesn't guarantee it will do so in production.
APC
2010-04-23 10:27:51
+2
A:
Since packages are editionable you could look at edition-based redefintion. This would give you a way to atomically switch between versions of your packages.
Janek Bogucki
2010-04-23 09:26:54
+2
A:
The other answers here are good (e.g. edition-based redefinition, which is available in 11gR2).
Another option is provided by PL/SQL Developer, which can be configured to do a test compile (compiles a package to an alternative name) prior to the "real" compile.
Jeffrey Kemp
2010-04-23 10:33:25