Hi All, I'm new to this forum, and appreciate any assistance with my issue.
I have a shell script that logs into an oracle DB and runs a sqlplus query. Everything works great except for the time I get. I'm new to shell so bare with me. What would be the code and where do I place it?
My results are this
myjob1
1.1915E+12
I need it to be seen as myjob1 hh:mm:ss: MM/DD/YY
Here is my code
#!/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
�
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
�
alternatively, you can set the global date format in sqlplus like this:
That almost got it, at least now I'm in epoc time
I didwhat you posted, and it gave me an error, so I removed the 'MM/DD/YY' from the query and that's how I got the epoc time. So almost there, I may be able to find some more info now. unless you guys have a quick code. Then I have to start working on grabbing the newest Time
Now it reads
myjob1
1193191665000
myjob1
1193278063000
myjob1
1193366454000
You must've typed something incorrectly.
Have a look at this Oracle session to understand both techniques:
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>
Gives me this error
ERROR at line 1:
ORA-01481: invalid number format model
When I use the code with date format, Here is my code that is getting errors
#!/usr/bin/sh
sqlplus -S username/password@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
Not sure the last post was handled as it had a link, but here is the cause of the error code from a website
Cause: The user is attempting to either convert a number to a string via TO_CHAR or a string to a number via TO_NUMBER and has supplied an invalid number format model parameter.
The datatype is a TIME stamp (numbers), I'm rereading SQL data formatting now
---------- Post updated at 07:04 PM ---------- Previous update was at 06:56 PM ----------
correct my last post as the data is a time stamp (numeric)
Ok here is what I have found, the column is date/time data, the EPOC time I receive is 1250909923000. I've found that this is was too many numbers, but when you remove the last 3 "0's" in the string, and compute it, it is the exact time that the job ended in our Prod environment, so the question now is how to you remove the last 3 digits cause I believe it will work. Any suggestions ?