echo "SQLPLUS CONNECTION"
sqlplus -s $ARBOR_USR/$ARBOR_DB_PASSWD@$ORACLE_SID<<EOF>$CUR_DIR/sql_output.txt
set feedback off
set heading off
select distinct account_no from adj WHERE ADJ_TRANS_CODE=-2401 and request_status=1 and bill_ref_no=0
order by account_no;
EOF
if [ $? -eq 0 ]
then
echo " SQLPLUS Connection Successful "
else
echo " SQLPLUS Connection Failed "
fi
##echo " The account numbers passed to BIP are "
if [ ! -s "$CUR_DIR/sql_output.txt" ]
then
echo "No account number for bad debt"
else
for i in `cat $CUR_DIR/sql_output.txt`
do
sqlplus -s $ARBOR_USR/$ARBOR_DB_PASSWD@$ORACLE_SID<<EOF1>$CUR_DIR/sql_interim.txt
insert INTO CMF_INTERIM_BILLS(ACCOUNT_NO,INTERIM_BILL_DATE,INCLUDE_ADJ,PAYMENT_DUE_DATE,TRACKING_ID,TRACKING_ID_SERV )
select $i,trunc(sysdate),1,trunc(sysdate),0,0 from dual;
EOF1
echo "bip $i is running"
sh $SCRIPT_DIR/bip.sh 01 0 $i > $CUR_DIR/bip_log_1.txt
sleep 200
done
fi
I want to insert the account number into CMF_INTERIM_BILLS.TRACKING_ID,TRACKING_ID_SERV columns are composite primary key in the table CMF_INTERIM_BILLS.
The logic behind this is that
Only 1 row is to be inserted into CMF_INTERIM_BILLS table then the bip.sh script runs for a particular account number .When the bip.sh runs the entry for that particular account number
is deleted from CMF_INTERIM_BILLS automatically.
How to implement this logic? Please correct my above code.
The usual solution is to write a script to generate you SQL for the insert. If you insert ... select ... UNION select .... you can do multiple rows at a time. Then you just pipe it to sqlplus.
There are file loaders, too. I forget the name, but Oracle External Tables (mapping files as tables) are very fast and pretty easy, but requires access to the configured dir on the server. JAVA can pass in blocks that can be insert'd, select'd as tables, too.
I have big large snapshot file which contains every process start time and end time. One server snapshot contains many Application handle. My task is to identify the process id for which the end time is not there or empty also if the completion time is not on the same date then I need to kill.
... (6 Replies)
