Re: Newbie, Oralce stored procedures w/ exceptions

flored_at_mail.conservation.state.mo.us (Diane) wrote in message news:<52086d5b.0204040932.366eca0d_at_posting.google.com>...
> flored_at_mail.conservation.state.mo.us (Diane) wrote in message news:<52086d5b.0204031341.436d480a@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;
I would suggest you create a function:

FUNCTION checkdate
(
  v_date IN CHAR
)
  RETURN NUMBER
IS
 v_dummydate DATE;
BEGIN
  v_dummydate := TO_DATE(v_date,'MM/DD/YYYY');   RETURN (0);
EXCEPTION
  WHEN OTHERS THEN
  RETURN (1);
END; And then simply:

insert into tblduplicatesnotftpd
select tblposstudent.*,'Invalid Date of Birth' from tblposstudent
where checkdate(TEXTDOB) = 1;

delete from tblposstudent
where checkdate(TEXTDOB) = 1;

update tblposstudent
set POSdateDOB = TO_DATE(TEXTDOB,'MM/DD/YYYY') where checkdate(TEXTDOB) = 0;

commit;

It would, however, be better to store the DOB in Oracle date format when it is entered and perform (client side or server side) date validation upon data entry. Received on Fri Apr 05 2002 - 15:00:16 CEST