SQL & PL/SQL

I am using Oracle 9.2 on Windows.

I have a logging package for batch jobs that writes out to external files and reads the results back as external tables.

Part of the procedure is below:

procedure abort(p_log# number)
is
l_ts_start timestamp;
l_ts_end timestamp;
l_msg varchar2(4000);
l_elapsed interval day(2) to second(3);
l_seq# number;
begin
/* Update control records */
begin
l_seq# := g_log_seq(p_log#) + 1;
l_ts_start := g_start_time(p_log#);
l_ts_end := systimestamp;
l_msg := g_message(p_log#);
l_elapsed := l_ts_end - l_ts_start;
exception
when no_data_found then
begin
select log_message, status_ts into l_msg, l_ts_start
from ddm.log_status
where log_number = p_log# and log_seq = 0;
select nvl(max(log_seq),0)+1 into l_seq#
from ddm.log_status
where log_number = p_log#;
exception
when no_data_found then
raise_application_error(-20002,'Log number invalid. No entry in status file');
end;
l_ts_end := systimestamp;
l_elapsed := l_ts_end - l_ts_start;
end;

Essentially I use package variables to store the timestamps and other control data, but I just modified the code so that if I lose the package (say the database has been restarted) and I want to manually abort a log entry I read the original log start date (status_ts) from the external table (ddm.log_status). However it is now giving the following error:

ORA-01873: the leading precision of the interval is too small

So I investigated this by running the following sql statement:

select
systimestamp
, status_ts as start_from_ext_table
, systimestamp-status_ts as ext_interval
, to_timestamp('2007-01-03-22.28.32.122','YYYY-MM-DD-HH24.MI.SS.FF3') as start_manual
, systimestamp-to_timestamp('2007-01-03-22.28.32.122','YYYY-MM-DD-HH24.MI.SS.FF3') as manual_interval
from ddm.log_status
where log_number = 5 and log_seq = 0;

And it produces the following results:

SYSTIMESTAMP = 04-JAN-07 11.33.14.690000 AM -06:00

START_FROM_EXT_TABLE = 03-JAN-07 10.28.32.122000 PM
EXT_INTERVAL = +000730487 13:04:42

START_MANUAL = 03-JAN-07 10.28.32.122000000 PM
MANUAL_INTERVAL = +000000000 13:04:42

When it calculates the interval using start_ts from the external table it produces the incorrect result (it adds 730487 days). When I calculate the interval using a to_timestamp function with the same timestamp format as found in my file (2007-01-03-22.28.32.122) with the same format string (YYYY-MM-DD-HH24.MI.SS.FF3) I get the right interval value. Incidentally the access parameter for the start_ts field in the external table is:

status_ts (26:47) char date_format timestamp mask "YYYY-MM-DD-HH24.MI.SS.FF3"

I am totally at a loss as to why I am getting differing results between the ext_interval and manual_interval calculations for the same timestamp.

Any ideas?