# Exit when sql script give error

**URL:** <https://community.unix.com/t/exit-when-sql-script-give-error/357781>\
**Category:** UNIX for Advanced & Expert Users\
**Created:** [January 7, 2016, 3:13pm UTC](https://community.unix.com/t/exit-when-sql-script-give-error/357781 "2016-01-07T15:13:52Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![rohit\_shinez](https://community.unix.com/letter_avatar/rohit_shinez/32/6_22734026db12803ebb3e059622ac3534.png) [@rohit\_shinez](https://community.unix.com/u/rohit_shinez)\
**Post date:** [January 7, 2016, 3:13pm UTC](https://community.unix.com/t/exit-when-sql-script-give-error/357781/1 "2016-01-07T15:13:52Z")

</div>

Hi Guys,

I am calling a SQL script which runs under while loop, i need to exit the while loop if i get error in sql script which is called

```nohighlight
while [i -le 10] 
do
sqlplus -s user/pass@db @test.sql id$i
done

test.sql
whenever sqlerror exit;
alter table t1 add &1 number;

```

I need to come out of while loop if i find error in any of the calling test.sql script with error message stating test.sql failed for 1st loop hence exiting loop

---

<div class="post-metadata">

**Author:** ![jim\_mcnamara](https://community.unix.com/letter_avatar/jim_mcnamara/32/6_22734026db12803ebb3e059622ac3534.png) [@jim\_mcnamara](https://community.unix.com/u/jim_mcnamara)\
**Post date:** [January 7, 2016, 4:40pm UTC](https://community.unix.com/t/exit-when-sql-script-give-error/357781/2 "2016-01-07T16:40:47Z")

</div>

With some corrections - mind your spaces around [[ and [ :  
try:

```nohighlight
status=0
while [[$i -le 10 && $status -eq 0]] 
do
sqlplus -s user/pass@db <<-EOF
@test.sql id${i}
EOF
status=$?
if [$status -ne 0] ; then
   echo "script failed on $i"
fi
done

```

test.sql code:

```nohighlight
# test.sql
whenever sqlerror exit failure;
alter table t1 add &1 number;
exit 0

```

---

<div class="post-metadata">

**Author:** ![rohit\_shinez](https://community.unix.com/letter_avatar/rohit_shinez/32/6_22734026db12803ebb3e059622ac3534.png) [@rohit\_shinez](https://community.unix.com/u/rohit_shinez)\
**Post date:** [January 8, 2016, 10:57am UTC](https://community.unix.com/t/exit-when-sql-script-give-error/357781/3 "2016-01-08T10:57:26Z")

</div>

Thanks,

is there a way exit the loop with error message if there is any DB connection issue for eg

```nohighlight
sqlplus -s db/user@db

ORA-12154: TNS:could not resolve the connect identifier specified

```