run sqlplus in shell script

#!/bin/bash

# Define database connection details
DB_USER="your_username"
DB_PASS="your_password"
DB_HOST="your_db_host"
DB_SID="your_db_sid"

# Check if enough parameters are provided
if [ "$#" -lt 2 ]; then
    echo "Usage: $0 <source_table> <sql_file1> [sql_file2]"
    exit 1
fi

SOURCE_TABLE=$1
SQL_FILE1=$2
SQL_FILE2=$3

# Define the target table and column name
TARGET_TABLE="your_target_table"
COLUMN_NAME="your_column_name"
LOAD_DATE_COLUMN="load_date_column_name"  # Adjust if the column name is different in the target table

# Get current timestamp
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# Define the log and error files with timestamps
LOG_FILE="oracle_script_${TIMESTAMP}.log"
ERROR_FILE="oracle_script_error_${TIMESTAMP}.log"

# Function to log messages
log() {
    local message=$1
    echo "$(date +%Y-%m-%d\ %H:%M:%S) : $message" | tee -a "$LOG_FILE"
}

# Function to log errors
log_error() {
    local message=$1
    echo "$(date +%Y-%m-%d\ %H:%M:%S) : $message" | tee -a "$ERROR_FILE" >&2
}

# Function to get initial load_date and column value
get_initial_values() {
    local source_table=$1
    local column_name=$2

    read load_date column_value <<< $(sqlplus -s ${DB_USER}/${DB_PASS}@${DB_HOST}/${DB_SID} <<EOF
    SET HEAD OFF
    SET FEEDBACK OFF
    SELECT TO_CHAR(load_date, 'YYYY-MM-DD'), ${column_name} FROM ${source_table} WHERE ROWNUM = 1;
    EXIT;
EOF
    )
    
    load_date=$(echo $load_date | xargs)
    column_value=$(echo $column_value | xargs)
    
    if [ -z "$load_date" ] || [ -z "$column_value" ]; then
        log_error "Failed to retrieve initial values. Exiting."
        exit 1
    fi
    
    log "Initial load_date: $load_date, $column_name: $column_value"
    export LOAD_DATE=$load_date
    export COLUMN_VALUE=$column_value
}

# Function to run SQL file and log the step
run_sql_file() {
    local sql_file=$1
    local step=$2

    log "Running step $step: $(basename $sql_file)"

    sqlplus -s ${DB_USER}/${DB_PASS}@${DB_HOST}/${DB_SID} <<EOF
    WHENEVER SQLERROR EXIT FAILURE
    SPOOL step_${step}_output.txt
    @${sql_file}
    SPOOL OFF
EOF

    if [ $? -ne 0 ]; then
        log_error "Step $step failed. Exiting."
        exit 1
    fi

    count=$(sqlplus -s ${DB_USER}/${DB_PASS}@${DB_HOST}/${DB_SID} <<EOF
    SET HEADING OFF
    SET FEEDBACK OFF
    SELECT COUNT(*) FROM ${TARGET_TABLE} WHERE TO_CHAR(${LOAD_DATE_COLUMN}, 'YYYY-MM-DD') = '${LOAD_DATE}' AND ${COLUMN_NAME} = '${COLUMN_VALUE}';
    EXIT;
EOF
    )
    
    count=$(echo $count | xargs)
    log "Step $step completed with record count: $count"
    export COUNT=$count
    return 0
}

# Get initial load_date and column value
get_initial_values $SOURCE_TABLE $COLUMN_NAME

# Run the first SQL file
run_sql_file $SQL_FILE1 1
if [ $? -ne 0 ]; then
    log_error "Step 1 failed. Exiting."
    exit 1
fi

# Log the count after the first SQL file
log "After running $SQL_FILE1, record count: $COUNT"
log "Load date: $LOAD_DATE, $COLUMN_NAME: $COLUMN_VALUE"

# Run the second SQL file only if it is provided and the first one succeeded
if [ -n "$SQL_FILE2" ]; then
    run_sql_file $SQL_FILE2 2
    if [ $? -ne 0 ]; then
        log_error "Step 2 failed. Exiting."
        exit 1
    fi
    # Log the count after the second SQL file
    log "After running $SQL_FILE2, record count: $COUNT"
    log "Load date: $LOAD_DATE, $COLUMN_NAME: $COLUMN_VALUE"
fi

# Output completion message
log "Execution complete. Check the log files: ${LOG_FILE} and error file: ${ERROR_FILE}"