How to print record that caused ORA-01843 error?

Question

I have a table with millions of records. I am trying to format one column data to DATE format which is currently in VARCHAR2. But, I am getting ORA-01843 not a valid month error.

I am trying to get those records which are causing ORA-01843

CREATE OR REPLACE PROCEDURE pr_TEST_CUSTOM_ORA1843 AS

v_company MyTable.MyColumn%TYPE;

BEGIN
          BEGIN
              SELECT to_char(to_date(TRIM(MyColumn), 'YYMMDD'), 'MM/DD/YYYY') 
              INTO v_company FROM MyTable;    
          EXCEPTION
              WHEN OTHERS THEN
                 DBMS_OUTPUT.PUT_LINE('ORA-01843 caused by'||v_company);
          END;


END  pr_TEST_CUSTOM_ORA1843;

But, the value of v_company is not printed.

How to get the records which are causing ORA-01843 error?

One method -- which is rather cumbersome -- is to write a function that converts the value one at a time. Have it return `NULL` when there isn't a match and then look for `NULL` values in the output. — Gordon Linoff, Apr 11 '18 at 12:57
Seeing as your SELECT statement to populate v_company has just failed why would you expect v_company to contain anything? Maybe try outputting MyColumn instead? — OTTA, Apr 11 '18 at 13:00
Why do you need TRIM? Are there extra spaces in the values in that column? Then - what is the format of the strings in the column? Is it YYMMDD? — , Apr 11 '18 at 13:31

score 4 · Accepted Answer · answered Apr 11 '18 at 13:30

I just commented on your previous question : How to format only those records for which ORA-01843 is not thrown? but you did not pay attention to it.

Create a function which checks if it is a valid date like this.

CREATE OR REPLACE FUNCTION validmmddyyyy (p_str IN VARCHAR2)
   RETURN NUMBER
AS
   V_date   DATE;
BEGIN
   V_Date := TO_DATE (p_str, 'MM/DD/YYYY');
   RETURN 1;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN 0;
END;

Then, to select the records which fail, you can run a query

select MyColumn FROM MyTable where validmmddyyyy(MyColumn) = 0

When you are lucky enough to use Oracle 12c R2, you could make use of DEFAULT..ON..CONVERSION ERROR clause of TO_DATE

SELECT *
  FROM MyTable
 WHERE TO_DATE (MyColumn default null on conversion error,'MM/DD/YYYY') IS NULL

An important advice as always, don't use a VARCHAR2 / CHAR type for DATEs in database.

How to print record that caused ORA-01843 error?

1 Answers1

Linked