I have an input file at ./$1.txt
with content of seq numbers like :
1234567890
1234589700
.
.
so on..
I need to take each seq nbr from the input file ,run the query below:
select ackname,seqnbr from event where event_text like '%seqnbr( from the input file)'
and redirect the query output to a file :log.txt
this has to be done for all seq nbr's in the input file.
Can anyone please help me out in this:-( :wall: :wall:
xargs printf "select ackname,seqnbr from event where event_text like '%%%s'\n" <$1.txt
I have tried it and it resulted an error:
SP2-0734: unknown command beginning "xargs prin..." - rest of line ignored.
code snippet I have used
#!/bin/ksh
db_user=`echo $DB_USER_NAME`
db_pwd=`echo $DB_PASSWORD`
db_sid=`echo $TWO_TASK`
if [ $# -ne 1 ]; then
echo "\tUsage: MoveUsageProcessing <BC Log file Name>"
exit 1
else
BCLogFileName=$1
fi
grep -i 'MoveUsage daemon needs to run on this account before it can be billed' $1 |awk -F\| '{for(i=0;++i<=NF;) if($i ~ /Acct/) print substr($i,6)}' > ./$1.txt
echo " log file path is : './$1.txt' ";
a=`sqlplus -s $db_user/$db_pwd@$db_sid << EOF
xargs printf "select ACK_PARTY_NAME,bus_event_seq_nbr from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and event_data_text like'%%%s'\n" <$1.txt
EOF`
xargs and printf are shell commands, not SQL commands.
What are the changes to be made to make it work:-/
You need something like this (untested):
xargs printf "select ackname,seqnbr from event where event_text like '%%%s';\n" <$1.txt |
sqlplus -s $db_user/$db_pwd@$db_sid
This assumes, that your SQLTERMINATOR is ';'.
try this: (not tested)
I have executed the code :
#!/bin/ksh
db_user=$DB_USER_NAME
db_pwd=$DB_PASSWORD
db_sid=$TWO_TASK
if [ $# -ne 1 ]; then
echo "\tUsage: MoveUsageProcessing <BC Log file Name>"
exit 1
else
BCLogFileName=$1
fi
grep -i 'MoveUsage daemon needs to run on this account before it can be billed' $1 |awk -F\| '{for(i=0;++i<=NF;) if($i ~ /Acct/) print substr($i,6)}' > ./$1.txt
echo " log file path is : './$1.txt' ";
sqlplus -s /nolog |&
print -p "connect $db_user/$db_pwd@$db_sid"
print -p "SPOOL output.txt"
while read var_ack_party_name
do
print -p "select ACK_PARTY_NAME,bus_event_seq_nbr from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and event_data_text like '%"$var_ack_party_name"' "
done
But it didnt worked..and also it is not coming out of loop...an empty file output.txt is created.
You try to read from stdin instead of the file, use
while read var_ack_party_name
do
print -p "select ACK_PARTY_NAME,bus_event_seq_nbr from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and event_data_text like '%"$var_ack_party_name"' "
done <$1.txt
[/COLOR][/COLOR]
I have tried this but this also didnt gave me expected result:
#!/bin/ksh
db_user=$DB_USER_NAME
db_pwd=$DB_PASSWORD
db_sid=$TWO_TASK
if [ $# -ne 1 ]; then
echo "\tUsage: MoveUsageProcessing <BC Log file Name>"
exit 1
else
BCLogFileName=$1
fi
grep -i 'MoveUsage daemon needs to run on this account before it can be billed' $1 |awk -F\| '{for(i=0;++i<=NF;) if($i ~ /Acct/) print substr($i,6)}' > ./$1.txt
echo " log file path is : './$1.txt' ";
sqlplus -s /nolog |&
print -p "connect $db_user/$db_pwd@$db_sid"
print -p "SPOOL output.txt"
while read var_ack_party_name
do
print -p "select ACK_PARTY_NAME,bus_event_seq_nbr from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and event_data_text like '%"$var_ack_party_name"' "
done <$1.txt
I ran this and the output is as below :newtrial.ksh 20111011.71BC.LOG
log file path is : './20111011.71BC.LOG.txt'
No file is created.
I am not so familiar with sqlplus, but shouldn't every single SQL command end with a terminator like ';'?
...
sqlplus -s /nolog |&
print -p "connect $db_user/$db_pwd@$db_sid;"
print -p "SPOOL output.txt;"
while read var_ack_party_name
do
print -p "select ACK_PARTY_NAME,bus_event_seq_nbr from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and event_data_text like '%"$var_ack_party_name"'; "
done <$1.txt
It worked Thankyou:-)