Executing PL/SQL from ANT - how to get the output
Javaddicts and the Amis blog are reporting about executing pl/sql statements from Ant: Executing Oracle PL/SQL from Ant, Executing PL/SQL from ANT – how to keep the format straight.
I’ve also written an Ant target for my current project where we execute pl/sql. The pl/sql queries domain values from some tables and outputs an xml document containing these domains. The problem with Ant’s sql task is that it doesn’t display the output created using dbms_output. So i’ve extended the SQLExec class, to copy all the output created using dbms_output to standard out. This is done in the printDbmsOutputResults method:
/**
- Print dbms_output results
*/
String getLineSql = “begin dbms_output.get_line(?,?); end;”;
CallableStatement stmt = conn.prepareCall(getLineSql);
boolean hasMore = true;
stmt.registerOutParameter(1, Types.VARCHAR);
stmt.registerOutParameter(2, Types.INTEGER); while (hasMore) {
boolean status = stmt.execute();
hasMore = (stmt.getInt(2) == 0); if (hasMore) {
out.println(stmt.getString(1));
}
}
stmt.close();
}
July 17th, 2005 at 1:06 am
Hi can you publish that ant task you are using and the way to extent the sqlexec class . (not all of us are java programmers)
Thanks.
August 8th, 2005 at 12:27 pm
Hi,
Very interesting stuff and very usefull especially when using utPLSQL. But I am very curious about extending the SQLExec class. Did you make a subclass or did you modify the SQLExec class?
I do not see a way to accomplish it by subclassing SQLExec, what am I missing?
Ciao
August 8th, 2005 at 12:52 pm
True… I started out subclassing SQLExec, but ended up including most of the source code of the original SQLExec task. SQLExec isn’t really useful for subclassing…
August 11th, 2006 at 7:43 pm
Hi,
I am just curious as to why some one would want to access sql or pl sql or anonymous sql from a ant task ?
Can you guive me a couple of real world scenarios ?
Raj
August 15th, 2006 at 7:35 pm
We have some tables with data which are required in an offline application. We query these tables in a plsql procedure, using dbms_output to print it into a xml document. This document is included in the application jar and loaded at runtime using castor. Using plsql in my ant build ensures that i always have the most uptodate data in my offline application.