[Help]: PL/SQL Block "Handling NO_DATA_FOUND exception"
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-4864Received on Mon Jul 18 1994 - 16:52:44 CEST
