Ant, Stored Procedures and dbms_output

0 0

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 Post Author

Aino Andriessen

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

aino.andriessen@amis.nl

Happy

0 0 %

Sad

0 0 %

Excited

0 0 %

Sleepy

0 0 %

Angry

0 0 %

Surprise

0 0 %

Related posts:

Oracle Advanced Queuing and JMS – bridging from AQ to JMS and vice versa Introducing Spring JDBC – frequently the best introduction of Spring in an organization Getting started with the EJB 3.0 Query Language (EJBQL 3.0) using GlassFish reference implementation Dinsdag 18 september: Oracle Open World & JavaOne 2012 Preview mini-conferenties – 12 presentaties op 1 avond The AMIS Summary of Oracle OpenWorld 2013 is available for download – 60-page white paper