Hi All,
I want to insert STAT and ENDTIME values for each job in joblist into TBL_DAILY_STATUS table.
Eg: insert into tbl_daily_status values(STAT,ENDTIME);
Please help me on this.
#!/bin/ksh
joblist="com_abc_job com_abc_dot_job com_abc_seq com_abc_det"
for i in $joblist
do
STAT=`./JobStatus.pl -i NP1 -z Z8PIMBN -p $i|awk '{print $6}'`
ENDTIME=`./JobStatus.pl -i NP1 -z Z8PIMBN -p $i|awk '{print $4" " $5}'`
echo "$STAT"
echo "$ENDTIME"
done
Scott
2
ORACLE_SID=<db name>
ORAENV_ASK=NO
. /usr/local/bin/oraenv
for i in ....
...
sqlplus /nolog << !
connect user/pass
insert into tbl_daily_status values('$STAT','$ENDTIME');
!
...
done
n70arun
3
sqlplus -s user/pwd@DB << EOF
insert into tbl_daily_status values(${STAT},${ENDTIME});
EOF
Hi Scott,
I am getting below error.
"batch.sh" 42 lines, 497 characters
PaBatchd_234>>batch.sh
FA
09/28/2012 03:05:22
SU
09/07/2012 05:56:51
RU
----- -----
RU
----- -----
batch.sh[13]: syntax error at line 14 : `newline or ;' unexpected
PaBatchd_234>>
Scott
5
If ENDTIME is a DATE type, try:
insert into tbl_daily_status values('$STAT', to_date( '$ENDTIME', 'MM/DD/YYYY HH24:MI:SS' ));
In future, please describe the table(s) in question, and show some example data / output. Thanks.
RudiC
6
Will tbl_daily_status reflect WHICH job had that stat and endtime?
Hi Scott,
I am getting below error.
PaBatchd_234>>batch.sh
FA
09/28/2012 03:05:22
SU
09/07/2012 05:56:51
RU
----- -----
RU
----- -----
batch.sh[14]: /usr/local/bin/oraenv: not found
Yoda
8
oraenv
is required when user want to set the environment variables ORACLE_SID
ORACLE_HOME
PATH
and connect to a given database instance.
I these ENV are already set, then you can also connect to your DB using sqlplus
utility by specifying username, password & db instance:-
sqlplus -s user/pass@inst << EOF
insert into tbl_daily_status values('$STAT', to_date( '$ENDTIME', 'MM/DD/YYYY HH24:MI:SS' ));
commit;
exit;
EOF
Scott
9
Your oraenv script is probably just somewhere else (I just cut and paste from a script I have). Maybe it's already in your PATH.
$ which oraenv
If so, just
. oraenv
would do it.