Oracle SQL*Loader is a powerful tool for bulk loading data into Oracle databases, no matter if the data comes from another source system, which can be in various formats, (CSV, XML, YAML, JSON, etc.) including non-Oracle platforms. This guide explains how to use SQL*Loader within a shell script to load data from a CSV file into an Oracle database with Flyway.

Prerequisites

1. Oracle Database: Ensure you have access to an Oracle database.
2. SQL*Loader Utility: This is typically included with the Oracle Client installation.  If using SQL*Loader with Flyway, you must have the ability to call the Oracle Client utilities from Flyway.
3. Data Load File: Prepare the CSV or other formatted file to be loaded, ensuring it is formatted properly.  Most failures occur from a poorly formatted data load file.
4. Control File: Create a control file to specify how the data in the data load file should be loaded.
5. Database User Credentials: Have the necessary credentials to access and write to the target database.

Create the Control File

The control file defines the mapping between the CSV file and the database table.  It can be written in a simple Notepad file, saved with the .ctl extension for SQL*Loader to identify it as the control file:

Example Control File (load_data.ctl):

LOAD DATA
INFILE 'data.csv'
INTO TABLE emp_salary
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
(
  column1,
  column2,
  column3
)

• INFILE: Path to the CSV file.
• INTO TABLE: Target table name in the Oracle database.
• FIELDS TERMINATED BY '|': Specifies that fields in the CSV are separated by a pipe. Carefully identify if there are string or data values that will use the delimiter.  Pipe is a good one to use, but comma-separated are absolutely supported.
• OPTIONALLY ENCLOSED BY '"': Handles text fields enclosed in double quotes.
• Column names must match those in the target table, skipping column names that aren’t included in the inserts and allow null values for rows.

Write the Shell Script

The shell script will invoke SQL*Loader and handle logging.  All files involved, including control file and CSV file are assumed to be in the same directory as the shell script.

Example Shell Script (load_data.sh):

#!/bin/bash

# Can add additional dynamic parameters for script as desired.
read -p "Enter Oracle username: " DB_USER
read -p "Enter Oracle Service as host:port/service: " DB_CONNECTION
# Note: DB_CONNECTION format=host:port/service
read -p "Enter Oracle password: " DB_PASSWORD
# read -p "Enter the SQL*Loader Work Directory: " WORK_DIR

# Variables must be updated in script to be run successfully in current state.
# Can be dynamically declared to automate further.
DTA_FILE="data.csv"
CONTROL_FILE="load_data.ctl"
LOG_FILE="load_data.log"
BAD_FILE="load_data.bad"
DISCARD_FILE="load_data.dsc"
CONNECTION_STRING="$DB_USER/$DB_PASSWORD@$DB_CONNECTION"
# Change to a specific SQL*Loader Work directory for further automation.
# cd $WORK_DIR

# Check if data load file exists
if [ ! -f "$DTA_FILE" ]; then
  echo "Error: data file '$DTA_FILE' not found."
  exit 1
fi                                              

# Run SQL*Loader
sqlldr userid=$CONNECTION_STRING \
    control=$CONTROL_FILE \
    log=$LOG_FILE \
    bad=$BAD_FILE \
    discard=$DISCARD_FILE

# Check the status of the SQL*Loader execution
if [ $? -eq 0 ]; then
  echo "Data loaded successfully."
else
  echo "Error occurred during data loading. Check the log file: $LOG_FILE"
  exit 2
fi

• This script will prompt for the username, service name and password.  We can add the directory used if required as well for the SQL Loader work directory.  Depending on how the connection to Oracle is configured in the database environment, different connection string information may need to be included.  In the example shown, a complex example is being used, that expects the host, port and service name.
• The script checks for the existence of the data load file and logs errors if any occur.

Execute the Shell Script Outside of Flyway

1. Make the script executable(demonstrated is Linux commands).  This example is assuming that Flyway was used to add the scripts to a Flyway project and using the naming convention that would be part of the scripts, as explained at a high level in the next section:

chmod +x V3__load_sal_data.sh

       2.  Run the script:

./V3__load_sal_data.sh

Executing a Shell Script with SQL Loader Within Flyway

Flyway allows for a self-service option to include shell scripts as part of deployments that include SQL*Loader data loads.  Now that we’ve demonstrated how to execute the shell script from the command line by itself, we now can demonstrate how to do this as part of a Flyway deployment.

In this example we’ll demonstrate an execution of a shell script in two ways. 

V = Versioned

R = Repeatable

Callbacks aren’t logged as part of the schema history table, but are run as part of before and/or after a deployment.

Example in Flyway

The following is a simple example Flyway migration loading various SQL scripts, followed by two SQL*Loader shell scripts.  The naming convention of the first script relates back to our example we’ve used throughout the document and both scripts are highlighted in yellow for easy identification.  The following is a sample set of scripts that are going to be part of our deployment.

The project "my-project" is broken down into three sections:

• resources
• migration
• post steps: sqlloader

Example of what this might look like:

my-project
   src
     main
       resources
         db
   migration   

   classpath:db/migration

             R__My_view.sql
             U1.1__Fix_indexes.sql
             U2__Add_emp_salary.sql
             V1__Initial_version.sql
             V1.1__Fix_indexes.sql
             V2__Add_emp_salary.sql

   sqlloader      

   filesystem:/my-project/sqlloader1
             V3__load_sal_data.sh
             R__load_emp_data.sh

If you’d like to read up more on classpath variables, please see the following documentation.  Locations can include classpath, environment variables, filesystem and other important information the deployment will require.

Aliases can be introduced as placeholders in Flyway deployments, such as exchanging directories for repeat script executions, etc. 

For an advanced deployment option, we can pass in arguments for Flyway to gather information that will be required to locate support files, directories, etc. needed for the deployment scripts to execute successfully.

Example of a Flyway migration execution from the command line:

flyway migrate -locations=classpath:db/migration,filesystem:/my-project/sqlloader1

Verification and Troubleshooting

1. Check the load_data.log file for details about the load process.
2. Query the target table to confirm that the data has been loaded:

SELECT count(*) FROM emp_salary;

Verify that the count on the table is + the number of rows that were loaded.  Check the Bad file to see if rows have been errored, if records were rejected to the discard file.

Troubleshooting

• Bad File (.bad): Contains records that failed to load due to errors.
• Discard File (.dsc): Contains records that did not meet filtering criteria (if specified).
• Control File Errors: Ensure the column mappings match the table definition.
• Permission Issues: Verify the database user has the required permissions.

Example CSV File

The below example is a CSV file that was a pull of data demonstrating the row #, first/last name and salary.  There are three rows displayed from the file.

1,John Doe,50000
2,Jane Smith,60000
3,Bob Johnson,55000

No matter the file type, the format is important to be uniform for success and to match the instructions demonstrated in the control file that will go with the data load file.  By following the steps above, you can effectively use Oracle SQL*Loader to load data into an Oracle database as part of a shell script for support in Flyway.