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.
>
>Here are some blocks from the code below:
>
>[ Block Deleted ]
>
> <<find_applicant>>
> 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;
>
> IF myNASRISID IS NULL THEN
>
> <<create_applicant>>
> [ Block Deleted ]
> commit;
> -- Commit our insert so the
> -- applicant will be found
> -- if another submission is
> -- for this applicant.
> <<insert_master_license>>
> [ Block Deleted ]
> ELSE
> <<insert_license>>
> [ Block Deleted ]
>
>
>-- [ Define some exception handlers to handler any errors. ]
>EXCEPTION
> WHEN NO_DATA_FOUND
> THEN myNASRISID := NULL;
> GOTO create_applicant; < -------- Can't use GOTO label in
> -- Exception handler to return
> -- to pl/sql block.
>
>HOW SHOULD I HANDLE THE ABOVE EXCEPTION??
>
>NOTE: I am only using one cursor because I have to process a commit in the
>event I create a new master record. Otherwise, I would end up with
>duplicate records in the event multiple submissions where in the same file
>for the same applicant.
>--
>Neil Greene
>benchMark Developments, Inc. [NeXT VAR]
>2040 Regency Road, Suite C Lexington, KY 40503
>Phone: 606-231-6599 / Fax: 606-254-4864

You could have a separate block for the select statement alone and in the exception handler insert the necessary records. It would look something like this.

  Begin

        select

		SELECT


>			nasrisid	-- Primary unique key


>		INTO

>			myNASRISID	-- May raise NO_DATA_FOUND error.

>		FROM

>			nasrisid	-- Primary unique key


                etc.

Exception
     When No_data_found
          then
           <your create applicant stuff>

End ;
           Now proceed with the rest of your code. 

Hope this helps

Ramesh Krishnamurthy Received on Fri Jul 22 1994 - 22:23:19 CEST