unix + oracle sql code

Posted on 2014-08-04
Last Modified: 2014-08-29
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.
Question by:digs developer
    LVL 37

    Accepted Solution

    >> 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 looks like:

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

    $ ./

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

    Exited with 2
    LVL 34

    Expert Comment

    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.
    LVL 40

    Assisted Solution

    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"
    LVL 34

    Assisted Solution

    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.

    Author Comment

    by:digs developer

    Author Comment

    by:digs developer
    I've requested that this question be closed as follows:

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

    for the following reason:

    LVL 37

    Expert Comment

    by:Gerwin Jansen
    I believe this question is closed incorrectly, 0 points for the askers own comment?
    LVL 34

    Expert Comment

    I would say an even split between these 3 comments:

    http:#a40240022 - 149 points
    http:#a40240419 - 148 points
    http:#a40240895 - 148 points
    LVL 37

    Expert Comment

    by:Gerwin Jansen
    @johnsone - I agree

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    SSH (Secure Shell) - Tips and Tricks As you all know SSH(Secure Shell) is a network protocol, which we use to access/transfer files securely between two networked devices. SSH was actually designed as a replacement for insecure protocols that sen…
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    This video shows how to recover a database from a user managed backup
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now