如何打印导致ORA-01843错误的记录? [英] How to print record that caused ORA-01843 error?

问题描述

我有一个包含数百万条记录的表.我正在尝试将一列数据格式化为DATE格式 当前在VARCHAR2中.但是，我收到ORA-01843无效的月份错误.

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.

我正在尝试获取导致ORA-01843的记录

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;

但是，不会打印v_company的值.

如何获取导致ORA-01843错误的记录?

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

推荐答案

我刚刚评论了您之前的问题:

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

当您有幸使用Oracle 12c R2时，可以使用TO_DATE

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

一如既往的重要建议，请勿对数据库中的DATE使用VARCHAR2 / CHAR类型.

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

这篇关于如何打印导致ORA-01843错误的记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！