Executing Oracle PL/SQL from Ant
June 13th, 2005 by Mathias MeyerWhile not being that new of a problem, I ran over it today. So if you ever run into that kind of problem, here’s what you can do. I had the specific issue of dropping all tables in a specific user’s schema. Ant’s <sql> task can’t run any PL/SQL without specific options, because as default, every single line of the input (be it a file or SQL embedded into your build file) is executed separately.
So I have a small PL/SQL script that looks like this:
table_name varchar2(30);
cursor usertables is select * from user_tables where table_name not like ‘BIN$%’;
begin
for next_row in usertables
loop
execute immediate ‘drop table ‘ || next_row.table_name || ‘ cascade constraints’;
end loop;
end;
/
Don’t worry about any inefficiencies, that this snippet might have, for now ;)
So if you stuff this piece of code into Oracle using Ant’s <sql> task, you get a nice error complaining about the second line containing declare.... We build on the following snippet of Ant build file code:
userid=“scott”
password=“tiger”
driver=“oracle.jdbc.OracleDriver”
url=“jdbc:oracle:thin:@myhost:1521:orcl”
classpathref=“classpath”
<transaction src=“drop-tables.sql”/>
</sql>
So this is the code that will throw an error when executing a bunch of PL/SQL code. But don’t worry, fixing it is just easy:
userid=“scott”
password=“tiger”
driver=“oracle.jdbc.OracleDriver”
url=“jdbc:oracle:thin:@myhost:1521:orcl”
classpathref=“classpath”
delimiter=“/”
delimitertype=“row”
keepformat=“yes”
<transaction src=“drop-tables.sql”/>
</sql>
That’s it you’re saying? Yes! Adding the attributes delimiter and delimitertype tells the task to throw the whole bunch of code directly over to Oracle without worrying about anything except a / on a single line. delimiter tells Ant to look for the / as a separator for a signle set of statements to be executed at once. Since we’re all huge fans of SQL*Plus, we’ll even tell Ant to look for the / (that’s what the attribute delimitertype is for) on a single line by itself. Only then will the bunch of statements that occured since the last / be thrown over to Oracle and be executed. Ah, the joy of delegating.
As a bonus, we’ll throw in the attribute keepformat, since we want to see the PL/SQL code executed as we have it in the file, right? But this only comes in handy when debugging Ant. Newlines will not be removed so that the output will look like the input.
June 13th, 2005 at 5:35 pm
[…] Tools Databases — Lucas Jellema @ 5:42 pm See Post Report Just read the post Executing Oracle PL/SQL from Ant on how to u […]
June 15th, 2005 at 10:51 am
[…] 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 […]
December 2nd, 2005 at 4:02 am
I just wanted to say thank you so much for providing this information. I found your blog post on Google and it solved the exact problem I was trying to solve: running PL/SQL from both ant and SQLPLUS.
December 13th, 2005 at 11:50 pm
Thank you very much for posting this. It’s an elegant solution to a simple problem. You actually solved several of my problems at once.
Keep up the good work!
March 8th, 2006 at 6:11 pm
I came across this problem recently, and tried something very very similar to what you have here, without success. Regardless, my unsophisticated method was to put things like triggers and pl/sql statements in a seperate file/script and set the delimiter to slash. Thus, no conflict with standard sql statements and no need to convert any semicolons to slashes.
Might give this method verbatim a go when I stop being lazy.
June 2nd, 2006 at 4:02 pm
Thank you so much. This post really helped me out as well.
June 7th, 2006 at 2:51 am
And me as well. Thanks.
June 22nd, 2006 at 11:19 am
Thank you very much for this tip !
August 17th, 2006 at 10:24 am
Hi,
thanks it works. But in this case i am not able to get possible compilation errors back to ANT. Is there any way how to do it? Thanks
Zdenek
September 21st, 2006 at 9:21 am
Hello,
Unfortunately, for complex PLSQL scripts involving user defined procedure, the tasks doesn’t work even with your tip.
The solution is found is:
http://incanto.sourceforge.net/usage-sqlplus.html
Here is the script:
Fred
October 5th, 2006 at 2:03 pm
The PL/SQL snippet was really useful to me as I was stuck with some silly error for dropping the user tables.
Thanks a lot. :-)
Regards
Nile
January 2nd, 2007 at 4:28 pm
Thanks work nice..
February 16th, 2007 at 1:25 pm
Scriptella (http://scriptella.javaforge.com) open source ETL tool also supports execution of PL/SQL scripts. See faq entry http://scriptella.javaforge.com/faq.html#PLSQL
April 2nd, 2007 at 3:39 pm
weird oracles!
July 11th, 2007 at 11:05 am
Great Idea and Suggesstion. Really it solves the exact problem.
Let your service continues…..Let all the glories get to you…….
Regards,
Rajasekar.PV
August 5th, 2007 at 5:55 pm
But how did you get this?
August 27th, 2007 at 1:12 pm
Works very fine !
October 10th, 2007 at 3:25 pm
can run more than one sql file under a folder in a single shot without specifying the file name ie, *.sql
October 10th, 2007 at 3:26 pm
can i run more than one sql file under a folder in a single shot without specifying the file name ie, *.sql
October 15th, 2007 at 10:02 pm
Yes, you can. Just use a fileset inside the sql task. You won\’t have control over the execution order of the files though.
October 25th, 2007 at 3:23 pm
I want you to know that we are very pleased with the quality of Article you have provided. I sincerely appreciate your responsiveness and the way you conduct. I have recommended your to others because of our satisfaction. I look forward to doing with you further comment or suggestions. regards.
January 23rd, 2008 at 8:13 pm
Thanks. Excellent tip. I’ve been wrestling with this issue off and on for a month or so.
The Ant sql task documentation leaves something to be desired.