How to return error code in case plsql compilation error from sqlplus

Question

I use "WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;" in my plsql scripts to use them in shell scripts. This works fine:

echo exit | sqlplus user/pass@XE  @normal.sql && echo "boo"

Executes the script and prints "boo" This works fine too:

echo exit | sqlplus user/pass@XE  @bad.sql && echo "boo"

"boo" is not printed.

However in case bad is:

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;

create or replace
PACKAGE TESTING
IS
function boo (co_id number) return varchar2;
END;
/

create or replace
PACKAGE BODY TESTING
is
end;

Which is obviously wrong - no error code is returned and "boo" is printed. How can I return plsqsl compilation error codes from sqlplus scripts?

Sodved · Accepted Answer · 2011-11-10 12:30:51Z

2

You will need to parse them from the output. Unix error codes are in the range 0 to 255, and theres all sorts of masks and signal stuff hidden in there. So you cannot record oracle error numbers in unix error codes.

So basically you need to make your sql scripts include the show errors statement. But you do NOT want the WHENEVER statement in there because that will error before the errors are printed. e.g. bad.sql will be

create or replace
PACKAGE TESTING
IS
function boo (co_id number) return varchar2;
END;
/
show errors

create or replace
PACKAGE BODY TESTING
is
end;
/
show errors

Then your shell script should be something like:

ERR_OUT="$( sqlplus rdbds/rdbds@XE  < bad.sql | egrep '^(ORA|PLS)-' )"
if [ -n "$ERR_OUT" ]
then
    echo "Errors in SQL:"
    echo "$ERR_OUT"
else
    echo boo
fi

answered Nov 10, 2011 at 12:30

Sodved

8,6272 gold badges34 silver badges47 bronze badges

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

2 Comments

Ivan Sopov Over a year ago

Thanks, that is what I was thinking about, but I hoped there is is a better way.

Ivan Sopov Over a year ago

Instead of parsing output of "show errors" it may be better to parse in sql the content of user_errors or all_errors table, as suggested here: forums.oracle.com/forums/thread.jspa?threadID=692710 (there is also another - third solution suggested there)

Collectives™ on Stack Overflow

How to return error code in case plsql compilation error from sqlplus

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related