Ant task for database script

Thread view:

Bhavin - 30 Jun 2006 12:28 GMT

Hello,

Has anybody worked on ant script for executing the database scripts. I
am working on build process and in which the sql command is working
fine for simple packages and stored procs. I am working on
interdependency of packages. My ant script fetches the database scripts
checked in visual source safe for oracle 9i. At the runtime i have to
check the interdependency. The dependency of package related stored
proc is described below.

There are two packages in oracle - A & B
Few Stored Procs in Package A are dependent / calling Stored Procs in
Package B and vise a versa, in this case the precedence of Package
needs to be set.

This senario comes in when getting scripts from VSS. Ant runs all the
files one by one thus unpredictable that when Package A will be
executed and when Package B will be executed.

Any input / suggestion to this senario will be greatly appreciated.

Reply to this Message

Andy Dingley <dingbat@codesmiths.com> - 30 Jun 2006 14:35 GMT

> Has anybody worked on ant script for executing the database scripts.

I'm experimenting with it. Mainly to manage remote upgrades of
customers, to apply a bunch of individual bug fixes to them.

Fixes are stored in Subversion, which I query with Python to make an
RDF/XML document. This is then transformed by XSLT into an Ant script.
Each script maps to an Ant target.

I get two benefits from Ant:
- It's a competent SQL client that uses JDBC to connect to the DB. Our
main app is in Java / JDBC, so we can already assuem this is available.
We've had a lot of past trouble with varying behaviours under different
interactive SQL client tools.

- Ant does dependency management. If I annotate a patch script in
Subversion (I store Dublin Core on them as SVN properties) then I can
extract this into my RDF/XML document, then pass the dependency through
to Ant. Ant does the rest.

Ant also lets me use a "dry run" property to list the script names out
in sequence, without actually running them

Reply to this Message

Wibble - 03 Jul 2006 16:37 GMT

> Hello,
>
[quoted text clipped - 16 lines]
>
> Any input / suggestion to this senario will be greatly appreciated.

It sounds like your package relationships are a bit of a mess.

It doesn't really matter which order you load them. You can
have a target 'loadDatabase' depends on
'loadPackage1,loadPackage2,compilePackages'
where compile packages does something like

http://www.oracle-base.com/articles/misc/RecompilingInvalidSchemaObjects.php

The order in which you load your packages then becomes irrelevant.

Reply to this Message

Ant task for database script

Free Magazines