Ant, Stored Procedures and dbms_output

Running sql (scripts) from ant using the ant task is quite easy and straight forward. Unfortunately, the ant sql task is not really suited for running pl/sql, e.g. exec mystoredprocedure does not work. When you look in the SQLExec code you’ll see that it uses a java.sql.Statement to execute the sql and not a CallableStatement. This is a pity because it would be quite nice to run utPLSQL testcases from ant and also catch and print the (dbms_output) result.
Some work-arounds for running stored procedures and functions with the ant sql task are available :
1 – Use sql to execute a function: e.g. select myfunction from dual. This is limited to functions.
2 – A good option is to use an anonymous plsql block. You must replace the default (ant sql task) delimiter, the colon, with a (forward) slash and end the anonymous pl/sql block with a slash. If you define delimitertype=”row” than you can also spread the code over more lines:

<sql ...
    delimiter="/"
    delimitertype="row"
    >
    begin
        mystoredprocedure;
    end;
    /
</sql>

This works quite well, unfortunately it is not possible to catch dbms_output statements or the result from the stored function.
3 – Use the ant exec task and use sqlplus to run a sql script and run pl/sql.
The ant target will look like something this:

<exec executable="sqlplus"
               description="Run de test via sqlplus. NB An sqlplus client is needed."
               output="${test.result.dir}/uttest.log">
    <arg line="${database.username}/${database.password}@${database.tnsnamesentry} @${sql.subdir}/execute_uttest.sql"/>
</exec>

Since sqlplus is used, normal SQL scripts can be executed. A nice feature is that all the sqlplus output (including dbms_output; don’t forget to set serveroutput on) is catched and can be printed to the console or to a file. This is a good option, but requires the presence of sqlplus. I wonder if it works with Oracle Instant Client.

Maybe the best solution would be to create your own ant task that is specialized in executing stored procedures. This can be a tedious task, especially when it should be able to manage in and output parameters. SQLUnit could be a good choice.

But when you’re only interested in executing a simple stored procedure and catching dbms_output, like executing utPLSQL testcases, it is possible to extend (read modify) ant’s SQLExec class. I made two modifications to the execute method:
1 – Enable dbms_output with boolean res1 = conn.prepareCall("begin dbms_output.enable; end;").execute();
2 – After each statement a method, provided by Andrej Koelewijn is executed that retrieves (on the same connection), the dbms_output from the database.
This resulted in the ant SQLExecWithDbmsOutput taskdef. Also available as jar file.

    <taskdef name="sqldo" classname="nl.amis.tools.ant.taskdefs.SQLExecWithDbmsOutput" classpath="..." />
    <target name="test-sqldo" description="Simple ant sqldo task">
        <sqldo
            driver="oracle.jdbc.driver.OracleDriver"
            url="${database.url}"
            userid="${database.user}"
            password="${database.pwd}"
            classpath="${lib.dir}/ojdbc14.jar"
            onerror="continue"
            delimiter="/"
            delimitertype="row"
        >
            begin
                dbms_output.put_line ('Hello World 1');
            end;
            /
        </sqldo>
    </target>