CODEWITHSUNDEEP
sqloracle-databaseant
Tarun
Tarun

Reputation: 723

How to fail ANT build if any SQL fails inside loop

I have to run all the SQL files present in a directory. So, using for loop , I'm able to achieve this but now build is getting success even if any of the SQL gets failed.

How to fail the ANT build if any sql failed executed in loop?

build.xml

<?xml version="1.0"?>
<project name="run_build" default="full_build">

        <target name="init" description="Initialize the Ant variables">
                <taskdef resource="net/sf/antcontrib/antlib.xml"/>
        </target>

        <target name="run_sqls" depends="init" description="Run SQLs">
                <for param="sqlfile">
                        <path>
                                <fileset dir="." includes="*.sql"/>
                        </path>
                        <sequential>
                                <property name="sqlfilename" value="@{sqlfile}"/>
                                <echo message = "Running SQL file : ${sqlfilename} "/>
                                <exec executable="${ORACLE_HOME}/bin/sqlplus" failonerror="true">
                                  <arg value="dbuser/dbpass@dbinstance"/>
                                  <arg value="@${sqlfilename}"/>
                                </exec>
                        </sequential>
                </for>
        </target>

        <target name="full_build" description="Running full build">
          <antcall target="run_sqls"/>
        </target>

</project>

Current Output : Build is success even if sql failed.

Buildfile: build.xml

full_build:

init:

run_sqls:
     [echo] Running Schema SQL file : abc.sql
     [exec]
     [exec] SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 5 14:20:25 2020
     [exec]
     [exec] Copyright (c) 1982, 2011, Oracle.  All rights reserved.
     [exec]
     [exec]
     [exec] Connected to:
     [exec] Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
     [exec] With the Partitioning, OLAP, Data Mining and Real Application Testing options
     [exec]
     [exec] update table_sdj set first_name='FirstName' where id=123
     [exec]        *
     [exec] ERROR at line 1:
     [exec] ORA-00942: table or view does not exist
     [exec]
     [exec]
     [exec]
     [exec] Commit complete.
     [exec]
     [exec] SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
     [exec] With the Partitioning, OLAP, Data Mining and Real Application Testing options

BUILD SUCCESSFUL
Total time: 0 seconds

Upvotes: 0

Views: 112

Answers (1)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

You must add these clauses to your sqlplus scripts in order to control whether there was an sql error or an os error.

whenever sqlerror exit failure;
whenever oserror exit failure;

Then, you must control that return code in your program to verify whether the script executed ok or not. If you don't, sqlplus always exits with 0 no matter what.

Example

$ cat test.sql
set echo on
drop table tx purge;
exit;
$ sqlplus / as sysdba @test.sql

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 5 12:11:31 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> drop table tx purge;
drop table tx purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> exit;
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$ echo $?
0

With the right clauses included

$ cat test.sql
whenever sqlerror exit failure;
whenever oserror exit failure;
set echo on
drop table tx purge;
exit;
$ sqlplus / as sysdba @test.sql

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 5 12:13:26 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> drop table tx purge;
drop table tx purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$ echo $?
1

By default, as shown, sqlplus will always exit with 0. You need to instruct it to exit with error when an error happens.

whenever sqlerror

Upvotes: 1

Related Questions