CODEWITHSUNDEEP
oracle-databaseplsqlcompilationatomic
Alexey Kalmykov
Alexey Kalmykov

Reputation: 1958

Can compilation of several Oracle Pl/SQL package be an atomic operation?

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?

Upvotes: 5

Views: 433

Answers (3)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

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.

Upvotes: 2

Janek Bogucki
Janek Bogucki

Reputation: 5123

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.

Upvotes: 3

APC
APC

Reputation: 146179

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.

Upvotes: 3

Related Questions