Withdata Software

DBF to Oracle: Type Mapping, CLOB and the Date Trap

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:

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.

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:

  1. Set NLS_DATE_FORMAT for 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).

  1. 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.

  1. Load into VARCHAR2 columns and convert afterwards with TO_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:

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.

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:

use on the text side.

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.