如何打印导致ORA-01843错误的记录？

Question

我有一张有上百万张唱片的桌子。我正在尝试将一列数据格式化为DATE格式，该格式目前是VARCHAR2格式。但是，我得到的ORA-01843不是一个有效的月份错误。

我正在努力弄到那些导致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错误的记录？

Answer 1

我刚才评论了你之前的问题：How to format only those records for which ORA-01843 is not thrown?，但你没有注意到它。

创建一个函数来检查它是否是这样的有效日期。

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;

然后，要选择失败的记录，可以运行一个查询。

select MyColumn FROM MyTable where validmmddyyyy(MyColumn) = 0

当您有幸使用oracle12c R2时，可以使用TO_DATE的DEFAULT..ON..CONVERSION ERROR子句。

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

一个重要的建议是，不要对数据库中的VARCHAR2 / CHAR使用DATE类型。