0

See this code:

test.sh

#!/bin/bash
echo "hello!"
klsdslkdsd
echo "bye"

When I run it I get:

hello!
/tmp/test.sh: line 3: klsdslkdsd command not found
bye

Although there is a syntactic error, the execution goes one (is a script after all).

Now, if I do:

testWithStop.sh

#!/bin/bash

set -e

echo "hello!"
klsdslkdsd
echo "bye"

I get:

hello!
/tmp/test.sh: line 5: klsdslkdsd command not found

The execution stops because I get an exit error from every executed line. If exit != 0 it aborts.

I would like to replicate this behavior in a set of (oracle) PL/SQL (and solely SQL) code.

At this point, even with error, the Oracles DB Manager manages the error gracefully and don't stop the execution of the SQLs. I would like that, when an error (syntactic or semantic) is found, the program aborts but without touching the logic of the program (if so, I would have to change several hundreds of PL/SQL and is not possible).

I know I could do it using raiserror or creating a macro anonymous block that encapsulate portions of my codes, so I could retrieve the exceptions. As I said, it could not be possible to do with several hundreds (could be thousands) of isolated (and logic complex) PL/SQLs

Is there an equivalent to set -e in SQL or an elegant way to do it?

EDIT:

In particular, I´m executing (and calling) de PL/SQLs via a shell script (bash).

For example, the sqlbash.sh file:

#!/bin/bash 
sqlplus .....  <<EOF

select * from table;
sdsdsfdsf     <--- intentional error!
select * from table2;
EOF

If I called the script, I will get the syntactic error, but the execution goes on and doesn't abort. I would like to abort, for example, mimicking the behavior of an exit 1.

Something like:

#!/bin/bash 
sqlplus .....  <<EOF

select * from table;
sdsdsfdsf     <--- intentional error!
exit 1        <--- it will  abort and the script WILL FAIL at this point
select * from table2;
EOF

I'm working with an scheduler, so, it is necessary for me to see that the script fails, not that it was executed and gave me warnings.

Improve this question
2
  • Firstly, please don't bold everything. Secondly, have you attempted anything for yourself? Thirdly, you seem to be asking whether there's anything which will stop execution on an exception. This is the default behaviour. If this is not what you want then please can you make your question clearer? Commented Jun 26, 2013 at 19:40
  • Yes, I tried catching exceptions or putting some exit 1 codes strategically and it works. It aborts the execution. But as I said, I can´t look every code and do that. Now, is true that if there an error it stops, but I want it to abort. I will update my question to write exactly the issue. Commented Jun 26, 2013 at 19:45

1 Answer 1

Reset to default
2

Your looking for WHENEVER SQLERROR and/or WHENEVER OSERROR SQL*Pus commands. They allow you to, among other things, exit if something bad happens in the middle of the script.

Put something like

whenever oserror exit 1
whenever sqlerror exit 2

at the top of your scripts, and you'll be able to tell from your shell script that something failed.

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

3 Comments

Thank you. Just what I needed. It works. Although in the case of a syntactic error, like in the example, it does not work. It just gives a message saying the it ignores the line and continues. I think I could live with that. If you have a suggestion, I would be delighted.
Suggestion: check your scripts before running them from your scheduler :-) (That's only half tongue-in-cheek.)
@Kani - you can't trap that kind of error (SP2-*), but you can log them them in 11g. I guess you could have a proc that checks the log table and raises an exception if it isn't empty, but you'd have to call that after every statement, and you could get those calls wrong too. Mat's right about checking your script *8-)