Hi friends,
I want to converting a task ,of making few words in my task mail bold and underlined sometimes, automated through sendmail.
I have never used sendmail/mailx before.
What i want is my ouput which looks like below:
+++++++++++++++
DETAILS: sqlid:6mbiosdfsdff parsing_schema_name:
BIND VALUES:
Snap Bind Bind Bind Bind
Time SNAP_ID Name Pos Datatype Value
----------------- -------------- ---------- -----
25-APR-12 326153 :1 1 CHAR 0
326153 :2 2 CHAR 0
326153 :3 3 NUMBER 40000
+++++++++++++++
--My shell script which gives me needed output is (actual script is much more complex with many more columns in first sql statement)
/home/kp456 > cat sql_analysis
#!/usr/bin/ksh
# Usage : ksh sql_analysis 10JUN12 sqlid.lst INST4
export ORACLE_HOME=/home/kp456/oracle/product/10.2.0
export PATH=$ORACLE_HOME/bin:./:${PATH}
export TNS_ADMIN=/home/kp456/tns/
export MY_DIR=/home/kp456/LOGS
export SQLCMD=$ORACLE_HOME/bin/sqlplus
first_variable=$1
second_variable=$2
third_variable=$3
sqlid_list=$second_variable
TEST_TAB=FULL_TABLE_SCAN_${first_variable}
if [[ $third_variable = "INST4" ]] then
CONN=testuser/testpass@INST4
elif [[ $third_variable = "INST5" ]] then
CONN=testuser/testpass@INST5
elif [[ $third_variable = "INST6" ]] then
CONN=testuser/testpass@INST6
elif [[ $third_variable = "INST7" ]] then
CONN=testuser/testpass@INST7
else
echo "wrong instance name"
CONN=""
fi
cat $sqlid_list | while read LINE
do
SQL_ID=`echo $LINE`
$SQLCMD -s $CONN << EOF
set heading off feedback off
select PICK " SQL DETAILS" from
(with t as (select 'sqlid:'|| sql_id ||'parsing_schema_name :' ||parsing_schema_name DETAILS
FROM $TEST_TAB )
where sql_id = '${SQL_ID}')
select 'DETAILS: ' || DETAILS PICK
from t
/
col name for a35
col VALUE_STRING for a25
set heading on feedback off
prompt BIND VALUES:
select vbc.HASH_VALUE,
vbc.CHILD_ADDRESS,
vbc.CHILD_NUMBER,
vbc.NAME,
vbc.POSITION,
vbc.DATATYPE,
vbc.WAS_CAPTURED,
vbc.VALUE_STRING
from
v\$sql_bind_capture vbc
where
vbc.sql_id = '${SQL_ID}'
order by vbc.CHILD_NUMBER,vbc.POSITION
/
quit
EOF
done
exit
--From this forum i have found this script which can be used for "bolding" few words
#!/usr/bin/ksh
export MAILTO="kp456@txt.com"
export SUBJECT="test message"
(
echo "Subject: $SUBJECT"
echo "MIME-Version: 1.0"
echo "Content-Type: text/html"
echo "Content-Disposition: inline"
echo "<HTML><BODY>"
echo "Test to see if <B>bold</B> works."
echo "</BODY></HTML>"
) | /usr/sbin/sendmail $MAILTO
Few more details:
- i have sqlid.lst which will sql ids which will specific to a instance.
- There will be many more columns in my first sql .
Please suggest how i can accomplish this task as the challenges i am facing are:
- It has to be a single which should contain all the details
- how to bold only specific parts of text while leaving remaining as it is in my script output.
Thanks,
Kunwar