Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: plsql error handling

Re: plsql error handling

From: mcstock <mcstockx_at_xenquery.com>
Date: Mon, 3 Nov 2003 06:39:40 -0500
Message-ID: <ms2dnW0u3s07ozuiRVn-iQ@comcast.com>


options:

  1. in the stored procedures use either RAISE_APPLICATION_ERROR of DBMS_OUTPUT to generate custom error messages
  2. 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
    • mcs

"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US