Transforming SQL*Plus Output into HTML and Emailing It: A Step-by-Step Guide

Ever wondered how to turn your SQL*Plus query results into a snazzy HTML file and then shoot it off as an email? Well, wonder no more!  I'll walk you through the magical journey of converting database outputs into eye-catching HTML tables and sending them straight to your inbox . 

Step 1: Crafting Your SQL Query

First things first, you need a SQL script. This script contains the query you want to run. Let’s save this SQL script as dd.sql in your /tmp/oracle/script/ directory. Here’s an example query to get you started:

set pages 9999 lines 300
col OWNER for a15
col HOST_NAME for a35
select name DB_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE ,DATABASE_STATUS,DATABASE_ROLE, to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

Step 2: The Magical Shell Script

Next up, we’ll write a shell script that will:

• Run the SQL query.
• Format the output as HTML.
• Save the output to a file.
• Email the file.

Here’s the script, let’s call it send_db_info.sh:

#!/bin/bash

# Abracadabra! Running SQL*Plus with HTML formatting and saving the output to dd.html
sqlplus -S -M "HTML ON TABLE 'BORDER=\"2\"'" '/ as sysdba' @/tmp/oracle/script/db_info.sql > /tmp/oracle/scripts/info_op.html

# Hocus pocus! Emailing the HTML file
echo "Behold the Database Information Report!" | mailx -s "Database Size Report" -a /tmp/oracle/scripts/info_op.html mail_id@domain.com

Step 3: Making It Executable

Before we can summon the script into action, we need to make it executable. Open your terminal and run:

chmod +x /home/oracle/DBA/send_db_size.sh

This command bestows executable powers upon your script. 🎉

Step 4: Running the Script

Now, it’s showtime! Execute the script with:

/tmp/oracle/scripts/db_info.sh

Behind the Curtain: How It Works

Let’s demystify what’s happening behind the scenes:

-S: Starts SQL*Plus in silent mode, because no one likes unnecessary chatter.

-M "HTML ON TABLE 'BORDER=\"2\"'": Sets the markup for HTML with a table border of 2. Fancy, right?

'/ as sysdba': Connects to the database as SYSDBA. This is where the magic begins.

@: Executes your SQL script.

> /tmp/oracle/scripts/db_info.html: Redirects the output to dd.html, transforming it into a dazzling HTML file.

-s "Database Info Report": Sets the subject of the email.

-a  Attaches the HTML file to the email.

The Final Act

And there you have it! You’ve just turned a mundane SQL output into a stylish HTML email. Now, you can impress your colleagues, keep stakeholders in the loop, or just bask in the glory of your scripting prowess. So go ahead, run that script, and watch your email light up with data-driven beauty. 

Happy scripting! 🌟