How to insert date from OS into Oracle table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to insert date from OS into Oracle table

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    How to insert date from OS into Oracle table

    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

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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).

  3. #3
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    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?

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    er, why you dont just try it? it takes 1 minute to try that

  5. #5
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width