Ant, Stored Procedures and dbms_output

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

Example ant target:

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

About Author

Aino Andriessen is a consultant on Enterprise Java, ADF, PL/SQL, XML, and SOA development and is Expertise Lead on Application Lifecycle Management (ALM). He has a strong interest in ADF, SOA, Maven, architecture, quality management, delivery and application lifecycle management. Aino publishes on the AMIS technology blog and has been a presenter at the ODTUG Kaleidoscope, Oracle Open World and UKOUG TechEbs.

9 Comments

  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.

    Thanks!
    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
    Christian

  3. Mukul Gupta on

    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 :

    <do-plsql
    driver=”oracle.jdbc.driver.OracleDriver”
    url=”jdbc:oracle:thin:@${sql.db.svr}:1521:${sql.db.sid}”
    userid=”${sql.usr.server}”
    password=”${sql.pwd.server}”
    classpath=”./lib/ojdbc14.jar”
    onerror=”abort”
    delimiter=”/”
    delimitertype=”row”>
    <fileset dir=”${dir.sql.packages}/headers”>
    <include name=”print_header.sql”/>
    </fileset>
    </do-plsql>

    adym

  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?

    thx,

    adym