Shell script to poll database and send an alert email

export ORACLE_HOME="/u000/app/oracle/product/112/db2"
export ORACLE_SID=orcl

a=`$ORACLE_HOME/bin/sqlplus -s /nolog << EOF
connect dev_mds/welcome123
set heading off
select count(report) from common_logs
where report='failed'
exit;
EOF`

echo count: $a

if test $a -eq 0
then
echo $a > /home/oracle/mess_count
/bin/mailx -s "ALERT: For BEA-382502 !!! " abc@abc.com < /home/oracle/mess_count

Sending alert message depending upon the JMS Queue count in weblogic server

import os

def sendMail(count,dest):
str3 = 'Queue with the NAME: '+ dest +' , has exceeded the Messages Current Count and now the COUNT is: ' + str(count)
cmd = "echo " + str3 + " > rw_file"
os.system(cmd)
os.system('/bin/mailx -s "ALERT: Messages Current Count Exceeded Limit !!! "abc@abc.com < rw_file')
print '********* ALTERT MAIL HAS BEEN SENT ***********'

connect('weblogic','weblogic1','t3://localhost:7001')
servers = domainRuntimeService.getServerRuntimes();
if (len(servers) > 0):
for server in servers:
jmsRuntime = server.getJMSRuntime();
jmsServers = jmsRuntime.getJMSServers();
for jmsServer in jmsServers:
destinations = jmsServer.getDestinations();
for destination in destinations:
print '.....Queue Count : ' , destination.getMessagesCurrentCount(), destination.getName()
if destination.getMessagesCurrentCount() > 50:
count=destination.getMessagesCurrentCount()
dest = destination.getName()
print '.....Queue Exceeded the Message Current Count : ' , count
print ''
sendMail(count , dest)


This will send an alert message once the queue count is greater than 50

XQuery to change date format

Now i am working in OSB and struggling hard to learn the xquery

My exact requirement was to convert the date from current-date to the following format

i.e if i will talk in terms of exact requirement

MY aim is to convert the date format fn:current-date()

2011-11-18+05:30 yyyy-dd-mm+05:30 to the following format dd/mm/yyyy

So here is the code that i have written from the same.

fn:substring( fn:replace(fn:string(fn:current-date()),'(\d{4})-(\d{2})-(\d{2})','$2/$3/$1'),1,10)

fn:current-date() will fetch me the result 2011-11-18+05:30

First of all i have converted it to string using following function

(fn:string(fn:current-date())

Then i am using the replace functionality of xquery.

It work as follows

i have taken the first four string of the time \d{4}) then i have taken the second two string including -(\d{2})

again we are taking the next two character along with the - as follow \d{2})

These values are by default stored in $1,$2 and $3 variable so i have arranged them as per my requirement.

Now after doing the following replacement i will get my result but it will cotaint some extra string +05:30

we need to remove that hence i have used the substring function which will remove the extra data.