Mystic Slayer's Bloggie

Because of some problems with Oracle datetime fields in a Oracle 9.2.0.6 db  at a customer, I had to look for a solution for the ORA-1801: Dateformat too long for internal buffer. The problem isn't that easy to find, why? Well the customer doesn't have this error everyday. So I had to find for a possible solution for this issue.

I've talked to 5 of the best people I know and gave me different solutions. The error should be a bug in Oracle, that's for sure. But how to find the error in your database. Because of migrations the database can be corrupted on datetime fields. In SQL Server this isn't an issue, but Oracle has alot of different time formats I've heard. Based on the knowledge of Oracle developers and administrators I had to search for invalid values in datetime fields in every table.

Below you'll find the script I have made with some help from my uncle.  Yet the only thing you have to change is the spoolfile dir.

 
 
ALTER session SET NLS_DATE_FORMAT = "DD-MON-YYYY";
 
SET serverout ON size 1000000
SET trimspool ON
--
 
spool <a href="file://\\sharename\dir\x.txt">\\sharename\dir\x.txt</a>
 
CREATE TABLE xxx_tmptable_xxx
    AS SELECT table_name, column_name
         FROM user_tab_columns col, tab
        WHERE col.table_name = tab.tname
          AND tab.tabtype   = 'TABLE'
          AND col.data_type = 'DATE'
AND ROWNUM &lt; 10
/
DECLARE
  rwid1    ROWID;
  tabNm    VARCHAR2(50);
  colNm    VARCHAR2(50);
  --
  TYPE     crsSelecTyp IS REF CURSOR;
  crsSelec crsSelecTyp;
  crsBody  LONG;
  --
  rwid2    ROWID;
  dt       DATE;
  dumpdt   VARCHAR2(50);
  dtce     VARCHAR2(50);
  dtyy     VARCHAR2(50);
  dtmm     VARCHAR2(50);
  dtdd     VARCHAR2(50);
  dthh     VARCHAR2(50);
  dtmi     VARCHAR2(50);
  dtss     VARCHAR2(50);
  --
BEGIN
  LOOP
    BEGIN
      SELECT ROWID, table_name, column_name
        INTO rwid1, tabNm, colNm
        FROM xxx_tmptable_xxx
       WHERE ROWNUM = 1;
    EXCEPTION
   WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.put_line('DONE');
        EXIT;
    END;
    --
    crsBody := 'select *'
             ||'  from (select rwid rwid2'
             ||'             , dt'
             ||'             , dumpdt'
             ||'             , substr(dumpdt, itm1+1, itm2-itm1-1) dtce'
             ||'             , substr(dumpdt, itm2+1, itm3-itm2-1) dtyy'
             ||'             , substr(dumpdt, itm3+1, itm4-itm3-1) dtmm'
             ||'             , substr(dumpdt, itm4+1, itm5-itm4-1) dtdd'
             ||'             , substr(dumpdt, itm5+1, itm6-itm5-1) dthh'
             ||'             , substr(dumpdt, itm6+1, itm7-itm6-1) dtmi'
             ||'             , substr(dumpdt, itm7+1)              dtss'
             ||'          from (select rowid rwid'
             ||'                     , '||colNm||' dt'
             ||'                     , dump('||colNm||') dumpdt'
             ||'                     , instr(dump('||colNm||'),'' '',1,2) itm1'
             ||'                     , instr(dump('||colNm||'),'','',1,1) itm2'
             ||'                     , instr(dump('||colNm||'),'','',1,2) itm3'
             ||'                     , instr(dump('||colNm||'),'','',1,3) itm4'
             ||'                     , instr(dump('||colNm||'),'','',1,4) itm5'
             ||'                     , instr(dump('||colNm||'),'','',1,5) itm6'
             ||'                     , instr(dump('||colNm||'),'','',1,6) itm7'
             ||'                  from '||tabNm
             ||'                 where '||colNm||' is not null'
             ||'               )'
             ||'       )'
             ||' where dtce not between ''110'' and ''199'''
             ||'   and dtyy not between ''100'' and ''199'''
             ||'   and dtmm not between ''1''   and ''12'''
             ||'   and dtdd not between ''1''   and ''31'''
             ||'   and dthh not between ''1''   and ''24'''
             ||'   and dtmi not between ''1''   and ''60'''
             ||'   and dtss not between ''1''   and ''60''';
    --
    OPEN crsSelec FOR crsBody;
    LOOP
      FETCH crsSelec INTO rwid2, dt, dumpdt, dtce, dtyy, dtmm, dtdd, dthh, dtmi, dtss;
      EXIT WHEN crsSelec%notfound;
      IF crsSelec%rowcount = 1 THEN
        DBMS_OUTPUT.put_line('entity: '||tabNm||' / element: '||colNm);
        DBMS_OUTPUT.put_line('rowid              date        dump(date)                                         dtce dtyy dtmm dtdd dthh dtmi dtss');
        DBMS_OUTPUT.put_line('------------------ ----------- -------------------------------------------------- ---- ---- ---- ---- ---- ---- ----');
      END IF;
      DBMS_OUTPUT.put_line
      ( rwid2
        ||' '||rpad(dt,11)
        ||' '||rpad(dumpdt,50)
        ||' '||lpad(dtce,4)
        ||' '||lpad(dtyy,4)
        ||' '||lpad(dtmm,4)
        ||' '||lpad(dtdd,4)
        ||' '||lpad(dthh,4)
        ||' '||lpad(dtmi,4)
        ||' '||lpad(dtss,4)
      );
    END LOOP;
    CLOSE crsSelec;
    --
    DELETE FROM xxx_tmptable_xxx
     WHERE ROWID = rwid1;
    COMMIT;
  END LOOP;
END;
/
DROP TABLE xxx_tmptable_xxx
/
 
--
spool off