Load Product Photos with SQL*Loader - Exercise

Objective: Load two image files into BLOB attributes of an Oracle object table and verify the loaded data.

This is a 10-point self-assessment. The result page shows a model answer; this website does not run SQL*Loader, automatically grade your response, or send it to a tutor.

Scenario

A pet-supply store needs to import two product photographs. Each metadata record identifies the product, the photograph date, and a local filename. SQL*Loader copies the file contents into the database BLOB; the filename itself is not the photograph.

Practice setup

Use Oracle AI Database 26ai and an installed SQL*Loader client. In a dedicated practice schema, create this object type and object table once, using SQL*Plus, SQLcl, or a compatible script runner. The schema needs CREATE TYPE, CREATE TABLE, and tablespace quota. If these names already exist, use a fresh practice schema rather than replacing existing objects. The slash executes CREATE TYPE in script mode.

CREATE TYPE lesson7_product_photo_t AS OBJECT (
  product_id NUMBER,
  product_photo BLOB,
  created_date DATE
);
/
CREATE TABLE product_photo_obj_table OF lesson7_product_photo_t
  (CONSTRAINT lesson7_photo_pk PRIMARY KEY (product_id));

Supply two real, nonempty GIF files named parrot.gif and collar.gif; this page does not provide them. Record their file sizes in bytes. Put both files and your control/data files in one working folder on the machine running SQL*Loader. That operating-system account needs read access to the inputs and write access for the log and bad files.

Instructions

Create ppot.dat with comma-separated fields in this order: product ID, date in YYYY-MM-DD format, image filename. Use product 901 dated 2026-10-01 with parrot.gif and product 1001 dated 2026-10-02 with collar.gif.
Create ppot.ctl to append these records to PRODUCT_PHOTO_OBJ_TABLE. Use a FILLER field for the filename and a dynamic LOBFILE specification that reads each entire file.
Write a conventional-path SQL*Loader command naming the control, log, and bad files. Use your practice username and connect identifier, and let SQL*Loader prompt for the password.
If a practice database is available, run the command from the folder containing all four input files. Inspect the exit status and log, then verify both product IDs, dates, and BLOB lengths. Compare each length with its source file size. Otherwise, submit your plan and label results as expected rather than observed.

SQL*Loader runs in its own database session and can commit loaded rows. A later ROLLBACK in SQLcl will not undo its load. APPEND does not replace existing rows: duplicate primary keys are rejected. Inspect any partial load before attempting a rerun.

Two explanation questions

What does the .ctl file describe, and how do FILLER and LOBFILE work together?
What database and operating-system prerequisites must be satisfied? Does this conventional BLOB load require a database DIRECTORY object?

Self-assessment: 10 points

Control file and field mappings: 3 points.
Two correctly formatted metadata records: 1 point.
Runnable command with password prompting: 1 point.
Log review and row/date/BLOB-length verification: 2 points.
Explanation of the control file: 1 point.
Correct prerequisites and client-side file access: 2 points.

Submit your work

Include the control file, data records, command, verification query and results (or expected checks), and answers to both questions. Omit passwords.

References: Oracle 26ai: loading objects and LOBs and SQL*Loader command-line reference.