Sample script

 #!/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 query

QUERY=$(cat <<EOF

SET MARKUP HTML ON SPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SET TERMOUT OFF

COLUMN owner FORMAT a15

COLUMN object_name FORMAT a30

COLUMN object_type FORMAT a20

COLUMN status FORMAT a10

SELECT owner, object_name, object_type, status

FROM dba_objects

WHERE status = 'INVALID'

AND object_name NOT IN (SELECT object_name FROM invalid_1213 WHERE status = 'INVALID');

SPOOL OFF

SET MARKUP HTML OFF SPOOL OFF

SET ECHO ON

SET FEEDBACK ON

SET TERMOUT ON

EOF

)

# Generate unique file name

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

# Execute the SQL query and store results in HTML format

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

if [ -s "$OUTPUT_FILE" ]; then

  # Read the HTML output from the file

  HTML_BODY=$(cat "$OUTPUT_FILE")

  # Send email using mailx

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

  echo "Email sent successfully."

else

  echo "No results found."

fi

# Clean up the temporary HTML file

if [ -f "$OUTPUT_FILE" ]; then

  rm "$OUTPUT_FILE"

fi