SQL Loader V3

Create the Configuration File (config.cfg):Create a file named config.cfg with the following content. Adjust the values to match your environment:[database]

username=your_username

password=your_password

database=your_database


[files]

csv_file=/path/to/your/file.csv

log_file=/path/to/your/log_file.log

inserted_rows_file=/path/to/your/inserted_rows.log

updated_rows_file=/path/to/your/updated_rows.log


**********


#!/bin/bash


# Load configuration

source config.cfg


# Variables

CSV_FILE=$csv_file

USERNAME=$username

PASSWORD=$password

DATABASE=$database

MASTER_TABLE="master_table"

CHILD_TABLE="child_table"

LOG_FILE=$log_file

INSERTED_ROWS_FILE=$inserted_rows_file

UPDATED_ROWS_FILE=$updated_rows_file


# Logging function

log() {

  echo "$(date +'%Y-%m-%d %H:%M:%S') $1" >> $LOG_FILE

}


log "Starting data load process."


# SQL Loader Control File for child table

CONTROL_FILE="control_file.ctl"


# Create control file for child table

cat <<EOF > $CONTROL_FILE

LOAD DATA

INFILE '$CSV_FILE'

INSERT INTO TABLE $CHILD_TABLE

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

(

  support_group,

  install_status,

  oracle_home,

  u_cmdb_ci_used_for,

  u_cmdb_owner_group,

  name,

  correlation_id,

  version,

  last_updated "SYSDATE"

)

EOF


log "Truncating child table."


# Truncate the child table

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

TRUNCATE TABLE $CHILD_TABLE;

EXIT

EOF


if [ $? -eq 0 ]; then

  log "Child table truncated successfully."

else

  log "Failed to truncate the child table."

  exit 1

fi


# Run SQL*Loader for the child table

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

if [ $? -eq 0 ]; then

  log "Data loaded successfully into $CHILD_TABLE."

else

  log "Failed to load data into $CHILD_TABLE."

  exit 1

fi


log "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 (support_group, install_status, oracle_home, u_cmdb_ci_used_for, u_cmdb_owner_group, name, correlation_id, version, last_updated)

SELECT c.support_group, c.install_status, c.oracle_home, c.u_cmdb_ci_used_for, c.u_cmdb_owner_group, c.name, c.correlation_id, c.version, c.last_updated

FROM $CHILD_TABLE c

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

WHERE m.correlation_id IS NULL;


-- Log inserted rows

SPOOL $INSERTED_ROWS_FILE

SELECT c.support_group, c.install_status, c.oracle_home, c.u_cmdb_ci_used_for, c.u_cmdb_owner_group, c.name, c.correlation_id, c.version

FROM $CHILD_TABLE c

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

WHERE m.correlation_id IS NULL;

SPOOL OFF


-- Update modified records in master table

MERGE INTO $MASTER_TABLE m

USING $CHILD_TABLE c

ON (m.correlation_id = c.correlation_id)

WHEN MATCHED THEN

  UPDATE SET m.support_group = c.support_group, m.install_status = c.install_status, m.oracle_home = c.oracle_home, m.u_cmdb_ci_used_for = c.u_cmdb_ci_used_for, m.u_cmdb_owner_group = c.u_cmdb_owner_group, m.name = c.name, m.version = c.version, m.last_updated = c.last_updated

  WHERE m.support_group != c.support_group OR m.install_status != c.install_status OR m.oracle_home != c.oracle_home OR m.u_cmdb_ci_used_for != c.u_cmdb_ci_used_for OR m.u_cmdb_owner_group != c.u_cmdb_owner_group OR m.name != c.name OR m.version != c.version;


-- Log updated rows

SPOOL $UPDATED_ROWS_FILE

SELECT c.support_group, c.install_status, c.oracle_home, c.u_cmdb_ci_used_for, c.u_cmdb_owner_group, c.name, c.correlation_id, c.version

FROM $CHILD_TABLE c

JOIN $MASTER_TABLE m ON c.correlation_id = m.correlation_id

WHERE m.support_group != c.support_group OR m.install_status != c.install_status OR m.oracle_home != c.oracle_home OR m.u_cmdb_ci_used_for != c.u_cmdb_ci_used_for OR m.u_cmdb_owner_group != c.u_cmdb_owner_group OR m.name != c.name OR m.version != c.version;

SPOOL OFF

EOF


# Execute the SQL file using SQL*Plus

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

@$SQL_COMPARE_UPDATE

EXIT

EOF


if [ $? -eq 0 ]; then

  log "Comparison and update executed successfully."

else

  log "Failed to execute comparison and update."

  exit 1

fi


# Cleanup temporary files

rm $CONTROL_FILE

rm $SQL_COMPARE_UPDATE


log "Data load process completed."


ExplanationConfiguration File:Credentials and file paths, including paths for the inserted and updated rows log files, are stored in the configuration file (config.cfg).Logging Function:The log function writes messages with timestamps to the specified log file. This helps track operations and debug issues.Truncate Child Table:The child table is truncated to ensure it contains only fresh data for each load.Load Data into Child Table:The script loads data from the CSV file into the child table using SQL*Loader. The INSERT directive ensures that new data is added.Compare and Update Master Table:The script compares the updated child table with the master table and:Inserts new records from the child table into the master table.Logs inserted rows by comparing the master and child tables and spooling the results to INSERTED_ROWS_FILE.Updates modified records in the master table.Logs modified rows by comparing the master and child tables and spooling the results to UPDATED_ROWS_FILE.Cleanup:The script removes temporary files created during execution.Steps to ExecuteCreate the configuration file (config.cfg) with the correct values.Ensure 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 updated script ensures the child table is truncated before each load, and it logs inserted and updated rows into separate files for easier reference.


Comments

Post a Comment

Popular posts from this blog

Db Unix

-- Insert new records into master table INSERT INTO $master_table (     parent,     parent_sys_class_name,     child_sys_class_name,     child_u_cmdb_ci_used_for,     parent_correlation_id,     type,     child_correlation_id,     child,     modified_date ) SELECT     c.parent,     c.parent_sys_class_name,     c.child_sys_class_name,     c.child_u_cmdb_ci_used_for,     c.parent_correlation_id,     c.type,     c.child_correlation_id,     c.child,     c.modified_date FROM $child_table c LEFT JOIN $master_table m ON c.child_correlation_id = m.child_correlation_id WHERE m.child_correlation_id IS NULL; -- Log inserted rows SPOOL $inserted_rows_file SELECT     c.parent,     c.parent_sys_class_name,     c.child_sys_class_name,     c.child_u_cmdb_ci_used_for,     c.pare...
Read more

Standby redo log add

********* #!/bin/bash # Function to run SQL*Plus commands run_sql() {     sqlplus -s / as sysdba <<EOF     SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF     $1     EXIT EOF } # Get the max group, max thread, and max size from the v$log and v$logfile views redo_info=$(run_sql "SELECT MAX(l.group#), MAX(l.thread#), MAX(l.bytes/1024/1024)  FROM v\$log l, v\$logfile f  WHERE f.group# = l.group#;") # Extract the max group, max thread, and max size values max_group=$(echo "$redo_info" | awk '{print $1}') max_thread=$(echo "$redo_info" | awk '{print $2}') max_size=$(echo "$redo_info" | awk '{print $3}') # Get the number of redo groups per thread groups_per_thread=$(run_sql "SELECT COUNT(DISTINCT group#) FROM v\$log WHERE thread#=1;") # Get the recovery file destination reco_dest=$(run_sql "SHOW PARAMETER db_recovery_file_dest") reco_dest=$(echo "$reco_dest" | awk 'NR==2 {print $3}' |...
Read more