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>
call mystoredprocedure();
/
–nagaraj mamedi
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
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
Incanto has a task for directly executing SQL*Plus. I’ve testet it with Oracle’s Instant Client and haven’t recognized any problems yet.
You can find the description of the task here:
http://incanto.sourceforge.net/usage-sqlplus.html
I wrote a small article about how to put the Instant Client to work:
http://mauszeig.wordpress.com/2006/04/10/jump-start-oic-incanto/
alex
i need help to compile functions and procedures using ant in oracle 9i
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
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
I’ve added the source.
can you provide a the source code of your class ?