# there are many methods to do this.
# this is the one i use in my production
export ORACLE_SID=yoursid
export MAILLIST="someemailid"
export MAILLIST_F="somepagerno" #like 1234@skytel.com
export SERVER=`uname -n`
$ORACLE_HOME/bin/sqlplus <<EOF

#  
#include your sql statement or procedure or anything
#Make sure create a  file in which an entry or any errors are written. 
#let the logfile be /u01/app/oracle/product/log
exit
EOF
#
# here i am intrested in a particular error message "error message stack follows"
# this error message is related to rman backups. YOU can CHOOSE ANY MESSAGE you want
# if the error i am looking for is located in the logfile i create a new file alert
cd /u01/app/oracle/product/log
grep "error message stack follows" log > alert
#
# so, now if there is some entry in the file alert, it means, something is wrong. else everything is cool.
# according to your need, you can handle this
# in this example , if everything is OK a regular email is sent to me.
# if there are any errors a the message is paged to me and an email is sent
# here i am doing in two steps ( emails and pager lists are seperate).
# Mine is a different case ( a little complex notification standards we follow)
# you can simplify the following into single step ( one if-then-else, insttead of two if statements)
if test -s alert
then
mailx -s "There is a problem with RMAN backup of the database on $SERVER" $MAILLIST <<EOF
Please Check RMAN backup log in the directory 'xxxxxxxx' for further information.
EOF
#
else
mailx -s "RMAN backup of the database on $SERVER is succesfully completed" $MAILLIST <<EOF
NONE
EOF
fi
##to send the pager
if test -s alert
then
mailx -s "There is a problem with RMAN backup of the database on $SERVER" $MAILLIST_F <<EOF
Please Check RMAN backup log in the directory 'xxxxxxxxxxx' for further information.
EOF
fi

#and ofcourse, schedule this script as cron job
#to do a regular sweep

----------------------------------------------------------------------

or to do within oracle

----------------------------------------------------------------------

create a stored procedure to do the job.
schedule that stored procedure to be executed in regular intervals using DBMS_JOB.
grab the messages ( you can use UTL_FILE to create logfiles )and send emails from oracle using 
UTL_SMTP. As a pre-requisite, you need to configure Oracle JVM for this by running initjvm.sql