Re: Newbie, Oralce stored procedures w/ exceptions

From: Diane <flored_at_mail.conservation.state.mo.us>
Date: 4 Apr 2002 09:32:24 -0800
Message-ID: <52086d5b.0204040932.366eca0d_at_posting.google.com>

flored_at_mail.conservation.state.mo.us (Diane) wrote in message news:<52086d5b.0204031341.436d480a_at_posting.google.com>...
> I have Oracle Version 8i
>
> I have a stored procedure with a cursor. I am looping through the
> cursor and if there are no errors, I want to insert the record into a
> table. If an error occurs on the To_date function, I want to insert
> the cursor in a different table and then delete that same record
> from the original table and continue looping through the cursor.
>
> is it even possible to do some code, have an exception move that record to
  another table and then continue through the loop? Or is there some date
  validation in Oracle. This is going to be a scheduled job to run at night.

> Any help would be greatly appreciated.

Here is my sample code:

           IS

	CURSOR validatedate_cursor IS
	SELECT pospplid, ulname, ufname, umi, posconservno, poslname,
textdob,
        posdatedob, txtftpdob from mdhunted.tblposstudent;


validatedate_rec 	validatedate_cursor%ROWTYPE;

  INVALID_DOB EXCEPTION;
  PRAGMA EXCEPTION_INIT(INVALID_DOB,-200001); BEGIN
--This puts an 8 digit text dob formatted to use the TO_DATE function next.
 UPDATE MDHUNTED.tblPOSStudent SET TextDOB

• SUBSTR(TXTFTPDOB,1,2) || '/' || SUBSTR(TXTFTPDOB,3,2) || '/' || SUBSTR(TXTFTPDOB,5,4);

 COMMIT; SAVEPOINT A;
    BEGIN
--loop through the cursor and update the date datatype field

    FOR validatedate_rec IN validatedate_cursor     LOOP

        UPDATE MDHUNTED.TBLPOSSTUDENT SET POSdateDOB = 
          TO_DATE(TEXTDOB,'MM/DD/YYYY');

    END LOOP;
--I have an invalid date so I now want to move that record to an exception table
--and then I want to delete that record and continue processing through my loop.

    EXCEPTION
      WHEN INVALID_DOB THEN               INSERT INTO mdhunted.tblduplicatesnotftpd (pospplid, ulname,ufname, umi, posconservno,posdatedob, dupReason) VALUES

validatedate_rec.pospplid,validatedate_rec.ulname,
validatedate_rec.ufname,
validatedate_rec.umi, validatedate_rec.posconservno,
validatedate_rec.poslname,validatedate_rec.posfname,         
validatedate_rec.posmi,validatedate_rec.posdatedob,'Invalid Date of

Birth');

DELETE FROM mdhunted.tblposstudent where pospplid = validatedate_rec.pospplid;       

   ROLLBACK TO A;
   END;     END PREPDOBFORMAT; Received on Thu Apr 04 2002 - 19:32:24 CEST