Re: [Help]: PL/SQL Block "Handling NO_DATA_FOUND exception"

In article <1994Jul18.145244.26558_at_KYnug.org> Neil Greene <neil_at_kynug.org> writes:

> I have a pl/sql cursor that fetches records from a table. It then has a
> select statement that tries to find the submitted applicant in our current
> database. Of course, this select statement can FAIL with a
> NO_RECORDS_FOUND exception. I have an exception handler setup for this,
> but I would simply like the block to create the applicants master file and
> add the submitted data. But, I cannot figure out how to get back to my
> origional pl/sql block that called the exception since you CANNOT use a
> goto label from the exception handler back to the calling block.
>

A simple solution is to surround your select in its own block, and place the exception handler within this block.

> Here are some blocks from the code below:

                   <<find_applicant>>
                begin -- anonymous block
                   SELECT
                           nasrisid	-- Primary unique key
                   INTO
                           myNASRISID	-- May raise NO_DATA_FOUND error.
                   FROM
                           master source
                   WHERE
                           source.ss = c1_rec.ss AND
                           source.lname = c1_rec.lname AND
                           source.birthdate = c1_rec.birthdate;

                   EXCEPTION
                     WHEN NO_DATA_FOUND 
                     THEN myNASRISID := NULL;
                end -- anonymous block

                           IF myNASRISID IS NULL THEN

                                -- rest of your code

> --
> Neil Greene
> benchMark Developments, Inc. [NeXT VAR]
> 2040 Regency Road, Suite C Lexington, KY 40503
> Phone: 606-231-6599 / Fax: 606-254-4864

--
Oddmund.Johannessen_at_delab.sintef.no