#!/bin/bash
# Define the target database details
target_db_name="TARGET_DB"
target_db_user="TARGET_USER"
target_db_pass="TARGET_PASS"
# Define the HTML file name and header
html_file="table_validation.html"
html_header="<html><head><title>Table Validation Report</title></head><body>"
html_footer="</body></html>"
# Define the recipient email address
recipient_email="recipient@example.com"
# Initialize the HTML file
echo "$html_header" > "$html_file"
# Validate tables in the target database
tables_to_validate=$(sqlplus -s "$target_db_user/$target_db_pass@$target_db_name" <<EOF
set heading off;
set feedback off;
set verify off;
select table_name from user_tables;
exit;
EOF
)
# Write the table validation result to the HTML file
echo "<h2>Table Validation Report</h2>" >> "$html_file"
echo "<table border='1'>" >> "$html_file"
echo "<tr><th>Table Name</th><th>Result</th></tr>" >> "$html_file"
for table in $tables_to_validate; do
validate_result=$(sqlplus -s "$target_db_user/$target_db_pass@$target_db_name" <<EOF
set heading off;
set feedback off;
set verify off;
declare
l_result varchar2(20);
begin
execute immediate 'analyze table $table validate structure';
l_result := 'Success';
exception
when others then
l_result := 'Failed';
end;
/
select l_result from dual;
exit;
EOF
)
echo "<tr><td>$table</td><td>$validate_result</td></tr>" >> "$html_file"
done
echo "</table>" >> "$html_file"
# Add the HTML file footer
echo "$html_footer" >> "$html_file"
# Send the HTML file as an email attachment
mail -s "Table Validation Report" -a "$html_file" "$recipient_email" <<EOF
Please find the attached table validation report.
EOF
No comments:
Post a Comment