Shell Programming and Scripting

 
#!/usr/bin/sh

sqlplus -S myusername/mypassword@MYDATABASE <<eof> myfile
       set heading off feedback off verify off
       select JOB.JOBNAME, JOBRUN.ENDTIME from JOB, JOBRUN where JOB.JOBID = JOBRUN.JOBID and JOB.JOBNAME in ('myjob1');
exit
 
EOF
‘

leo@lein:~$ echo 1.1915E+12|bc -l
13.19159
leo@lein:~$

sqlplus -S myusername/mypassword@MYDATABASE <<eof> myfile
set heading off feedback off verify off
select JOB.JOBNAME, to_char( JOBRUN.ENDTIME, 'HH24:MI:SS MM/DD/YY' ) from JOB, JOBRUN where JOB.JOBID = JOBRUN.JOBID and JOB.JOBNAME in ('myjob1');

#!/usr/bin/sh

sqlplus -S myusername/mypassword@MYDATABASE <<eof> myfile
       set heading off feedback off verify off
       select JOB.JOBNAME, TO_CHAR(JOBRUN.ENDTIME, 'HH24:MI:SS MM/DD/YY') from JOB, JOBRUN where JOB.JOBID = JOBRUN.JOBID and JOB.JOBNAME in ('myjob1');
exit
 
EOF
‘

alter session set NLS_DATE_FORMAT='<my_format>';

test@XE> 
test@XE> create table t (x number, y date);

Table created.

test@XE> 
test@XE> insert into t (x,y)
  2  select 1, to_date('12/31/2008 13:23:47','mm/dd/yyyy hh24:mi:ss') from dual union all
  3  select 2, to_date('2/4/2009 9:19:34','mm/dd/yyyy hh24:mi:ss') from dual union all
  4  select 3, to_date('5/24/2009 19:23:58','mm/dd/yyyy hh24:mi:ss') from dual;

3 rows created.

test@XE> commit;

Commit complete.

test@XE> 
test@XE> -- (1) using to_char function
test@XE> select y, to_char(y,'hh24:mi:ss mm/dd/yy') y_fmt from t;

Y           Y_FMT
------------------ -----------------
31-DEC-08       13:23:47 12/31/08
04-FEB-09       09:19:34 02/04/09
24-MAY-09       19:23:58 05/24/09

test@XE> 
test@XE> -- (2) using nls_date_format
test@XE> alter session set nls_date_format = 'hh24:mi:ss mm/dd/yy';

Session altered.

test@XE> 
test@XE> -- now you don't have to put that to_char around the date column
test@XE> select y from t;

Y
-----------------
13:23:47 12/31/08
09:19:34 02/04/09
19:23:58 05/24/09

test@XE> 
test@XE>