André Krijnen

Tag: dateformat too long for internal buffer

ORA 1801 – Dateformat too long for internal buffer

by on May.08, 2009, under oracle

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.

  1.  
  2. ALTER session SET NLS_DATE_FORMAT = "DD-MON-YYYY";
  3.  
  4. SET serverout ON size 1000000
  5. SET trimspool ON
  6.  
  7. spool <a href="file://\\sharename\dir\x.txt">\\sharename\dir\x.txt</a>
  8.  
  9. CREATE TABLE xxx_tmptable_xxx
  10.     AS SELECT table_name, column_name
  11.          FROM user_tab_columns col, tab
  12.         WHERE col.table_name = tab.tname
  13.           AND tab.tabtype   = 'TABLE'
  14.           AND col.data_type = 'DATE'
  15. AND ROWNUM &amp;lt; 10
  16. /
  17. DECLARE
  18.   rwid1    ROWID;
  19.   tabNm    VARCHAR2(50);
  20.   colNm    VARCHAR2(50);
  21.  
  22.   TYPE     crsSelecTyp IS REF CURSOR;
  23.   crsSelec crsSelecTyp;
  24.   crsBody  LONG;
  25.  
  26.   rwid2    ROWID;
  27.   dt       DATE;
  28.   dumpdt   VARCHAR2(50);
  29.   dtce     VARCHAR2(50);
  30.   dtyy     VARCHAR2(50);
  31.   dtmm     VARCHAR2(50);
  32.   dtdd     VARCHAR2(50);
  33.   dthh     VARCHAR2(50);
  34.   dtmi     VARCHAR2(50);
  35.   dtss     VARCHAR2(50);
  36.  
  37. BEGIN
  38.   LOOP
  39.     BEGIN
  40.       SELECT ROWID, table_name, column_name
  41.         INTO rwid1, tabNm, colNm
  42.         FROM xxx_tmptable_xxx
  43.        WHERE ROWNUM = 1;
  44.     EXCEPTION
  45.    WHEN NO_DATA_FOUND THEN
  46.         DBMS_OUTPUT.put_line('DONE');
  47.         EXIT;
  48.     END;
  49.    
  50.     crsBody := 'select *'
  51.              ||'  from (select rwid rwid2'
  52.              ||'             , dt'
  53.              ||'             , dumpdt'
  54.              ||'             , substr(dumpdt, itm1+1, itm2-itm1-1) dtce'
  55.              ||'             , substr(dumpdt, itm2+1, itm3-itm2-1) dtyy'
  56.              ||'             , substr(dumpdt, itm3+1, itm4-itm3-1) dtmm'
  57.              ||'             , substr(dumpdt, itm4+1, itm5-itm4-1) dtdd'
  58.              ||'             , substr(dumpdt, itm5+1, itm6-itm5-1) dthh'
  59.              ||'             , substr(dumpdt, itm6+1, itm7-itm6-1) dtmi'
  60.              ||'             , substr(dumpdt, itm7+1)              dtss'
  61.              ||'          from (select rowid rwid'
  62.              ||'                     , '||colNm||' dt'
  63.              ||'                     , dump('||colNm||') dumpdt'
  64.              ||'                     , instr(dump('||colNm||'),'' '',1,2) itm1'
  65.              ||'                     , instr(dump('||colNm||'),'','',1,1) itm2'
  66.              ||'                     , instr(dump('||colNm||'),'','',1,2) itm3'
  67.              ||'                     , instr(dump('||colNm||'),'','',1,3) itm4'
  68.              ||'                     , instr(dump('||colNm||'),'','',1,4) itm5'
  69.              ||'                     , instr(dump('||colNm||'),'','',1,5) itm6'
  70.              ||'                     , instr(dump('||colNm||'),'','',1,6) itm7'
  71.              ||'                  from '||tabNm
  72.              ||'                 where '||colNm||' is not null'
  73.              ||'               )'
  74.              ||'       )'
  75.              ||' where dtce not between ''110'' and ''199'''
  76.              ||'   and dtyy not between ''100'' and ''199'''
  77.              ||'   and dtmm not between ''1''   and ''12'''
  78.              ||'   and dtdd not between ''1''   and ''31'''
  79.              ||'   and dthh not between ''1''   and ''24'''
  80.              ||'   and dtmi not between ''1''   and ''60'''
  81.              ||'   and dtss not between ''1''   and ''60''';
  82.    
  83.     OPEN crsSelec FOR crsBody;
  84.     LOOP
  85.       FETCH crsSelec INTO rwid2, dt, dumpdt, dtce, dtyy, dtmm, dtdd, dthh, dtmi, dtss;
  86.       EXIT WHEN crsSelec%notfound;
  87.       IF crsSelec%rowcount = 1 THEN
  88.         DBMS_OUTPUT.put_line('entity: '||tabNm||' / element: '||colNm);
  89.         DBMS_OUTPUT.put_line('rowid              date        dump(date)                                         dtce dtyy dtmm dtdd dthh dtmi dtss');
  90.         DBMS_OUTPUT.put_line('—————— ———– ————————————————– —- —- —- —- —- —- —-');
  91.       END IF;
  92.       DBMS_OUTPUT.put_line
  93.       ( rwid2
  94.         ||' '||rpad(dt,11)
  95.         ||' '||rpad(dumpdt,50)
  96.         ||' '||lpad(dtce,4)
  97.         ||' '||lpad(dtyy,4)
  98.         ||' '||lpad(dtmm,4)
  99.         ||' '||lpad(dtdd,4)
  100.         ||' '||lpad(dthh,4)
  101.         ||' '||lpad(dtmi,4)
  102.         ||' '||lpad(dtss,4)
  103.       );
  104.     END LOOP;
  105.     CLOSE crsSelec;
  106.    
  107.     DELETE FROM xxx_tmptable_xxx
  108.      WHERE ROWID = rwid1;
  109.     COMMIT;
  110.   END LOOP;
  111. END;
  112. /
  113. DROP TABLE xxx_tmptable_xxx
  114. /
  115.  
  116. spool off
1 Comment :, , , , , , more...

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!

Blogroll

A few highly recommended websites...