Results 1 to 2 of 2

Thread: ORA-01839 Error

  1. #1
    Join Date
    Jun 2002
    Posts
    1

    Unanswered: ORA-01839 Error

    Trying to insert thousands of records into db. There's a date field that's giving me problems...

    In converting a varchar string to a date, I do the checks below... but I'm still getting a "date not valid for month specified..." error some unknown records... Is my Day validation off? What other checks should I do on the p_dateString parameter to verify the date format. Is there an easy way to find out which records are giving me problems?

    SAMPLE:

    --check char length
    IF LENGTH(p_dateString) < 6 OR LENGTH(p_dateString) > 6 THEN
    RETURN NULL;
    END IF;

    -- Chop the date up into components
    v_dateMM := SUBSTR(p_dateString,1,2);
    v_dateDD := SUBSTR(p_dateString,3,2);
    v_dateYY := SUBSTR(p_dateString,5,2);

    -- Validate the year
    IF NOT(v_dateYY BETWEEN '00' and '99') THEN
    RETURN NULL;
    END IF;

    -- Validate the Month
    IF NOT(v_dateMM BETWEEN '01' and '12') THEN
    RETURN NULL;
    END IF;

    -- Check the Day (Does not account for leap year errors)
    IF v_dateMM IN ('01','03','05','07','08','10','12') THEN
    IF NOT(v_dateDD BETWEEN '01' AND '31') THEN
    RETURN NULL;
    END IF;
    ELSIF v_dateMM IN ('04','06','09','11') THEN
    IF NOT (v_dateDD BETWEEN '01' and '30') THEN
    RETURN NULL;
    END IF;
    ELSIF v_dateMM = '02' THEN
    IF NOT (v_dateDD BETWEEN '01' AND '29') THEN
    RETURN NULL;
    END IF;
    ELSE
    RETURN NULL;
    END IF;

    -- No errors, so the date must be good. Convert and return it
    v_dateResult := to_date(p_dateString,'MMDDRR');

    RETURN v_dateResult;

    END SAMPLE:

    Any help would be appreciated...

  2. #2
    Join Date
    Jun 2002
    Location
    Indianapolis
    Posts
    21
    Well, it looks like this is a stand alone function. If that is the case, then your options for tracking down the actual record(s) that are giving you problems are limited since you only know about the data passed into the code. You could alter the function to pass in the primary key of the record, or some unique identifier along with the string, and in your exception block, log the id of the record when it throws an error.

    If you need more ideas, providing more info on the loading would go a long way.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •