unix + oracle sql code

In shell script I have one oracle sql block . Suppose the sql block gives ant oracle exception.Then we stop the process.

I know in oracle how to stop it. e.g. I have used Exit keyword and come out from sql block.

But in unix how can we stop the process if sql error occurs.
How will user know the error e.g. no_data_found

Please Give me the examples of unix shell script and in that they call oracle sql code.
digs developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gerwin Jansen, EE MVETopic Advisor Commented:
>> In shell script I have one oracle sql block
Please post (part) of your script with the sql block

If you use sqlplus in your unix shell script then you can use the exit code from sqlplus to check for in your unix shell script.

For example, your somesql.sh looks like:

sqlplus -s scott/tiger <<EOFSQL
  whenever sqlerror exit 2;
  select 1 from not_existing_table;
echo Exited with $?

$ ./somesql.sh

  select 1 from not_existing_table
ERROR at line 1:
ORA-00942: table or view does not exist

Exited with 2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
I prefer to use the following:

whenever sqlerror exit sql.sqlcode;

That way the return code is the error code that caused the exit.

I'm not sure that a NO_DATA_FOUND would trigger this.  I believe that is considered a warning and not an error.  If you trap it and raise it as a user defined error, it should pick it up.
In unix, processes will exit with status 0 if successful. The exit status of a process or program can be checked by shell env variable $?. if $? is not 0 then there would be an error (this is set by the program / process that was running, and you need to check what sqlplus would return as exit status and what it means).

an example shell code to check exit code of a command / process:

if [ $? -eq 0 ]
      echo "last command exit status is successful"
     echo "last command exit status needs to be checked"
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

johnsoneSenior Oracle DBACommented:
SQL*Plus will always return 0 unless otherwise directed by the user.  I believe the only time you would get a non-0 exit status is if the program itself encounters an error, but personally I don't think I have ever seen that.

According to the documentation I found the default behavior of 2 the exit strategies (OS error or SQL error) is to return a success code.

The WHENEVER clause is the key, without that processing wouldn't stop on an error and SQL*Plus wouldn't exit.
digs developerAuthor Commented:
digs developerAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for digvijay yadav's comment #a40267727

for the following reason:

Gerwin Jansen, EE MVETopic Advisor Commented:
I believe this question is closed incorrectly, 0 points for the askers own comment?
johnsoneSenior Oracle DBACommented:
I would say an even split between these 3 comments:

http:#a40240022 - 149 points
http:#a40240419 - 148 points
http:#a40240895 - 148 points
Gerwin Jansen, EE MVETopic Advisor Commented:
@johnsone - I agree
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Unix OS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.