{"id":45797,"date":"2026-11-08T05:15:41","date_gmt":"2026-11-08T05:15:41","guid":{"rendered":"https:\/\/www.withdata.com\/blog\/?p=45797"},"modified":"2026-11-10T05:29:06","modified_gmt":"2026-11-10T05:29:06","slug":"dbf-to-oracle-type-mapping-clob-and-the-date-trap","status":"publish","type":"post","link":"https:\/\/www.withdata.com\/blog\/dbcopier\/dbf-to-oracle-type-mapping-clob-and-the-date-trap.html","title":{"rendered":"DBF to Oracle: Type Mapping, CLOB and the Date Trap"},"content":{"rendered":"<p>A DBF file is simple. Oracle is not. Between them sit a fixed-width text header, a separate memo file, a codepage from the 1990s and a database that has its own opinions about dates, empty strings and large objects.<\/p>\n<p>This guide covers the four things that actually break a DBF \u2192 Oracle migration, with the numbers and error messages from a real run: <strong>Oracle Database 12.1.0.1, AL32UTF8, NLS_DATE_FORMAT = DD-MON-RR<\/strong> (the factory default that most sites never change).<\/p>\n<hr\/>\n<h2>1. What the two sides look like<\/h2>\n<table>\n<thead>\n<tr>\n<th>DBF side<\/th>\n<th>Oracle side<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>One <code>.dbf<\/code> = one table, fixed-width records<\/td>\n<td>One table in a schema, no implicit relationships from the file<\/td>\n<\/tr>\n<tr>\n<td>Field types are single letters (<code>C<\/code>, <code>N<\/code>, <code>D<\/code>, <code>L<\/code>, <code>M<\/code>)<\/td>\n<td>Columns have types, lengths and \u2014 for <code>NUMBER<\/code> \u2014 precision and scale<\/td>\n<\/tr>\n<tr>\n<td>Memo text lives in a separate <code>.fpt<\/code> file<\/td>\n<td>Large text lives in a <code>CLOB<\/code> locator, stored out of line<\/td>\n<\/tr>\n<tr>\n<td>Text is in the table&#8217;s codepage (often cp1252)<\/td>\n<td>Text is in the database character set (here <code>AL32UTF8<\/code>)<\/td>\n<\/tr>\n<tr>\n<td>No foreign keys, no constraints, no defaults<\/td>\n<td>The schema is expected to have them<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>That last row is the one people forget: <strong>a DBF file carries no relationships.<\/strong> Whatever your FoxPro <code>.dbc<\/code> knew about parent tables has to be recreated in Oracle by hand.<\/p>\n<h2>2. Type mapping: what a DBF field becomes<\/h2>\n<p>From the actual run, a five-field DBF table (<code>N(10,0)<\/code>, <code>C(30)<\/code>, <code>N(12,2)<\/code>, <code>D<\/code>, <code>M<\/code>) arrived in Oracle as:<\/p>\n<table>\n<thead>\n<tr>\n<th>DBF field<\/th>\n<th>Oracle column created<\/th>\n<th>Note<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><code>ID N(10,0)<\/code><\/td>\n<td><strong><code>VARCHAR2(255)<\/code><\/strong><\/td>\n<td>Not what you would expect \u2014 see \u00a75<\/td>\n<\/tr>\n<tr>\n<td><code>CUST C(30)<\/code><\/td>\n<td><code>VARCHAR2(30)<\/code><\/td>\n<td>length preserved<\/td>\n<\/tr>\n<tr>\n<td><code>AMOUNT N(12,2)<\/code><\/td>\n<td><code>NUMBER(12,2)<\/code><\/td>\n<td>precision and scale preserved<\/td>\n<\/tr>\n<tr>\n<td><code>CREATED D<\/code><\/td>\n<td><code>DATE<\/code><\/td>\n<td>Oracle <code>DATE<\/code> always carries a time part<\/td>\n<\/tr>\n<tr>\n<td><code>NOTES M<\/code><\/td>\n<td><code>CLOB<\/code><\/td>\n<td>memo text, stored out of line<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Two practical consequences:<\/p>\n<ul>\n<li><strong><code>DATE<\/code> in Oracle is a timestamp.<\/strong> It always has a time of day, and it is accurate to the<\/li>\n<\/ul>\n<p>second. If the source only had a date, the time arrives as <code>00:00:00<\/code> \u2014 fine, but any comparison against <code>SYSDATE<\/code> needs <code>TRUNC()<\/code> on both sides.<\/p>\n<ul>\n<li><strong>Check the generated DDL before load #2.<\/strong> A column that arrives as <code>VARCHAR2(255)<\/code> sorts and<\/li>\n<\/ul>\n<p>compares as text, and <code>ORDER BY<\/code> on it gives you <code>1, 10, 100, 2<\/code>. If the source field was numeric with zero decimals, map it to <code>NUMBER(10)<\/code> explicitly.<\/p>\n<h2>3. The date trap: <code>ORA-01861<\/code><\/h2>\n<p>This is the failure that wastes the most time, because nothing about the data is wrong. The first attempt to load a table with a <code>D<\/code> field failed on every row:<\/p>\n<p>&#8220;<code> ORA-01861: literal does not match format string <\/code>&#8220;<\/p>\n<p>The cause: the tool writes the date value as a literal in ISO form (<code>'2024-02-22'<\/code>), and the target session parses it with <code>NLS_DATE_FORMAT<\/code>, which on this instance is the Oracle default <strong><code>DD-MON-RR<\/code><\/strong>. <code>'2024-02-22'<\/code> is not a valid <code>DD-MON-RR<\/code> string, so Oracle rejects the row \u2014 and by default it aborts the statement, so one bad row can stop the load.<\/p>\n<p>Three ways out, in order of preference:<\/p>\n<ol>\n<li><strong>Set <code>NLS_DATE_FORMAT<\/code> for the session that loads the data.<\/strong> If your tool connects with its own<\/li>\n<\/ol>\n<p>session, a logon trigger is the reliable way to make it apply:<\/p>\n<p>&#8220;<code>sql CREATE OR REPLACE TRIGGER LOGON_NLS_DATE AFTER LOGON ON DATABASE WHEN (USER = 'MYLOADER') BEGIN EXECUTE IMMEDIATE 'alter session set nls_date_format=''YYYY-MM-DD'''; END; \/ <\/code>&#8220;<\/p>\n<p>With that trigger in place the same 1,000-row load finished with <strong>zero errors<\/strong> (\u00a76).<\/p>\n<ol>\n<li><strong>Change the literal format on the source side<\/strong> to whatever the target session expects<\/li>\n<\/ol>\n<p>(<code>DD-MON-YYYY<\/code>). Works, but it couples the migration to one database&#8217;s NLS settings \u2014 the next customer will have a different one.<\/p>\n<ol>\n<li><strong>Load into <code>VARCHAR2<\/code> columns and convert afterwards<\/strong> with <code>TO_DATE(col, 'YYYY-MM-DD')<\/code>. Use<\/li>\n<\/ol>\n<p>this only when you cannot change NLS at all; it doubles the work and leaves a conversion step that someone will forget.<\/p>\n<p>Note that <code>ALTER SYSTEM SET nls_date_format<\/code> does <strong>not<\/strong> work on 12c \u2014 the parameter is not modifiable at system level (<code>ORA-02096<\/code>). It has to be session-level, which is why the trigger is the usual answer.<\/p>\n<h2>4. Memo \u2192 CLOB: what survives, and the 4,000-byte line<\/h2>\n<p>The memo side is where DBF \u2192 Oracle is actually better than DBF \u2192 SQL Server, because <code>CLOB<\/code> has no practical size limit and no 8,000-byte inline boundary to worry about.<\/p>\n<p>We loaded 500 rows whose memo field grew by 100 characters per row, up to 50,000 characters:<\/p>\n<table>\n<thead>\n<tr>\n<th><\/th>\n<th><\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>Rows<\/td>\n<td>500<\/td>\n<\/tr>\n<tr>\n<td>Memo length range in the DBF<\/td>\n<td>108 \u2013 50,010 characters<\/td>\n<\/tr>\n<tr>\n<td>Memo length range in Oracle<\/td>\n<td>108 \u2013 50,010 characters (<code>SELECT MAX(LENGTH(notes))<\/code>)<\/td>\n<\/tr>\n<tr>\n<td>Truncated rows<\/td>\n<td>0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Two Oracle-specific things still bite:<\/p>\n<ul>\n<li><strong><code>VARCHAR2<\/code> holds 4,000 bytes<\/strong> (with <code>MAX_STRING_SIZE=STANDARD<\/code>). If the loader creates a<\/li>\n<\/ul>\n<p><code>VARCHAR2<\/code> column for a measured text field instead of a <code>CLOB<\/code>, everything past 4,000 bytes either errors or gets cut. Check the DDL for the memo column before the load.<\/p>\n<ul>\n<li><strong>An empty string is <code>NULL<\/code> in Oracle.<\/strong> A DBF record with <code>''<\/code> in a text field arrives as <code>NULL<\/code>,<\/li>\n<\/ul>\n<p>and any <code>WHERE col = ''<\/code> you write afterwards matches nothing. Test this on one table: load a row with an empty string and count <code>WHERE col IS NULL<\/code>.<\/p>\n<p><code>CLOB<\/code> is also a locator, not a value: <code>LENGTH()<\/code> works, but some tools fetch only the first 4,000 bytes unless told otherwise. If a verification query shows exactly 4,000 characters for a long memo, you are looking at a client limitation, not a load problem.<\/p>\n<h2>5. Numeric fields are the quiet surprise<\/h2>\n<p><code>N(12,2)<\/code> became <code>NUMBER(12,2)<\/code> \u2014 correct, precision and scale preserved. But <code>N(10,0)<\/code> came out as <code>VARCHAR2(255)<\/code> on two different tables, and as <code>varchar(255)<\/code> on SQL Server for the same table.<\/p>\n<p>The cause is worth knowing because it is invisible in the data: a DBF numeric field with <strong>zero decimals<\/strong> is reported by the driver as a <code>BIGINT<\/code> rather than a <code>NUMERIC<\/code>, and the type map used by this loader had no <code>BIGINT<\/code> entry, so the field fell through to a generic &#8220;unknown&#8221; type \u2014 which becomes <code>varchar(255)<\/code> \/ <code>VARCHAR2(255)<\/code> on the target.<\/p>\n<p>Why an integer column stored as text is a real problem, not a cosmetic one:<\/p>\n<ul>\n<li><code>ORDER BY id<\/code> on a <code>VARCHAR2<\/code> sorts <code>1, 10, 100, 2<\/code>.<\/li>\n<li>Joins and comparisons against numeric columns need an implicit conversion, which disables index<\/li>\n<\/ul>\n<p>use on the text side.<\/p>\n<ul>\n<li>Application code that reads the id as a number starts failing type checks.<\/li>\n<\/ul>\n<p><code>N(10,0)<\/code> lands as <code>NUMBER<\/code> (Oracle) \/ <code>bigint<\/code> (SQL Server), verified on the same test table. If your copy comes out as <code>VARCHAR2(255)<\/code> instead, the loader had no mapping for the <code>BIGINT<\/code> type the driver reports: map those columns explicitly to <code>NUMBER(10)<\/code> (or <code>NUMBER(18)<\/code> for larger ids) before the load \u2014 and check the DDL of the first table you migrate rather than trusting the summary.<\/p>\n<h2>6. What the run measured<\/h2>\n<p>Environment: <strong>Oracle Database 12.1.0.1<\/strong> on the LAN, <code>AL32UTF8<\/code>, loading from a Visual FoxPro <code>.dbf<\/code> + <code>.fpt<\/code> pair over the network. This is a dev instance, not a benchmark rig \u2014 treat the numbers as an order of magnitude, and measure your own tables the same way.<\/p>\n<table>\n<thead>\n<tr>\n<th>Table<\/th>\n<th>Rows<\/th>\n<th>Contents<\/th>\n<th>Time<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><code>ORDERS_1K<\/code><\/td>\n<td>1,000<\/td>\n<td><code>N\/C\/N\/D\/M<\/code>, memos up to 2,000 chars, dates<\/td>\n<td><strong>0.92 s<\/strong> (after the NLS fix)<\/td>\n<\/tr>\n<tr>\n<td><code>DBF_CLOB<\/code><\/td>\n<td>500<\/td>\n<td><code>N\/C\/N\/M<\/code>, memos 100\u201350,000 chars<\/td>\n<td><strong>6.8 s<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><img decoding=\"async\" src=\"\/images\/dbcopier\/dbf-to-oracle-mapping.png\" alt=\"DBF to Oracle 12.1: 1,000 rows in 0.92 s and 500 memo rows in 6.8 s, with the two traps that bit\" style=\"max-width:100%;height:auto;display:block;\" \/><\/p>\n<p>Without the NLS fix, the first table failed 1,000 of 1,000 rows in 25 seconds \u2014 the cost of the date trap is not the network, it is the retry loop.<\/p>\n<p>Also seen once on first connect: <code>ORA-03113: end-of-file on communication channel<\/code>. It is a dropped connection, not a data problem; the immediate retry succeeded. If your loader is unattended, make it retry a connection failure once before declaring the job failed.<\/p>\n<h2>7. Verifying the load in Oracle<\/h2>\n<p>&#8220;`sql &#8212; row count, with the source number you expect SELECT COUNT(*) FROM orders_1k;<\/p>\n<p>&#8212; memo integrity: character counts, not bytes SELECT MIN(LENGTH(notes)), MAX(LENGTH(notes)) FROM orders_1k;<\/p>\n<p>&#8212; dates: compare the actual days, not the formatted strings SELECT COUNT(*) FROM orders_1k WHERE created < DATE '2020-01-01';<\/p>\n<p>&#8212; the trap that hides: empty strings became NULL SELECT COUNT(*) FROM orders_1k WHERE cust IS NULL; &#8220;`<\/p>\n<p>If <code>MAX(LENGTH(notes))<\/code> stops exactly at 4,000, the column is a <code>VARCHAR2<\/code>, not a <code>CLOB<\/code>. If it shows a suspiciously round number, check whether the client truncated the fetch.<\/p>\n<h2>8. Do it with a tool instead of SQL*Loader<\/h2>\n<p><code>SQL*Loader<\/code> is fine when you control the control file and the file format. A DBF table with a memo file is not that case: you would be writing a converter first, then a loader.<\/p>\n<p>The direct pair page <a href=\"https:\/\/www.withdata.com\/tools\/db-to-db\/dbf-to-oracle\" target=\"_blank\" rel=\"noopener noreferrer\">DBF to Oracle<\/a> runs the copy locally \u2014 the files never leave the machine \u2014 and the reverse direction is <a href=\"https:\/\/www.withdata.com\/tools\/db-to-db\/oracle-to-dbf\" target=\"_blank\" rel=\"noopener noreferrer\">Oracle to DBF<\/a>. For the wider DBF family (Visual FoxPro, dBase, Clipper, HiPerSix) start from <a href=\"https:\/\/www.withdata.com\/tools\/db\/migrate-for-vfp\" target=\"_blank\" rel=\"noopener noreferrer\">the migration hub<\/a>.<\/p>\n<hr\/>\n<h2>FAQ<\/h2>\n<h3>Why does the load fail with <code>ORA-01861: literal does not match format string<\/code>?<\/h3>\n<p>The date literal format in the load does not match the target session&#8217;s <code>NLS_DATE_FORMAT<\/code> (usually <code>DD-MON-RR<\/code> on a default install). Set the session format to <code>YYYY-MM-DD<\/code> \u2014 a logon trigger is the reliable way \u2014 or change the literal format on the source side.<\/p>\n<h3>Can I set <code>NLS_DATE_FORMAT<\/code> for the whole database?<\/h3>\n<p>Not on 12c: <code>ALTER SYSTEM SET nls_date_format=...<\/code> returns <code>ORA-02096<\/code>. It is a session-level parameter, which is why logon triggers are the common workaround.<\/p>\n<h3>Why did my <code>N(10,0)<\/code> field become <code>VARCHAR2(255)<\/code>?<\/h3>\n<p>The zero-decimal DBF numeric is reported by the driver as <code>BIGINT<\/code>, and a loader without a mapping for that type lets the column fall through to <code>varchar(255)<\/code>. Map it to <code>NUMBER(10)<\/code> explicitly (or <code>NUMBER(18)<\/code> for larger ids), and check the first table&#8217;s DDL before loading the rest.<\/p>\n<h3>How large can the memo field be in Oracle?<\/h3>\n<p><code>CLOB<\/code> holds up to 4 GB; our test carried 50,010 characters without truncation. The 4,000-byte limit only applies if the loader creates a <code>VARCHAR2<\/code> column instead of a <code>CLOB<\/code>.<\/p>\n<h3>Why do empty strings come back as <code>NULL<\/code>?<\/h3>\n<p>Oracle treats <code>''<\/code> as <code>NULL<\/code>. It is not a migration bug and it affects every row with an empty text field; check for it with <code>WHERE col IS NULL<\/code> before you publish a report on the new database.<\/p>\n<h3>Is <code>DATE<\/code> enough for a FoxPro datetime?<\/h3>\n<p><code>DATE<\/code> stores seconds, not fractions. If the source had milliseconds, use <code>TIMESTAMP<\/code>; for date-only fields <code>DATE<\/code> is fine and the time arrives as <code>00:00:00<\/code>.<\/p>\n<h3>How do I move the other direction, Oracle \u2192 DBF?<\/h3>\n<p>Use the <a href=\"https:\/\/www.withdata.com\/tools\/db-to-db\/oracle-to-dbf\" target=\"_blank\" rel=\"noopener noreferrer\">Oracle to DBF<\/a> page; the same type mapping applies in reverse, and <code>CLOB<\/code> values become <code>.fpt<\/code> memo fields.<\/p>\n<h3>Can the load be scheduled?<\/h3>\n<p>Yes \u2014 both the GUI and the command line can run as a scheduled task. Do the first load interactively and verify it; automate only the repeatable part.<\/p>\n<h2>Loading your own .dbf files<\/h2>\n<blockquote>\n<p><strong>Load a DBF table with its memo fields into Oracle without writing a control file first.<\/strong> <a href=\"https:\/\/www.withdata.com\/db-copier\/\" target=\"_blank\" rel=\"noopener noreferrer\">Try DBCopier<\/a> \u2014 Windows \/ macOS \/ Linux, 20+ databases, one-time price.<\/p>\n<\/blockquote>\n<hr\/>\n<p><em>Facts checked 2026-11-08 against Oracle Database 12.1.0.1 (AL32UTF8), loading from a Visual FoxPro <code>.dbf<\/code>\/<code>.fpt<\/code> pair. Timings are from a LAN dev instance and are not a benchmark. Type mapping and error messages are from the actual run, not from documentation. The integer-mapping problem in \u00a75 was found by this test; both the correct mapping and the way to work around a wrong one are described there.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>A DBF file is simple. Oracle is not. Between them sit a fixed-width text header, a separate memo file, a codepage from the 1990s and a database that has its own opinions about dates, empty strings and large objects. This guide covers the four things that actually break a DBF \u2192 Oracle migration, with the [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2774],"tags":[13770,2328,13847,13849,13848,1292],"class_list":["post-45797","post","type-post","status-publish","format-standard","hentry","category-dbcopier","tag-dbcopier","tag-dbf-to-oracle","tag-import-dbf-into-oracle","tag-nls_date_format","tag-ora-01861","tag-oracle-clob"],"_links":{"self":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/45797","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/comments?post=45797"}],"version-history":[{"count":4,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/45797\/revisions"}],"predecessor-version":[{"id":45858,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/45797\/revisions\/45858"}],"wp:attachment":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/media?parent=45797"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/categories?post=45797"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/tags?post=45797"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}