Insert script result into Oracle Table

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
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
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>>

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.

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

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

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.