4

I am trying to write a control file for a sql loader. The source file to which is in fixed length format. I have date and time present in the source file like say , from position 17 to position 24 for date in CCYYMMDD format and from position 25 to 34 in HH:MM:SS24 format. I like to store this date and time value form source file in a date column in some table in 'YYYYMMDD HH24:MI:SS' format.

Can Anybody please help in telling how to achieve this in control file of sql loader?

Would following piece of code do? I doubt since there is no space between the date and time in the source file?

APPEND INTO TABLE target_table
TRAILING NULLCOLS
(
date_column POSITION(17:34) "TO_DATE(:DATE_TIME,'YYYYMMDD HH24:MI:SS')"
)

Any lead would be highly appreciated

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Nitin_Sen
  • 331
  • 3
  • 5
  • 10
  • This should work. Remove the space though, the format should match the data in the file. Try it and see! – Gary_W May 08 '15 at 13:02

1 Answers1

0

Check Oracle's documentation BOUNDFILLER:

...
(date_column BOUNDFILLER POSITION(17:24), 
 time_column BOUNDFILLER POSITION(25:34), 
 date_n_time POSITION(1:1) 
             TO_DATE(:date_column || ' ' || :time_column, 'CCYYMMDD HH24:MI:SS'),
 ...
)

It allows you to define fields that do not correspond to any column.

Trinimon
  • 13,839
  • 9
  • 44
  • 60