Loading LOB Data with SQL*Loader

A batch of customer photographs usually arrives with identifying information: which customer each photograph belongs to, when it was created, and where its file can be found. Loading the photographs requires more than moving bytes. The loader must associate each file with the correct row and preserve its contents in the intended column.

Oracle SQL*Loader can load large object data from a primary input file or from separate files. This lesson develops a one-photograph-per-file example for a BLOB column. You will prepare the target table, describe the input with a control file, run the loader, and distinguish a completed process from verified content.

Understand the four parts of the load

The target table defines what the database stores. A control file describes how SQL*Loader interprets the input. A primary data file supplies identifying fields for each row. Separate photograph files supply the binary content. In this example, each primary record contains a filename that directs SQL*Loader to the appropriate photograph.

These filenames describe a practice arrangement, not files already installed with this article. Supply two real photographs before attempting the load. Renaming a text file with a .jpg extension does not create a valid photograph, and SQL*Loader does not establish image validity merely by storing its bytes.

Primary data, LOBFILEs, and SDFs

A secondary data file, or SDF, has records and fields and supports other loading structures, including collection-related arrangements. It is not a substitute name for every LOBFILE specification. Keep the two mechanisms distinct when reading older control files or adapting an example.

Prepare the practice environment

Use a practice database account with permission to create and load the demonstration table, adequate storage, and a working SQL*Loader client. The client must be able to connect to the intended database service. Confirm the account and service before loading, particularly when several environments use similar table names.

For this example, create the table below through a SQL client before running SQL*Loader. It is an ordinary relational table. Its name does not imply an Oracle object type, and it does not depend on the definitions used in earlier lessons.

CREATE TABLE lesson7_customer_photos (
    customer_id    NUMBER PRIMARY KEY,
    created_date   DATE NOT NULL,
    customer_photo BLOB
);

Run this setup separately from unrelated uncommitted work because Oracle DDL has implicit commit behavior. The name must be available in the practice schema. If it already exists, inspect its definition and data rather than replacing it simply to repeat the example.

The code and control-file examples are documentation-reviewed illustrations. They have not been executed against an Oracle instance for this lesson. The expected identifiers are defined below, but actual photograph sizes and load results depend on the files you supply.

Arrange the input files

Create a working folder on the machine where SQL*Loader will run. Put cpot.ctl, cpot.dat, and the two photograph files in that folder. Start the loader from that same folder so that this example's relative filenames have an explicit working-directory context.

For ordinary local-file SQL*Loader input, the loader process reads the files on its own machine and sends data to the database. A database DIRECTORY object is not required merely because this example uses LOBFILEs. BFILE references and server-side external-table loading are different mechanisms with different file-access arrangements.

For production jobs, use an intentional path convention and verify access under the account that actually runs the job. A path that works in an interactive session may be unavailable to a scheduled process. Do not assume that supplying an absolute control-file path also changes the working directory used by every referenced file.

Save the following as cpot.dat. It has no header row. Each line contains a customer identifier, a date, and a photograph filename, followed by a terminating comma.

701,2026-10-21,customer_701.jpg,
702,2026-10-21,customer_702.jpg,

Check both associations before loading. A valid JPEG stored under the wrong customer's identifier is still an incorrect result. The loader cannot infer whether the filename in a record represents the intended person.

Write the control file

Save the following as cpot.ctl. The filename is a label for the example, not a line to include in the control file. Use ordinary ASCII quotation marks in the saved text.

LOAD DATA
INFILE 'cpot.dat'
APPEND
INTO TABLE lesson7_customer_photos
FIELDS TERMINATED BY ','
(
    customer_id    INTEGER EXTERNAL,
    created_date   DATE "YYYY-MM-DD",
    photo_filename FILLER CHAR(1024),
    customer_photo LOBFILE(photo_filename) TERMINATED BY EOF
)

LOAD DATA begins the specification. INFILE names the primary metadata file. APPEND retains existing rows while loading additional ones. It does not replace a matching customer record or deduplicate repeated input. INTO TABLE identifies the practice table created earlier.

The comma delimiter separates the three fields in each primary record. The field list maps those fields and the external photograph content to their respective destinations. Keep the order consistent with cpot.dat; changing a column name alone does not rearrange the input data.

INTEGER EXTERNAL reads the customer identifier from its textual representation. The DATE mask matches the numeric year-month-day values in the input. This avoids depending on the language used for abbreviated month names.

photo_filename is a FILLER field. SQL*Loader reads it from the metadata record but does not insert it into a table column. CHAR(1024) bounds this filename field under the applicable length semantics; it is not a 1,024-byte maximum for the photograph. The sample names are short ASCII strings.

The final mapping supplies customer_photo from the file named by photo_filename. TERMINATED BY EOF identifies the complete file as the value. There is no fourth photograph-text field in cpot.dat. Oracle's LOB loading guide documents this whole-file pattern.

Keep binary loading separate from text conversion

The destination in this example is BLOB because the application retains photograph bytes. The source files are not character strings to trim, pad, or convert to another character set. A photograph can contain arbitrary byte values, including values that would be delimiters in a text record.

For a CLOB or NCLOB variation, review source encoding and the appropriate SQL*Loader character-set settings. Do not change only the destination type and assume that every other part of a binary example remains suitable. The input format and character interpretation must agree with the target's purpose.

A BFILE variation would store an external-file reference rather than copy photograph bytes into a BLOB. The copied BLOB in this lesson is database-managed content; removing an original input photograph later does not turn the stored value into a broken BFILE reference. Retention of source files is nevertheless useful for audit and recovery of the loading process.

Run SQL*Loader

From the working folder containing the example files, run the following command in an operating-system terminal, not at a SQL prompt. It explicitly chooses conventional path for this introductory load.

sqlldr CONTROL=cpot.ctl LOG=cpot.log BAD=cpot.bad DIRECT=FALSE

When prompted, supply the practice account and configured service, for example app_user@practice_service, followed by its password. Those names are placeholders for your environment. The command intentionally contains no password. An existing configured wallet is another authentication arrangement for an automated job.

CONTROL selects the control file; LOG and BAD name diagnostic output. INFILE already identifies cpot.dat, so a separate DATA argument is unnecessary for this example. Before rerunning, preserve earlier diagnostic files or choose new output names so that evidence from the previous attempt remains available.

Check the loader's exit status immediately after it finishes. In PowerShell, inspect $LASTEXITCODE; in other shells, use their corresponding mechanism. Interpret the status using the platform's SQL*Loader documentation and read the log. A console message that the process ended is not proof that both photographs loaded correctly.

Oracle's command-line reference describes connection parameters, file options, and exit statuses. Use those definitions when integrating the command into a job rather than assuming every nonzero status has the same meaning on every platform.

Read the diagnostics carefully

The log reports how the load was interpreted and records processing results and errors. The bad file contains rejected input records under the applicable rules. A discard file, when used with selection criteria, serves a different purpose: it records input that was not selected for loading.

LOB failures require particular care. Oracle documents cases in conventional-path loading where a row remains loaded but its LOB is empty. LOBFILE and SDF contents are not copied into the bad file with rejected records. Therefore, an absent or empty bad file does not establish that every document arrived intact.

Review errors with the original metadata and binary files still available. A diagnostic filename alone may not contain the content needed to reproduce a failed record. Retaining the input set also makes it possible to confirm which photograph was intended for a particular identifier.

See Oracle's control-file reference for bad-file handling. Treat the log, exit status, and destination checks as complementary evidence rather than choosing whichever one appears most reassuring.

Verify the destination rows and LOBs

Connect to the same database service and schema used for loading. Query the expected identifiers rather than simply counting every row in the table. The following query reports whether each expected row is missing, whether its photograph is NULL or empty, and the stored byte length.

WITH expected (customer_id) AS (
    SELECT 701 FROM dual
    UNION ALL
    SELECT 702 FROM dual
)
SELECT e.customer_id,
       p.created_date,
       CASE
           WHEN p.customer_id IS NULL THEN 'MISSING ROW'
           WHEN p.customer_photo IS NULL THEN 'NULL LOB'
           WHEN DBMS_LOB.GETLENGTH(p.customer_photo) = 0
               THEN 'EMPTY LOB'
           ELSE 'POPULATED LOB'
       END AS load_state,
       DBMS_LOB.GETLENGTH(p.customer_photo) AS photo_bytes
FROM expected e
LEFT JOIN lesson7_customer_photos p
    ON p.customer_id = e.customer_id
ORDER BY e.customer_id;

DBMS_LOB.GETLENGTH reports a BLOB length in bytes. Compare each populated value with the size of its corresponding source file. No fixed expected lengths are shown here because you supply the photographs. An empty input file would legitimately produce a different expectation from an ordinary nonempty photograph.

Matching length is a useful check, but different files can have the same size. Where exact fidelity matters, add a byte or hash comparison through the application's supported tooling and confirm that the image can be decoded. Also verify the customer association and date. Content integrity and correct row identity are separate requirements.

Handle incomplete loads and retries

APPEND is not a retry policy. If customer 701 loaded and customer 702 failed, rerunning both records may encounter the existing primary key while leaving the original content problem unresolved. Determine which rows exist and which LOBs need attention before selecting the next action.

Preserve the failed input, diagnostics, and observed destination state. In a real application, decide whether recovery uses a corrected input subset, a controlled update, or a separate staging process. Avoid clearing the entire target table merely to make a second demonstration run easier.

SQL*Loader may save data during processing. A ROLLBACK issued later in a separate SQL session is not a universal undo operation for that load. Use a disposable practice table for experimentation and a defined recovery plan for business data.

Consider other load paths deliberately

Direct-path loading is available for supported LOB workloads, but it has different restrictions and error behavior. Oracle documents that errors can leave empty or truncated LOB content in direct-path scenarios. Choosing a faster path does not remove the need to verify the resulting values.

Parallel execution likewise depends on the operation, target, and configuration. Do not add DIRECT=TRUE or PARALLEL=TRUE as unexplained improvements to this example. Establish correctness with the intended files first, then evaluate supported performance options using the actual workload.

The same principle applies to field lengths and buffers. A filename-field maximum is not merely a suggestion about memory, and a loader buffer is not the total capacity of the destination LOB. Review the relevant field definitions and loading-path rules before changing them.

Review the completed workflow

You now have a sequence that starts with declared inputs and ends with checks of the stored result: create the practice table, prepare two metadata records and their photographs, map the fields, invoke SQL*Loader, examine diagnostics, and verify the expected rows and content. Each step answers a different question about the load.

Before adapting the example, explain the role of the FILLER field and why the BLOB is populated from a separate file. Also explain why an empty bad file is insufficient evidence of success. Those distinctions help prevent a syntactically correct control file from being mistaken for a fully verified loading process.

Inserting LOB Data SQL*Loader Exercise

Apply the concepts in the Inserting LOB Data SQL Loader-Exercise. Follow its instructions in your practice environment; opening the exercise does not itself run a database load.

In the next lesson, Updating LOB Data, you will work with changes to existing content using DBMS_LOB.