How to process all the sql files in a directory using ant and sqlplus

This is useful if you have a build process where you would like to run a bunch of Oracle sql scripts in a directory as part of your build.

In my current project, developers add their schema update scripts to a certain directory each sprint. This ant target will pick up all the scripts in the directory and apply them to the local schema so you (and the continuously integrated build) are always up to date.

It requires the ant-contrib plugin.

    <target name="developer.scripts.run.all" depends="db.init">
        <foreach target="developer.script.run.single" param="sqlFile">
            <fileset dir="../data/patches/${db.patches.directory}" casesensitive="no"/>
        </foreach>
    </target>
 
    <target name="developer.script.run.single" depends="db.init">
        <exec resolveexecutable="yes" executable="sqlplus" failonerror="true">
            <arg line="-s ${db.userid}/${db.password}@${db.database} @${sqlFile}"/>
        </exec>
    </target>

UPDATE 4/8/10 :



The code snippet above processes the files in random order. To process the files in alphabetical order, wrap it using sort and path like so:

    <target name="developer.scripts.run.all" depends="db.init">
        <foreach target="developer.script.run.single" param="sqlFile">
            <path>
                <sort>
                    <fileset dir="../data/patches/${db.patches.directory}" casesensitive="no"/>
                </sort>
            </path>
        </foreach>
    </target>
 
    <target name="developer.script.run.single" depends="db.init">
        <exec resolveexecutable="yes" executable="sqlplus" failonerror="true">
            <arg line="-s ${db.userid}/${db.password}@${db.database} @${sqlFile}"/>
        </exec>
    </target>