I'm running a SQL job from Linux. I'm trying to grab the start time and end time of the job so I'm using the following SQL to grab SYSdate
select to_char(sysdate - 5/24, 'YYYY/MM/DD HH:MI:SS') "Job XXXXXX-28702-names01 started at:" from dual;
My output looks like this in the spool file:
Job XXXXXX-28702-na
-------------------
2024/05/10 07:55:42
The log file grabs the sysdate and time as expected but the job name (in the double quotes) is being truncated to Job XXXXXX-28702-na
What output format am I missing?
I've tried turning on SERVER OUTPUT, setting the LINESIZE and PAGESIZE to 256 to no avail. It always seems to truncate the output to 19 characters in the double quotes.
It's setting the column heading length to the same width as the output. You can explicitly set the column format to the required width:
column "Job XXXXXX-28702-names01 started at:" format a36
Or add 17 extra spaces to the query to make the date format the length you want:
select to_char(sysdate - 5/24, 'YYYY/MM/DD HH24:MI:SS ')
as "Job XXXXXX-28702-names01 started at:"
from dual;
(Notice I changed the format form HH to HH24 - which you almost certainly want...)
Or you could get a single line back:
set heading off
select 'Job XXXXXX-28702-names01 started at:'
|| to_char(sysdate - 5/24, 'YYYY/MM/DD HH24:MI:SS')
from dual;
... and have your shell script split the value after the first colon.