Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2008
    Posts
    8

    Unanswered: SQL Script to check status of last command

    Hi All,

    I need to make a SQL script and call it from Unix (bash) shell.


    Code:
    sqlplus vikas/vikas <<END
    spool /oracle/vikas/output.txt
    command 1
    command 2
    ...
    ....
    comman N
    spool off;
    commit;
    END

    This runs perfectly, but my problem is that if a command fails, the other commands keeps on running. However, I need to keep a check that when a command fails the script is exited.

    In unix we have an option of echo $? to check the status of previous command, But how to accomplish this SQL.

    Pls help !!
    __________________
    ==> VIKAS <==

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Whenever...

    Try this:
    Code:
    sqlplus vikas/vikas <<END
    WHENEVER SQLERROR EXIT 13;
    spool /oracle/vikas/output.txt
    command 1
    command 2
    ...
    ....
    comman N
    spool off;
    commit;
    EXIT 0;
    END
    if [ $? -ne 0 ]; then
      echo "ERROR! SQL*Plus failed..."
      exit 1
    fi
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2008
    Posts
    8

    Thumbs up Solved

    Hi,

    Sorry for reply late. The lines in bold helped me. Here is my final code.

    Code:
    sqlplus vikas/vikas << END
    WHENEVER SQLERROR EXIT;			    
    WHENEVER OSERROR  EXIT;			    
    spool /oracle/vikas/output.txt
    desc WHITELIST;
    desc USERS;
    desc TRANSACTION;
    desc computer;
    spool off;
    !echo ALL SUCCESSFUL
    END
    if [ $? -ne 0 ]; 
    then
      echo "ERROR! SQL*Plus failed..."; exit 1
    else
      echo "\n\nOne or more query has failed if --- ALL SUCCESSFUL -- is not echoed !!!"
    fi
    Here,
    • WHENEVER SQLERROR EXIT -- Exits if any error is encounter in query
    • WHENEVER OSERROR EXIT -- Exits if any error encountered in OS
    • desc computer; -- Wrong Line

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Exit with style

    You may want to FORCE a numeric error upon exit:
    Code:
    sqlplus vikas/vikas <<END
    WHENEVER SQLERROR EXIT 41;
    WHENEVER OSERROR  EXIT 99;
    spool /oracle/vikas/output.txt
    select SYSDATE from dual;
    exit 0
    END
    case $? in
       0) echo "\n\nALL SUCCESSFUL!";;
      41) echo "ERROR! SQL*Plus failed..."; exit 1;;
      99) echo "ERROR! OS failure..."; exit 2;;
       *) echo "Warning! Unexplained exit code: $?;; 
    fi
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Sep 2008
    Posts
    8
    Quote Originally Posted by LKBrwn_DBA
    You may want to FORCE a numeric error upon exit:
    Code:
    sqlplus vikas/vikas <<END
    WHENEVER SQLERROR EXIT 41;
    WHENEVER OSERROR  EXIT 99;
    spool /oracle/vikas/output.txt
    select SYSDATE from dual;
    exit 0
    END
    case $? in
       0) echo "\n\nALL SUCCESSFUL!";;
      41) echo "ERROR! SQL*Plus failed..."; exit 1;;
      99) echo "ERROR! OS failure..."; exit 2;;
       *) echo "Warning! Unexplained exit code: $?;; 
    fi
    Thanks a lot my friend. Just one question (might be stupid).
    Are these error codes (41,99) system error codes OR we can use any number to identify error.

    I am asking, because in Unix we have a fixed error code 0 when a command is executed successfully else it is 1.

    Thanks a ton again for you help. Will include it in my SQL scripts. I am pretty okay with shell scripting But still learning SQL commands and use them in SQL scripts.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    No way Jose...

    Quote Originally Posted by vikas027
    Thanks a lot my friend. Just one question (might be stupid).
    Are these error codes (41,99) system error codes OR we can use any number to identify error.

    I am asking, because in Unix we have a fixed error code 0 when a command is executed successfully else it is 1.

    Thanks a ton again for you help. Will include it in my SQL scripts. I am pretty okay with shell scripting But still learning SQL commands and use them in SQL scripts.
    No, these error numbers are arbitrary (made up) you can use any number between 0 and 255.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •