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

Try using another cursor, with parameters instead of a SELECT...INTO. That way, you can FETCH the first record from the cursor and test for cursor-name%notfound to determine how to proceed. This also helps you handle the case where more that one record is returned in which case a SELECT...INTO raises yet another exception.

Are you using Oracle6 or Oracle7? Oracle7 introduced PL/SQL 2 where you can create a package of callable procedures and functions that pretty much eliminates the need for labeled blocks and gotos.

-- 
________________________________________________________________________

    _/_/_/    _/_/_/  _/_/_/    David C. Richoux      drichoux_at_shell.com
   _/    _/  _/      _/   _/
  _/    _/  _/      _/_/_/      Shell Offshore Inc.
 _/    _/  _/      _/   _/      701 Poydras St. Rm.1634   P.O. Box 61933
_/_/_/    _/_/_/  _/    _/      New Orleans, La.  70161   (504) 588-0620
________________________________________________________________________