Ad applied patches

 #!/bin/bash

# Source the environment file to load environment variables

source /opt/mis/SID/EBSapps.env RUN

# Database connection details

DB_USER="your_db_user"

DB_PASSWORD="your_db_password"

DB_HOST="your_db_host"

DB_PORT="your_db_port"

DB_SERVICE="your_db_service"

# Email details

RECIPIENT="recipient@example.com"

SENDER="sender@example.com"

EMAIL_SUBJECT="Query Results"

# SQL queries

QUERIES=(

    "set pagesize 200;

    set linesize 160;

    column adop_session_id format 999999999999;

    column bug_number format a15;

    column status format a15;

    column applied_file_system_base format a23;

    column patch_file_system_base format a23;

    column adpatch_options format a15;

    column node_name format a15;

    column end_date format a15;

    column clone_status format a15;

    SELECT ADOP_SESSION_ID, BUG_NUMBER, STATUS, APPLIED_FILE_SYSTEM_BASE, PATCH_FILE_SYSTEM_BASE, ADPATCH_OPTIONS, NODE_NAME, END_DATE, CLONE_STATUS

    FROM ad_adop_session_patches

    ORDER BY end_date DESC;"

    "SELECT * FROM ad_adop_session_patches

    ORDER BY end_date DESC;"

    "SELECT * FROM ad_adop_session_patches

    ORDER BY end_date DESC;"

)

# Execute the SQL queries and store results in HTML format

OUTPUT_FILES=()

for ((i = 0; i < ${#QUERIES[@]}; i++)); do

    QUERY="${QUERIES[i]}"

    OUTPUT_FILE=$(date +%Y%m%d%H%M%S)_query$i.html

    OUTPUT_FILES+=("$OUTPUT_FILE")

    sqlplus -S "$DB_USER/$DB_PASSWORD@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$DB_HOST)(PORT=$DB_PORT))(CONNECT_DATA=(SERVICE_NAME=$DB_SERVICE)))" << EOF

    $QUERY

EOF

done

# Prepare the email body with results of each query

EMAIL_BODY=""

for ((i = 0; i < ${#OUTPUT_FILES[@]}; i++)); do

    OUTPUT_FILE="${OUTPUT_FILES[i]}"

    HTML_BODY=$(cat "$OUTPUT_FILE")

    EMAIL_BODY+="\n\nQuery $((i+1)) Results:\n\n$HTML_BODY"

done

# Send email using mailx

echo -e "$EMAIL_BODY" | mailx -s "$EMAIL_SUBJECT" -r "$SENDER" "$RECIPIENT"

echo "Email sent successfully."

# Clean up the temporary HTML files

for OUTPUT_FILE in "${OUTPUT_FILES[@]}"; do

    if [ -f "$OUTPUT_FILE" ]; then

        rm "$OUTPUT_FILE"

    fi

done