Ant, Stored Procedures and dbms_output

9
Share this on .. Tweet about this on Twitter0Share on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on TumblrBuffer this page

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>

About Author

Aino Andriessen is principal consultant and expertise lead 'Continuous Delivery'. His focus is on Oracle Fusion Middleware ADF and SOA development, Continuous Delivery, architecture, improving the software development proces and quality management. He is a frequent presenter at Oracle Open World, ODTUG Kaleidoscope, UKOUG Technology Conference and OUGN Vårseminar. He writes articles and publishes at the AMIS technology blog (http://technology.amis.nl/blog/).

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