{"id":73327,"date":"2012-03-27T14:48:48","date_gmt":"2012-03-27T14:48:48","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/ora-01801-date-format-is-too-long-for-internal-buffer\/"},"modified":"2021-07-14T13:08:00","modified_gmt":"2021-07-14T13:08:00","slug":"ora-01801-date-format-is-too-long-for-internal-buffer","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/ora-01801-date-format-is-too-long-for-internal-buffer\/","title":{"rendered":"ORA-01801: Date Format is too Long for Internal Buffer"},"content":{"rendered":"

Oracle 10.2.0.1 (I know, version out-of-date) on Windows 2003:<\/p>\n

The other day one of my stored procedures error’ed out with:<\/p>\n

ORA-01801: date format is too long for internal buffer<\/em><\/strong> somewhere near a line in which I was doing the following assignment:<\/p>\n

d := to_char(c.date_field,'YYYYMMDD');<\/pre>\n
Where d is of type varchar2, and c is a row from a cursor for loop, and date_field is a date column in the cursor. Selecting the date field from the database didn\u2019t look odd:<\/p>\n
select date_field from table_name t where t.id = ;<\/pre>\n
’04-AUG-17′<\/p>\n
However, selecting to_char did yield something odd:<\/p>\n
select to_char(date_field,'YYYYMMDD') from table_name t where t.id = ;<\/pre>\n
‘00000000’<\/p>\n
Odd, no 04, no AUG and no 17.<\/p>\n
Find out how I resolved this by reading the post on my Oracle Musings<\/a> blog.<\/p>\n","protected":false},"excerpt":{"rendered":"
Oracle 10.2.0.1 (I know, version out-of-date) on Windows 2003: The other day one of my stored procedures error’ed out with: ORA-01801: date format is too long for internal buffer somewhere near a line in which I was doing the following assignment: d := to_char(c.date_field,’YYYYMMDD’); Where d is of type varchar2, and c is a row from a cursor for loop,…<\/p>\n","protected":false},"author":316178,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[],"coauthors":[],"class_list":["post-73327","post","type-post","status-publish","format-standard","hentry","category-oracle-databases"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73327","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316178"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73327"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73327\/revisions"}],"predecessor-version":[{"id":91774,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73327\/revisions\/91774"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73327"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73327"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73327"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73327"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}