CODEWITHSUNDEEP
sqloracle-databaseplsqlerror-handling
user2488578
user2488578

Reputation: 916

How to print record that caused ORA-01843 error?

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?

Upvotes: 3

Views: 269

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

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.

Upvotes: 4

Related Questions