Oracle Database Discussions

Hi,

I'm struggling with a problem when importing a time duration from a CSV file into an interval day to second data type column. The duration in the CSV file is given in the format "HH24:MI:SS", e.g. 00:02:31. I tried the following option in my CTL file:

(1) DURATION interval
gives error "ORA-01867: invalid interval" because the day part is missing
If I modify the format of the CSV file to be "0 00:02:31" it would work

(2) DURATION char "to_dsinterval('0 ' || :DURATION)"
Record 1: rejected - error in tabelle TMP_TEST.
ORA-00917: comma missing

The workaround to option (1) is not a solution because the data comes from a telecomunication system and every day we have to import many files with lots of data.

I provide some test data below, if somebody is willing to try to play with SQLLoader:

Thanks,
Stefan


============== TMP_TEST.SQL ==============
drop table TMP_TEST;
create table TMP_TEST (
LOAD_SEQ number(10) not null,
DURATION interval day to second not null
);

============== TMP_TEST.CTL ==============
LOAD DATA
TRUNCATE
INTO TABLE TMP_TEST
FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( DURATION char "to_dsinterval('0 ' || :DURATION)"
, LOAD_SEQ SEQUENCE(MAX,1)
)

============== TMP_TEST.CMD ==============
@echo off
if exist TMP_TEST.LOG del TMP_TEST.LOG
if exist TMP_TEST.BAD del TMP_TEST.BAD
SQLLDR CONTROL=TMP_TEST.ctl DATA=TMP_TEST.csv LOG=TMP_TEST.log BAD=TMP_TEST.bad

============== TMP_TEST.CSV ==============
00:10:53
00:05:45
00:04:24
00:13:52
00:00:48
00:00:22
00:10:14
00:01:05
00:02:35
00:00:07