options:
- in the stored procedures use either RAISE_APPLICATION_ERROR of
DBMS_OUTPUT to generate custom error messages
- in your calling script, use an anonymous block with an exception handler
for each procedure call, then user DBMS_OUTPUT to display the error message
with additional information
"porschberg" <thomas.porschberg_at_osp-dd.de> wrote in message
news:8d9566f5.0311030106.8004c4_at_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 - 05:39:40 CST
