While 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.
[...] Tools Databases — Lucas Jellema @ 5:42 pm See Post Report Just read the post Executing Oracle PL/SQL from Ant on how to u [...]
[...] 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 [...]
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.
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!
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.
Thank you so much. This post really helped me out as well.
And me as well. Thanks.
Thank you very much for this tip !
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
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
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
Thanks work nice..
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
weird oracles!
Great Idea and Suggesstion. Really it solves the exact problem.
Let your service continues…..Let all the glories get to you…….
Regards,
Rajasekar.PV
But how did you get this?
Works very fine !
can run more than one sql file under a folder in a single shot without specifying the file name ie, *.sql
can i run more than one sql file under a folder in a single shot without specifying the file name ie, *.sql
Yes, you can. Just use a fileset inside the sql task. You won\’t have control over the execution order of the files though.
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.
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.
Another way that supports execution of SQL is at paperlessme.wordpress.com
Awesome, i tried and it worked fine. But one thing i have i mind is to execute a list of sql scripts file using a simgle task(eg fileset type of mechanism).
Thanks.
Excellent …