SQL task manual (http://ant.apache.org/manual/CoreTasks/sql.html) states, that "...Multiple statements can be provided, separated by semicolons (or the defined delimiter). Individual lines within the statements can be commented using either --, // or REM at the start of the line..." Having keepformat="true", ant sql task attempts to execute commented lines and fails. <target name="sql.test"> <sql driver="${database.driver}" url="${database.url}" userid="${database.user_name}" password="${database.user_pwd}" classpathref="classpath-database" keepformat="true" > <transaction> REM test </transaction> </sql> </target> Results in: java.sql.SQLException: ORA-00900: invalid SQL statement With other types of comments <transaction>--test</transaction> and <transaction>//test</transaction> execution results in: java.sql.SQLException: Invalid SQL type Having keepformat="false" all calls succeed.
Attaching error stacktrace: C:\myproject\build.xml:23: java.sql.SQLException: Invalid SQL type at org.apache.tools.ant.taskdefs.SQLExec.execute(SQLExec.java:454) at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288) at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke (DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) at org.apache.tools.ant.dispatch.DispatchUtils.execute (DispatchUtils.java:105) at org.apache.tools.ant.Task.perform(Task.java:348) at org.apache.tools.ant.Target.execute(Target.java:357) at org.apache.tools.ant.Target.performTasks(Target.java:385) at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1329) at org.apache.tools.ant.Project.executeTarget(Project.java:1298) at org.apache.tools.ant.helper.DefaultExecutor.executeTargets (DefaultExecutor.java:41) at org.apache.tools.ant.Project.executeTargets(Project.java:1181) at org.apache.tools.ant.Main.runBuild(Main.java:698) at org.apache.tools.ant.Main.startAnt(Main.java:199) at org.apache.tools.ant.launch.Launcher.run(Launcher.java:257) at org.apache.tools.ant.launch.Launcher.main(Launcher.java:104) Caused by: java.sql.SQLException: Invalid SQL type at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:208) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:67) at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout (OracleStatement.java:1168) at oracle.jdbc.driver.OracleStatement.executeInternal (OracleStatement.java:1687) at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1653) at org.apache.tools.ant.taskdefs.SQLExec.execSQL(SQLExec.java:564) at org.apache.tools.ant.taskdefs.SQLExec.runStatements(SQLExec.java:532) at org.apache.tools.ant.taskdefs.SQLExec$Transaction.runTransaction (SQLExec.java:768) at org.apache.tools.ant.taskdefs.SQLExec$Transaction.access$000 (SQLExec.java:707) at org.apache.tools.ant.taskdefs.SQLExec.execute(SQLExec.java:438) ... 16 more --- Nested Exception --- java.sql.SQLException: Invalid SQL type at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:208) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:67) at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout (OracleStatement.java:1168) at oracle.jdbc.driver.OracleStatement.executeInternal (OracleStatement.java:1687) at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1653) at org.apache.tools.ant.taskdefs.SQLExec.execSQL(SQLExec.java:564) at org.apache.tools.ant.taskdefs.SQLExec.runStatements(SQLExec.java:532) at org.apache.tools.ant.taskdefs.SQLExec$Transaction.runTransaction (SQLExec.java:768) at org.apache.tools.ant.taskdefs.SQLExec$Transaction.access$000 (SQLExec.java:707) at org.apache.tools.ant.taskdefs.SQLExec.execute(SQLExec.java:438) at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288) at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke (DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) at org.apache.tools.ant.dispatch.DispatchUtils.execute (DispatchUtils.java:105) at org.apache.tools.ant.Task.perform(Task.java:348) at org.apache.tools.ant.Target.execute(Target.java:357) at org.apache.tools.ant.Target.performTasks(Target.java:385) at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1329) at org.apache.tools.ant.Project.executeTarget(Project.java:1298) at org.apache.tools.ant.helper.DefaultExecutor.executeTargets (DefaultExecutor.java:41) at org.apache.tools.ant.Project.executeTargets(Project.java:1181) at org.apache.tools.ant.Main.runBuild(Main.java:698) at org.apache.tools.ant.Main.startAnt(Main.java:199) at org.apache.tools.ant.launch.Launcher.run(Launcher.java:257) at org.apache.tools.ant.launch.Launcher.main(Launcher.java:104)
Note: when the commented line is surrounded by correct sql clauses, script execution succeeds even with keepformat="true" i.e. <transaction> select 1 from dual; -- test select 2 from dual; </transaction> is executed without errors
This doesnt surprise me; When I took the sql code for use in my own project, I took one look at the statement parsing code and decided to leave it well alone. Anyway, thanks for the tests; we should be able to replicate this pretty easily.
The keepformat attribute was created specifically for loading sql packages, functions and procedures, in order to maintain the formatting of the files being loaded, including comments. Unless you are loading packages, functions or procedures, you probably want to leave keepformat="false". -Rob Anderson
I'd like to know if this bug is going to be fixed one day According to Bugzilla it's of "major" importance but it has not been fixed yet. Any ETA for that? Any workaround solution?
Any updates on this?
The problem described by Billy Delo seems to me out of scope. Sending this statement "REM test" with any JDBC client will fail with the very same Exception because the Oracle server just doesn't accept this statement (and I tested). REM being an SQL*Plus command only ( https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve034.htm#SQPUG054 ). Is ANT supposed to work when JDBC doesn't work ? SQL*Plus commands are not supposed to work through JDBC without modification, that's probably what the users asking for this bug want to achieve, but to solve this we would need to implement an SQL*Plus task to simulate an SQL*Plus client, which is quite different from SQL task. Please remember that comments in ANT files follow XML comments syntax (<-- comment -->). It's not difficult for the user to change his REM by an XML comment if he really wants to keep the comment but it won't be sent. There are 2 ways to send comments to Oracle server ( see https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/fundamentals.htm#i6074 ) and both are working with ANT. Here are my tests : <target name="sql.test"> <sql driver="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@localhost:1521:XE" userid="dbuser" password="dbpass" keepformat="true" output="43413.txt" print="true" > <transaction> SELECT users.first_name, users.last_name FROM users, roles WHERE users.username LIKE '%ith' AND users.username = roles.username -- single line comment UNION SELECT users.first_name, users.last_name FROM users WHERE users.last_name LIKE '%er'; </transaction> </sql> </target> <target name="sql.test"> <sql driver="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@localhost:1521:XE" userid="dbuser" password="dbpass" keepformat="true" output="43413.txt" print="true" > <transaction> /* this is a multiline comment and there is no problem */ SELECT users.first_name, users.last_name FROM users, roles WHERE users.username LIKE '%ith' AND users.username = roles.username /* another multiline comment */ UNION SELECT users.first_name, users.last_name FROM users WHERE users.last_name LIKE '%er'; </transaction> </sql> </target> Both give the expected result : SELECT users.first_name, users.last_name FROM users, roles WHERE users.username LIKE '%ith' AND users.username = roles.username -- single line comment UNION SELECT users.first_name, users.last_name FROM users WHERE users.last_name LIKE '%er'; FIRST_NAME,LAST_NAME ema,smith john,smith marcos,turner marek,miller 0 rows affected As noted by Robert Anderson it needed some clarification. Indeed, the SQL task code is not of the highest quality, but if it's working what's the next step after cynical comments ? Users who fail to run this SQL task, please show your ANT script then we can investigate your problem.