SQLMentor // learn sql*loader

Learn Oracle SQL*Loader — Bulk Data Loading Made Easy

SQL*Loader (the sqlldr command) is Oracle's heavy-duty bulk-load utility — the right tool when you need to move millions of rows from flat files (CSV, fixed-width, JSON, etc.) into Oracle tables. This free tutorial covers control files, conventional vs direct path loads, performance tuning, and the gotchas that every DBA has to learn the hard way.

Each topic builds on a realistic example: a control file, a sample data file, the sqlldr invocation, and the resulting log. Use the sidebar to jump to a specific feature, or work through the curriculum in order from Introduction through to External Tables.

What you'll learn about Oracle SQL*Loader

  • The sqlldr command line and control-file syntax
  • Conventional path vs direct path loads — when to use each
  • INSERT / APPEND / REPLACE / TRUNCATE load modes
  • Delimited, fixed-width, and date/timestamp parsing
  • BAD, DISCARD, and log file diagnostics
  • WHEN clauses, multi-table loads, and SEQUENCE generation
  • External tables as the modern alternative to SQL*Loader

Common use cases

One-time data migrations

Loading historical data from a legacy system into Oracle when the source is a flat-file dump (CSV, TSV, or fixed-width). SQL*Loader handles millions of rows in a single sqlldr invocation and gives you per-row error reporting.

Nightly ETL drops

A common pattern: an upstream system writes a CSV to a shared directory each night; a cron job triggers sqlldr to load it into a staging table, which a PL/SQL package then transforms into the production schema.

Loading LOBs and large text blobs

SQL*Loader is the most reliable way to bulk-load CLOB/BLOB content into Oracle when each row references an external file. Direct path mode delivers the throughput external table loads cannot match.

Frequently asked questions

When should I use SQL*Loader vs external tables?
Use SQL*Loader for true bulk one-shot loads where direct-path performance matters and rows are read once. Use external tables when you need to query the file repeatedly with SQL, join it to real tables, or run transformations in pure SQL. External tables are easier; SQL*Loader is faster for very large loads.
What is the difference between conventional path and direct path?
Conventional path uses INSERT statements through the SQL engine — slower but supports triggers, constraints and indexes during the load. Direct path writes blocks directly above the high-water mark, bypassing the SQL engine — much faster but with constraint and trigger limitations.
How do I troubleshoot rows that fail to load?
SQL*Loader writes failed rows to a .bad file and the rejection reasons to the .log file. Rows that match a WHEN clause filter go to a .dis (discard) file. Always check all three files after a load; the log includes the exact line number, column, and Oracle error code for every rejected row.
Loading content