Tag: dateformat too long for internal buffer
ORA 1801 – Dateformat too long for internal buffer
by mysticslayer 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.
-
-
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
