# Source the EBS environment
source /u01/install/APPS.env
# Define variables
OUTPUT_DIR="/tmp"
CSV_FILE="${OUTPUT_DIR}/invalid_objects.csv"
EMAIL_SUBJECT="Invalid Objects Report"
EMAIL_TO="youremail@example.com"
AUTHOR_NAME="Abdul Moheet"
# SQL query to find invalid objects and export to CSV
sqlplus -s apps/password <<EOF
SET FEEDBACK OFF
SET HEADING ON
SET PAGESIZE 50000
SET LINESIZE 200
SET COLSEP ","
SPOOL $CSV_FILE
-- Find invalid objects from both tables
SELECT 'dba_objects' AS source_table, object_name, object_type, status
FROM dba_objects
WHERE status != 'VALID'
UNION ALL
SELECT 'ad_objects' AS source_table, object_name, object_type, status
FROM ad_objects
WHERE status != 'VALID';
SPOOL OFF
-- Get counts from both tables
SPOOL ${OUTPUT_DIR}/counts.txt
PROMPT Counts of objects:
SELECT 'DBA_OBJECTS: ' || COUNT(*) FROM dba_objects;
SELECT 'AD_OBJECTS: ' || COUNT(*) FROM ad_objects;
SPOOL OFF
EXIT
EOF
# Create HTML email body
HTML_EMAIL="${OUTPUT_DIR}/email_body.html"
cat <<EOM >$HTML_EMAIL
<html>
<head>
<title>Invalid Objects Report</title>
</head>
<body>
<p>Dear Team,</p>
<p>Please find the attached CSV file containing invalid objects from both DBA and AD objects tables.</p>
<p>Additionally, here is the count of total objects:</p>
<pre>$(cat ${OUTPUT_DIR}/counts.txt)</pre>
<p>Regards,<br/>${AUTHOR_NAME}</p>
</body>
</html>
EOM
# Send email with attachment using uuencode
(cat $HTML_EMAIL; uuencode $CSV_FILE invalid_objects.csv) | mailx -s "$EMAIL_SUBJECT" -a "Content-Type: text/html" $EMAIL_TO
