Ant, Stored Procedures and dbms_output

Aino Andriessen 9

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 ...

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."
    <arg line="${database.username}/${database.password}@${database.tnsnamesentry} @${sql.subdir}/execute_uttest.sql"/>

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.

Example ant target:

    <taskdef name="sqldo" classname="" classpath="..." />
    <target name="test-sqldo" description="Simple ant sqldo task">
                dbms_output.put_line ('Hello World 1');

9 thoughts on “Ant, Stored Procedures and dbms_output

  1. Hello!

    Sry, i have resolve the problem.
    When i done my ant sql task i must write print=”no”. Then it works as i needed.

    Best regards

  2. Hello,

    thank for your good documentation. i have realized the third method. it works.
    But there was one problem.

    When i execute my stored procedure, it create my .xml file. But at the end of the outputfile, the procedure write a line with “0 rows affected” and so the .xml is not wellformed.

    Why is that so and how can i resolve the problem? i want to delete the line.

    Thank you for your help!

    Best regards

  3. i need help to compile functions and procedures using ant in oracle 9i

  4. Try again with the Ant XML stuff…Forgot to mention. I am using in the Ant :

    <fileset dir=”${dir.sql.packages}/headers”>
    <include name=”print_header.sql”/>


  5. Hi,
    Trying to modify the source so that it will compile/run PL/SQL packages. The class I have will compile them, but if there are errors in the Oracle package, then they do not get displayed on the Ant console output.

    Is there a way to get that output from Oracle and display it to the Ant console?



Comments are closed.

Next Post

Use Data Tree in List of Values Window - ADF BC and UIX and JHeadstart

Recently I have had several occasions where I had to implement a List of Values, allowing the users to select a value from a large set of reference values. In several instances, this set of reference data values was quite large, and could easily be organized and presented in an […]
%d bloggers like this: