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 → Oracle migration, with the numbers and error messages from a real run: Oracle Database 12.1.0.1, AL32UTF8, NLS_DATE_FORMAT = DD-MON-RR (the factory default that most sites never change).
1. What the two sides look like
| DBF side | Oracle side |
|---|---|
One .dbf = one table, fixed-width records |
One table in a schema, no implicit relationships from the file |
Field types are single letters (C, N, D, L, M) |
Columns have types, lengths and — for NUMBER — precision and scale |
Memo text lives in a separate .fpt file |
Large text lives in a CLOB locator, stored out of line |
| Text is in the table’s codepage (often cp1252) | Text is in the database character set (here AL32UTF8) |
| No foreign keys, no constraints, no defaults | The schema is expected to have them |
That last row is the one people forget: a DBF file carries no relationships. Whatever your FoxPro .dbc knew about parent tables has to be recreated in Oracle by hand.
2. Type mapping: what a DBF field becomes
From the actual run, a five-field DBF table (N(10,0), C(30), N(12,2), D, M) arrived in Oracle as:
| DBF field | Oracle column created | Note |
|---|---|---|
ID N(10,0) |
VARCHAR2(255) |
Not what you would expect — see §5 |
CUST C(30) |
VARCHAR2(30) |
length preserved |
AMOUNT N(12,2) |
NUMBER(12,2) |
precision and scale preserved |
CREATED D |
DATE |
Oracle DATE always carries a time part |
NOTES M |
CLOB |
memo text, stored out of line |
Two practical consequences:
DATEin Oracle is a timestamp. It always has a time of day, and it is accurate to the
second. If the source only had a date, the time arrives as 00:00:00 — fine, but any comparison against SYSDATE needs TRUNC() on both sides.
- Check the generated DDL before load #2. A column that arrives as
VARCHAR2(255)sorts and
compares as text, and ORDER BY on it gives you 1, 10, 100, 2. If the source field was numeric with zero decimals, map it to NUMBER(10) explicitly.
3. The date trap: ORA-01861
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 D field failed on every row:
“ ORA-01861: literal does not match format string “
The cause: the tool writes the date value as a literal in ISO form ('2024-02-22'), and the target session parses it with NLS_DATE_FORMAT, which on this instance is the Oracle default DD-MON-RR. '2024-02-22' is not a valid DD-MON-RR string, so Oracle rejects the row — and by default it aborts the statement, so one bad row can stop the load.
Three ways out, in order of preference:
- Set
NLS_DATE_FORMATfor the session that loads the data. If your tool connects with its own
session, a logon trigger is the reliable way to make it apply:
“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; / “
With that trigger in place the same 1,000-row load finished with zero errors (§6).
- Change the literal format on the source side to whatever the target session expects
(DD-MON-YYYY). Works, but it couples the migration to one database’s NLS settings — the next customer will have a different one.
- Load into
VARCHAR2columns and convert afterwards withTO_DATE(col, 'YYYY-MM-DD'). Use
this only when you cannot change NLS at all; it doubles the work and leaves a conversion step that someone will forget.
Note that ALTER SYSTEM SET nls_date_format does not work on 12c — the parameter is not modifiable at system level (ORA-02096). It has to be session-level, which is why the trigger is the usual answer.
4. Memo → CLOB: what survives, and the 4,000-byte line
The memo side is where DBF → Oracle is actually better than DBF → SQL Server, because CLOB has no practical size limit and no 8,000-byte inline boundary to worry about.
We loaded 500 rows whose memo field grew by 100 characters per row, up to 50,000 characters:
| Rows | 500 |
| Memo length range in the DBF | 108 – 50,010 characters |
| Memo length range in Oracle | 108 – 50,010 characters (SELECT MAX(LENGTH(notes))) |
| Truncated rows | 0 |
Two Oracle-specific things still bite:
VARCHAR2holds 4,000 bytes (withMAX_STRING_SIZE=STANDARD). If the loader creates a
VARCHAR2 column for a measured text field instead of a CLOB, everything past 4,000 bytes either errors or gets cut. Check the DDL for the memo column before the load.
- An empty string is
NULLin Oracle. A DBF record with''in a text field arrives asNULL,
and any WHERE col = '' you write afterwards matches nothing. Test this on one table: load a row with an empty string and count WHERE col IS NULL.
CLOB is also a locator, not a value: LENGTH() 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.
5. Numeric fields are the quiet surprise
N(12,2) became NUMBER(12,2) — correct, precision and scale preserved. But N(10,0) came out as VARCHAR2(255) on two different tables, and as varchar(255) on SQL Server for the same table.
The cause is worth knowing because it is invisible in the data: a DBF numeric field with zero decimals is reported by the driver as a BIGINT rather than a NUMERIC, and the type map used by this loader had no BIGINT entry, so the field fell through to a generic “unknown” type — which becomes varchar(255) / VARCHAR2(255) on the target.
Why an integer column stored as text is a real problem, not a cosmetic one:
ORDER BY idon aVARCHAR2sorts1, 10, 100, 2.- Joins and comparisons against numeric columns need an implicit conversion, which disables index
use on the text side.
- Application code that reads the id as a number starts failing type checks.
N(10,0) lands as NUMBER (Oracle) / bigint (SQL Server), verified on the same test table. If your copy comes out as VARCHAR2(255) instead, the loader had no mapping for the BIGINT type the driver reports: map those columns explicitly to NUMBER(10) (or NUMBER(18) for larger ids) before the load — and check the DDL of the first table you migrate rather than trusting the summary.
6. What the run measured
Environment: Oracle Database 12.1.0.1 on the LAN, AL32UTF8, loading from a Visual FoxPro .dbf + .fpt pair over the network. This is a dev instance, not a benchmark rig — treat the numbers as an order of magnitude, and measure your own tables the same way.
| Table | Rows | Contents | Time |
|---|---|---|---|
ORDERS_1K |
1,000 | N/C/N/D/M, memos up to 2,000 chars, dates |
0.92 s (after the NLS fix) |
DBF_CLOB |
500 | N/C/N/M, memos 100–50,000 chars |
6.8 s |
Without the NLS fix, the first table failed 1,000 of 1,000 rows in 25 seconds — the cost of the date trap is not the network, it is the retry loop.
Also seen once on first connect: ORA-03113: end-of-file on communication channel. 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.
7. Verifying the load in Oracle
“`sql — row count, with the source number you expect SELECT COUNT(*) FROM orders_1k;
— memo integrity: character counts, not bytes SELECT MIN(LENGTH(notes)), MAX(LENGTH(notes)) FROM orders_1k;
— dates: compare the actual days, not the formatted strings SELECT COUNT(*) FROM orders_1k WHERE created < DATE '2020-01-01';
— the trap that hides: empty strings became NULL SELECT COUNT(*) FROM orders_1k WHERE cust IS NULL; “`
If MAX(LENGTH(notes)) stops exactly at 4,000, the column is a VARCHAR2, not a CLOB. If it shows a suspiciously round number, check whether the client truncated the fetch.
8. Do it with a tool instead of SQL*Loader
SQL*Loader 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.
The direct pair page DBF to Oracle runs the copy locally — the files never leave the machine — and the reverse direction is Oracle to DBF. For the wider DBF family (Visual FoxPro, dBase, Clipper, HiPerSix) start from the migration hub.
FAQ
Why does the load fail with ORA-01861: literal does not match format string?
The date literal format in the load does not match the target session’s NLS_DATE_FORMAT (usually DD-MON-RR on a default install). Set the session format to YYYY-MM-DD — a logon trigger is the reliable way — or change the literal format on the source side.
Can I set NLS_DATE_FORMAT for the whole database?
Not on 12c: ALTER SYSTEM SET nls_date_format=... returns ORA-02096. It is a session-level parameter, which is why logon triggers are the common workaround.
Why did my N(10,0) field become VARCHAR2(255)?
The zero-decimal DBF numeric is reported by the driver as BIGINT, and a loader without a mapping for that type lets the column fall through to varchar(255). Map it to NUMBER(10) explicitly (or NUMBER(18) for larger ids), and check the first table’s DDL before loading the rest.
How large can the memo field be in Oracle?
CLOB 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 VARCHAR2 column instead of a CLOB.
Why do empty strings come back as NULL?
Oracle treats '' as NULL. It is not a migration bug and it affects every row with an empty text field; check for it with WHERE col IS NULL before you publish a report on the new database.
Is DATE enough for a FoxPro datetime?
DATE stores seconds, not fractions. If the source had milliseconds, use TIMESTAMP; for date-only fields DATE is fine and the time arrives as 00:00:00.
How do I move the other direction, Oracle → DBF?
Use the Oracle to DBF page; the same type mapping applies in reverse, and CLOB values become .fpt memo fields.
Can the load be scheduled?
Yes — 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.
Loading your own .dbf files
Load a DBF table with its memo fields into Oracle without writing a control file first. Try DBCopier — Windows / macOS / Linux, 20+ databases, one-time price.
Facts checked 2026-11-08 against Oracle Database 12.1.0.1 (AL32UTF8), loading from a Visual FoxPro .dbf/.fpt 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 §5 was found by this test; both the correct mapping and the way to work around a wrong one are described there.