Error executing SQL with Apache Ant, but not Oracle SQL Developer

Question

Following is a trigger:

CREATE OR REPLACE TRIGGER "CMDC"."USER_ROADS_UC"
BEFORE INSERT OR UPDATE OF
    ASSOCIATED_PARENT_ROAD
ON USER_ROADS
REFERENCES NEW AS NEW
FOR EACH ROW BEGIN
    :new.ASSOCIATED_PARENT_ROAD:=upper(:new.ASSOCIATED_PARENT_ROAD);
    EXCEPTION
    WHEN OTHERS THEN RETURN;
END;
/
ALTER TRIGGER "CMDC"."USER_ROADS_UC" ENABLE

The above runs perfectly in SQL Developer. However, when I get the following error when I run it using Ant:

[sql] Failed to execute:  EXCEPTION WHEN OTHERS THEN RETURN
[sql] java.sql.SQLException: ORA-00900: invalid SQL statement
[sql] Failed to execute:  END
[sql] java.sql.SQLException: ORA-00900: invalid SQL statement
[sql] Failed to execute:  / ALTER TRIGGER "CMDC"."USER_ROADS_UC" ENABLE
[sql] java.sql.SQLException: ORA-00900: invalid SQL statement

I have already reviewed this question. However, I still couldn't modify the script to work with Ant.

Following is the ant target

<target name="create-db-schema" >
        <echo message="############################################################"/>
        <echo message="#               Create Complete DB Schema                  #"/>
        <echo message="############################################################"/>

        <sql onerror="continue" classpathref="project.class.path" driver="${database.driverClassName}"
             url="${database.url}" userid="${database.username}" password="${database.password}">
            <path>
                <fileset dir="${test.dbscripts.dir}/schema/">
                    <include name="*.sql"/>
                </fileset>
            </path>
        </sql>
</target>

Any difference if you put a semi-colon and/or a `/` at the conclusion of the `ALTER TRIGGER` statement? How about if you remove the `ALTER TRIGGER` statement and put it through as a separate statement in a new call? — Peter M., Jun 22 '16 at 15:11
But how did you defined your ant task? Did you set up `delimiter`, `delimitertype` row? [check this](https://technology.amis.nl/2005/08/08/ant-and-stored-procedures-and-dbms_output/) — Arkadiusz Łukasiewicz, Jun 22 '16 at 15:12
@ArkadiuszŁukasiewicz, would you mind showing the relevant build script? Did you try `-debug` option? — Rao, Jun 23 '16 at 14:41
Looks you are executing pl/sql and sql together and [this](http://stackoverflow.com/questions/3363356/ant-sql-task-how-to-run-sql-and-pl-sql-and-notice-execution-failure) might help — Rao, Jun 23 '16 at 14:48
@Rao I've tried it with adding the delimiter to but still gives the same error :( — Asiri Liyana Arachchi, Jun 24 '16 at 05:42
Hmm..you want to run in two separate scripts? And if that helps to proceed? — Rao, Jun 24 '16 at 05:43
That trigger is a part of DDL for table USER_ROADS. I want to run both PL/SQL & SQL in same script. Is it what you are asking? — Asiri Liyana Arachchi, Jun 24 '16 at 05:47

score 0 · Accepted Answer · answered Jun 24 '16 at 09:14

With ant sql tag I couldn't run both SQL and PL/SQL in the same script even though I tried in many ways.

But using an external library I could do this.I've used this library to do that dbmaintain Add following to your build script.

<path id="dbmaintain-lib"><fileset dir="${dbmaintain.home}/lib"><include name="*.jar"/></fileset></path>
<taskdef resource="dbmaintain-anttasks.xml" classpathref="dbmaintain.lib"/>

    <target name="update-db">
    <updateDatabase scriptLocations="scripts" autoCreateDbMaintainScriptsTable="true">
        <database driverClassName="oracle.jdbc.driver.OracleDriver" userName="user" password="pass" url="jdbc:oracle:thin:@//localhost:1521/XE" schemaNames="SCHEMA"/>
    </updateDatabase>
</target>

And everthing works perfectly now without changes to sql scripts files to replace ';'. Hope this'll help for somebody in need.

score 0 · Answer 2 · answered Jun 24 '16 at 10:08

I don't have environment to test this solution. But for testing purpose you can try something like this.

<sql
    driver="xxx"
    url="xxx"
    userid="xxx"
    password="xxx"
    delimiter="/"
    delimitertype="row"
    ><![CDATA[

CREATE OR REPLACE TRIGGER "CMDC"."USER_ROADS_UC"
BEFORE INSERT OR UPDATE OF
    ASSOCIATED_PARENT_ROAD
ON USER_ROADS
REFERENCES NEW AS NEW
FOR EACH ROW BEGIN
    :new.ASSOCIATED_PARENT_ROAD:=upper(:new.ASSOCIATED_PARENT_ROAD);
    EXCEPTION
    WHEN OTHERS THEN RETURN;
END;
/
ALTER TRIGGER "CMDC"."USER_ROADS_UC" ENABLE
/

]]></sql>

Important ! Your delimiter now is "/" in new line without any surrounding whitespaces.

Already did this but i kept the sql part in a separate file. It didn't work — Asiri Liyana Arachchi, Jun 24 '16 at 14:55

Error executing SQL with Apache Ant, but not Oracle SQL Developer

2 Answers2