Oracle Error Handling in Bash Script

Question

How can I error handling in SQLPlus , printing out customised messages when an error is encountered. I have put my code below.

My Code

#!/bin/bash

    echo "My Scripts run below"
    sqlplus -S UID1/UID2@DB1<< EOF
    whenever sqlerror exit sql.sqlcode;
    @/path/Script1
    @/path/Script2
    exit;
    EOF
   echo "My Scripts have run"

Output

My Scripts run below
SP2-0310: unable to open file "/path/Script1.sql"
SP2-0310: unable to open file "/path/Script2.sql"
My Scripts have run

Required Output

My Scripts run below
**Below error in Script1**
SP2-0310: unable to open file "/path/Script1.sql"
**Below error in Script2**
SP2-0310: unable to open file "/path/Script2.sql"
My Scripts have run

Emmanuel · Accepted Answer · 2013-06-28 07:29:07Z

1

Indeed, SQLPlus return code is always 0 fr om my experience, so I advise you to do what I had to do in previous projects: redirect scripts output in a file, then parse it to find SPx-xxxx or ORA-xxxx expressions that indicate errors.

answered Jun 28, 2013 at 7:29

Emmanuel

14.2k12 gold badges53 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:52:17Z

1

WHENEVER SQLERROR detects an error in a SQL command or PL/SQL block. You should use WHENEVER OSERROR instead to catch operating system errors.

WHENEVER OSERROR EXIT FAILURE

If in doubt which error code is returned, you could think about hardcoding a number:

WHENEVER OSERROR EXIT 1

For clarity, I'd change the last EXIT to

EXIT SUCCESS

You won't be able to catch both errors for script1 and script2. After the first error, your SQL*Plus scripts exits and hands back control to bash.

About what to do with the exit code back in bash, see Error handling in Bash

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Jun 28, 2013 at 8:48

wolφi

8,3914 gold badges41 silver badges71 bronze badges

Collectives™ on Stack Overflow

Oracle Error Handling in Bash Script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related