Re: Update NOTFOUND from PL/SQL

From: Steve Cosner <stevec_at_zimmer.CSUFresno.EDU>
Date: 1995/10/06
Message-ID: <DG1rHM.GIJ_at_CSUFresno.EDU>#1/1

I almost always put a return field as the last parameter passed to a stored procedure. If anything goes wrong in the procedure, I pass back a message indicating so. If the procedure runs as expected, the return field is set to null.

My procedures look like this:
PROCEDURE Procedure_Name (P1 in varchar2,RTRN_MSG in out varchar2) is BEGIN
  RTRN_MSG:=NULL;
  Do_Something;
  EXCEPTION WHEN OTHERS THEN --You could add a NOT_FOUND check here     RTRN_MSG:=SUBSTR('ProcedureName: '||SQLERRM,1,80); END ProcedureName;

It is then up to the calling process to check Rtrn_Msg, and raise an application error if necessary.

Steve Cosner

---

In article <452416$gt2_at_zippy.cais.net> satish_at_cais.cais.com (Satish Rajan) writes:
>A newbie question...
>
>I've written a PL/SQL storedproc to update a single row
>in a table. When I invoke it from a ProC program, it
>never tells me that the record I'm looking for is
>NOTFOUND. So I had to put in an SQL%NOTFOUND in the
>stored procedure and raise_application_error() to
>detect the notfound condition.
>
>Is there a better way of detecting notfound conditions
>in this situation?
>Satish Rajan satish_at_cais.com
Received on Fri Oct 06 1995 - 00:00:00 CET