How to insert date from OS into Oracle table

[nir_s]

Hi guys,

I'm writing this days a shell script wich issues the following:

1. sqlplus - insert into log table the step process and its start run dade
2. exit from sqlplus
3. running sql loader
4. check status of sql loader's running
5. sqlplus - update the end date run of the row from step 1.

Due to the fact i suppose to update the row which i've inserted in step 1 according to the start run date , i wanted to ask you how to do it?
How can i keep the start run date for the later updating?

I thought maybe keeping the OS date in parameter and use it for the insert and for the later update. Is it possible to use OS date parameter in sqlplus?

Thanks in advance,

Nir

[DaPi]

The function SYSDATE gives local time & date derived from the OS time on the server - is that what you want? If so TRUNC(date_field) will allow comparisons on the date only (ignoring the time part).

[nir_s]

Hi DaPi,

No. I didn't mean to SYSDATE.

I meant to the "date" in UNIX (HP-UX).
Due to the fact i get out from sqlplus after the first insert to the log table , i don't have a way to keep the sysdate of the start_run.
So, i thought about the option to keep the "date" of the OS in parameter and using it both to the insert ("start_run" column) and update ("end_run" column) according to "date" parameter.

The script supoose to be as the following:
!# /usr/csh
set date=`date + %D`

sqlplus user/pwd << EOF
insert into log_tab
values('step 1',to_date($date,'dd/mm/yy'),null);
commit;
EOF

sqlldr ......
if ( $status ==1 || $status == 3)
then ....

sqlplus user/pwd < update log_tab
set end_run=sysdate
where step_name='step 1' and start_run=to_date($date,'dd/mm/yy');
commit;
EOF

Is it OK to do this?

[pando]

er, why you dont just try it? it takes 1 minute to try that

[nir_s]

It works!

#! /bin/csh
set mydate = `date +%D`" "`date +%T`
sqlplus scott/tiger << EOF
insert into toved_log
values('sqlldr_boved',to_date('$mydate','mm/dd/yy hh24:mi:ss'),null,null,null);
commit;
EOF

Nir