SQL Loader V2

#!/bin/bash

# Variables

CSV_FILE="/path/to/your/file.csv"

USERNAME="your_username"

PASSWORD="your_password"

DATABASE="your_database"

MASTER_TABLE="master_table"

CHILD_TABLE="child_table"

TEMP_CHILD_TABLE="temp_child_table"

# Create a temporary table to load the new CSV data

TEMP_CREATE_SQL="create_temp_table.sql"

cat <<EOF > $TEMP_CREATE_SQL

CREATE TABLE $TEMP_CHILD_TABLE AS SELECT * FROM $CHILD_TABLE WHERE 1=0;

EOF

# Run SQL*Plus to create the temporary table

sqlplus -s $USERNAME/$PASSWORD@$DATABASE <<EOF

@$TEMP_CREATE_SQL

EXIT

EOF

# Create control file for temporary child table

CONTROL_FILE="control_file.ctl"

cat <<EOF > $CONTROL_FILE

LOAD DATA

INFILE '$CSV_FILE'

INTO TABLE $TEMP_CHILD_TABLE

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

(

  id,

  name,

  age,

  last_updated "SYSDATE"

)

EOF

# Run SQL*Loader for the temporary child table

sqlldr $USERNAME/$PASSWORD@$DATABASE CONTROL=$CONTROL_FILE LOG=temp_loader.log BAD=temp_loader.bad

# Check if sqlldr command was successful

if [ $? -eq 0 ]; then

  echo "Data loaded successfully into $TEMP_CHILD_TABLE."

else

  echo "Failed to load data into $TEMP_CHILD_TABLE."

  exit 1

fi

# SQL File to update the child table with new values and log updates

SQL_UPDATE_CHILD="update_child_table.sql"

# Create SQL file to update the child table

cat <<EOF > $SQL_UPDATE_CHILD

-- Update existing records and insert new records into child table

MERGE INTO $CHILD_TABLE c

USING $TEMP_CHILD_TABLE t

ON (c.id = t.id)

WHEN MATCHED THEN

  UPDATE SET c.name = t.name, c.age = t.age, c.last_updated = t.last_updated

  WHERE c.name != t.name OR c.age != t.age OR c.last_updated != t.last_updated

WHEN NOT MATCHED THEN

  INSERT (id, name, age, last_updated)

  VALUES (t.id, t.name, t.age, t.last_updated);

-- Select and notify updated rows in child table

SELECT t.id, t.name, t.age

FROM $TEMP_CHILD_TABLE t

JOIN $CHILD_TABLE c ON t.id = c.id

WHERE c.name != t.name OR c.age != t.age OR c.last_updated != t.last_updated;

EOF

# Execute the SQL file using SQL*Plus

sqlplus -s $USERNAME/$PASSWORD@$DATABASE <<EOF

@$SQL_UPDATE_CHILD

EXIT

EOF

# Check if SQL*Plus command was successful

if [ $? -eq 0 ]; then

  echo "Child table updated successfully with new values."

else

  echo "Failed to update the child table."

  exit 1

fi

echo "Comparing and updating master table."

# SQL File to Compare and Update Records in the master table

SQL_COMPARE_UPDATE="compare_update_master.sql"

# Create SQL file to compare and update records in the master table

cat <<EOF > $SQL_COMPARE_UPDATE

-- Insert new records into master table

INSERT INTO $MASTER_TABLE (id, name, age, last_updated)

SELECT c.id, c.name, c.age, c.last_updated

FROM $CHILD_TABLE c

LEFT JOIN $MASTER_TABLE m ON c.id = m.id

WHERE m.id IS NULL;

EOF

# Execute the SQL file using SQL*Plus

sqlplus -s $USERNAME/$PASSWORD@$DATABASE <<EOF

@$SQL_COMPARE_UPDATE

EXIT

EOF

# Check if SQL*Plus command was successful

if [ $? -eq 0 ]; then

  echo "Comparison and update executed successfully."

else

  echo "Failed to execute comparison and update."

  exit 1

fi

# Cleanup temporary files

rm $CONTROL_FILE

rm $SQL_UPDATE_CHILD

rm $SQL_COMPARE_UPDATE

# Drop the temporary table

sqlplus -s $USERNAME/$PASSWORD@$DATABASE <<EOF

DROP TABLE $TEMP_CHILD_TABLE;

EXIT

EOF

# Remove the temporary SQL script

rm $TEMP_CREATE_SQL

ExplanationTemporary Table Creation:A temporary table (temp_child_table) is created to hold the new CSV data.Load Data into Temporary Table:Data from the CSV file is loaded into the temporary table using SQL*Loader.Update Child Table with New Values and Notify:The script merges the data from the temporary table into the child table:Updates existing records where values have changed.Inserts new records if they don't already exist in the child table.Selects and notifies about updated rows in the child table.Compare and Update Master Table:The script then compares the updated child table with the master table and inserts new records from the child table into the master table.Cleanup:The script removes temporary files and drops the temporary table created during execution.Steps to ExecuteEnsure the master and child tables are created in your Oracle database.Save the script to a file, for example load_and_update.sh.Make the script executable:chmod +x load_and_update.shRun the script:./load_and_update.shThis script ensures that any existing records in the child table are updated with new values from the CSV file before comparing and updating the master table. It handles both inserts and updates in a single, consolidated script. Adjust the column definitions in the control files and SQL scripts to match your actual table structures and CSV file format.