plsql error handling

From: porschberg <thomas.porschberg_at_osp-dd.de>
Date: 3 Nov 2003 01:06:28 -0800
Message-ID: <8d9566f5.0311030106.8004c4@posting.google.com>

I have some questions how to deal reasonable and in a practical way with errors/exceptions from PLSQL-procedures. I have several PL-SQL procedures which implement some business logic. The procedures are called one after another inside a shellscript via sqlplus.
The problem is that every of this procedures can fail and that I have to report errors to the calling program (in my case the shellscript).

A simle approach could be:

ERRORS=`sqlplus -s sysp/dresden_at_SYSP_DD <<EOF | grep ORA

execute importmigrationdata.importlogos;
execute importmigrationdata.importlvdate;
execute importmigrationdata.importmovingdata;

EOF` if (( $? == 0 ))
then

    echo "EXIT-Code =$ERRORS"
    exit 2
fi

In the example above I don't know which procedure raised the error.

I could call split the procdure calls:

ERRORS=`sqlplus -s sysp/dresden_at_SYSP_DD <<EOF | grep ORA execute importmigrationdata.importlogos; EOF` if (( $? == 0 ))
then

    echo "EXIT-Code =$ERRORS"
    exit 2
fi

ERRORS=`sqlplus -s sysp/dresden_at_SYSP_DD <<EOF | grep ORA execute importmigrationdata.importlvdate; EOF` if (( $? == 0 ))
then

    echo "EXIT-Code =$ERRORS"
    exit 2
fi

but thats neither smart nor efficient.

Another idea is to write inside my PLSQL-package with the UTL_FILE package into the filesystem and the shellscript could read from this file.

I have also some experience with the perl DBI/DBD interface so its not a problem to embed the procedure calls into this perl environment.

However what I'm searching for is a reliable way to handle this errors which also is sufficient for a large number of procedures.

Any suggestion is welcome. Received on Mon Nov 03 2003 - 03:06:28 CST