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

From: Neil Greene <neil_at_kynug.org>
Date: Mon, 18 Jul 1994 14:52:44 GMT
Message-ID: <1994Jul18.145244.26558_at_KYnug.org>


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
Received on Mon Jul 18 1994 - 16:52:44 CEST

Original text of this message