CODEWITHSUNDEEP
oracle-databaseplsqlpackagecompilation
Tito
Tito

Reputation: 9044

bulk compiling pl/sql procedures

i have lots of packages which needed to be compiled when i move from development to production or when we release a change request.

right now , we compile each of the packages one by one using toad or sqldbx , is there a way that i can write a batch file with sqlplus command so that i can run all my packages in one go.. like *.sql

Upvotes: 1

Views: 10493

Answers (7)

Gustavo González
Gustavo González

Reputation: 11

Package Headers first:

for i in $(ls *.hed); do sqlplus user/password @$i; done

Then package bodies:

for i in $(ls *.hed); do sqlplus user/password @$i; done

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52336

One way of tackling this is to deploy your code in the correct order.

PL/SQL packages themselves are the API for the code in the package body, and the packages themselves are not dependent on each other. Package bodies however can be dependent on packages, so if a package is recompiled than it runs the risk of invalidating package bodies that reference it.

Unfortunately it's very common to see deployments that work in this order:

create or replace Package A ...;
create or replace Package Body A ...;
create or replace Package B ...;
create or replace Package Body B ...;
create or replace Package C ...;
create or replace Package Body C ...;

This has the side-effect that if code in Package Body A is dependent on Package B, then when Package B is (re)created it invalidates Package Body A.

The correct sequence for deployment is:

create or replace Package A ...;
create or replace Package B ...;
create or replace Package C ...;
create or replace Package Body A ...;
create or replace Package Body B ...;
create or replace Package Body C ...;

If there have not been changes in the package itself then there is no need to deploy it at all, of course.

Respecting these methods should give you much fewer invalid objects.

Upvotes: 1

Rob van Wijk
Rob van Wijk

Reputation: 17705

You can execute dbms_utility.compile_schema(user,false); to compile all invalid objects in your schema at once.

You can read about that procedure here in the documentation: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_util.htm#ARPLS73226

Regards,
Rob.

Upvotes: 9

Shamit Verma
Shamit Verma

Reputation: 3827

You can put all the SQLs in a text file and execute that by:

SQL >  @/path/script.sql

You just need to provide path of script to be executed.

Upvotes: 3

tbone
tbone

Reputation: 15473

you can use dba_objects to check for invalid objects and use dynamic sql to generate compile statements, something like:

select 'alter ' || object_type || ' ' || owner || '.' || object_name || ' compile;'
from dba_objects
where status = 'INVALID'
and object_type in ('PACKAGE', 'PROCEDURE', 'FUNCTION');

you can then put that into a sql script.

You can also look into utl_recomp package

Upvotes: 0

user123664
user123664

Reputation:

Normally when we do lots of changes in a database that invalidates lots of objects, the easiest way to get them recompiled is by running sqlplus "/ as sysdba" @?/rdbms/admin/utlrp This procedure gets smarter every release and from 10g it uses the Oracle Scheduler to work in parallel. This of course only works with dba access to the database. If you lack that Rob van Wijk's answer is the way to go.

Upvotes: 3

pablo
pablo

Reputation: 408

My approach would be to copy all package scripts into a directory then create a single sql script in that directory to load all packages, see example below.

-- load package specifications
@@package1.pks
@@package2.pks

-- load package bodies
@@package1.pkb
@@package2.pkb

Upvotes: 1

Related Questions