SQL Loader - Learn in 22 Examples

Explore how SQL Loader loads data from external files into Oracle tables, across networks, with multi-file loading, character set options, and detailed error reports.

Explore SQL Loader components, including the data file and control file, with outputs such as the log file, batch file, and discard file for loading data into an Oracle table.

Explore the SQL Loader course's first example, showing how a control file dictates loading data into a table, with fields terminated by a comma and a date format.

Load data from an external data file using a control file, handling comma-separated values with optional double quotes. Clear the sales table before loading and verify that seven rows are loaded.

Explore sql loader loading options: insert, append, replace, and truncate, using control files to load data, with guidelines on when a table must be empty and how each option behaves.

Explore how sql loader manages rejected data via a bad file, customize the bad file name with a control file, and use the log file to diagnose invalid records.

Use filler keyword to ignore fields when loading data, leaving them null in the table. Example 3 shows applying filler for product name and product category in the control file.

Learn how the constant keyword assigns a default value to a missing column, using product color with red as the default, and how to handle absent data during loading.

Learn to use Oracle sequel functions in the control file to modify data as it is loading into the Oracle database, including uppercasing product names and applying arithmetic transformations.

Learn to generate sequential key values in SQL loader using the sequence function or an Oracle sequence, with last-column and trailing-null rules.

Explore loading files with multiple field terminators in a single file and learn to specify different separators for columns via control files.

Process fixed-length data with a 66-byte record format and no line breaks. Define 66-byte fixed length in the control file and load seven records from the continuous stream.

Learn to handle variable record length in SQL Loader by using the first three characters of each record to indicate its length and configure the control file accordingly.

Learn to load a pipe-delimited record stream with SQL Loader, using quotes in the control file, and verify seven records populate the sales table.

Learn how to define fixed-length fields in sql loader by specifying start and end positions for each column, such as sales date, rating, and product.

Load a formatted report with SQL Loader by skipping header lines and using a dot as the data indicator, specifying column positions and numeric formats to load only data rows.

Learn to load multi-line records with sql loader by treating two lines as one, using continue if, and using a hashtag to start a new record and define card positions.

Learn to load multi-line records with sql loader by using concatenate to group every two lines, and map columns by start positions in the control file.

Learn to selectively load data with filters using the when and in clauses, apply only equality or non-equality operators, and capture discarded records in a discard file with SQL loader.

Learn to selectively load data using filters in SQL loader, applying second position equals 1 and 57-58 not equal to 20, with results stored in a discard file.

Load data into multiple tables by specifying the target table and listing the columns with csv load.

Combine filters with multi-table loading to insert data into the sales and sales_texas tables, using when clauses to discard records that don't meet the condition and load only the required data.

Learn to load data into objects with a control file, create a phone numbers object with four columns, add it to the sales table, and validate loaded data.

Learn to load data into a collection (varray) by defining an object type with the columns form tape and phone number, and using a control file to populate the collection.

Demonstrates loading large objects with sql loader by using a control file and infile to populate a clob column. A placeholder column captures filenames and handles end-of-file and null values.

Compare conventional loads with direct path loads, detailing SQL command processing, space management, buffer cache, and data blocks; explain faster loading via direct path formatting and extents for large data.

Compare direct path loads with conventional path loads, showing how to set options in the control file and via the command prompt, then verify loading success by a select statement.