sample invalid script

#!/bin/bash

Set the variables

sql_query="set pagesize 0 set linesize 200 set feedback off column owner format A9; column object_name format A31; column object_type format A15; column last_ddl_time format A10; spool EBS_DB_INVALIDS.lst; select owner,object_type,COUNT(*) from dba_objects where status='INVALID' group by owner, object_type; select OWNER, OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME from dba_objects where status='INVALID' order by owner; spool off; exit;"

email_subject="EBS INVALID Objects" email_body="The following is a list of invalid objects in the EBS database. Please contact me if you have any questions."

Source the Oracle application environment

source /path/to/oracle/apps/env.sh

Run the SQL query and save the output to a file

echo "$sql_query" | sqlplus -S apps/apps > EBS_DB_INVALIDS.lst

Convert the output file to HTML format

awk -F'|' '{print "<tr><td>"$1"</td><td>"$2"</td><td>"$3"</td><td>"$4"</td></tr>"}' EBS_DB_INVALIDS.lst > EBS_DB_INVALIDS.html

Send the HTML content in the email body as MIME format

echo "$email_body" | mailx -s "$email_subject" your_email_address