通过SQL LOADER存储日期和时间值

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.

我正在尝试为sql加载器编写一个控制文件。源文件采用固定长度格式。我在源文件中有日期和时间,例如,从CC17YMMDD格式的第17位到第24位,以及HH:MM:SS24格式的第25到34位。我喜欢将此日期和时间值表格源文件存储在某些表格中的日期列中,格式为“YYYYMMDD HH24:MI:SS”。

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

任何人都可以帮助告诉如何在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

任何领导都将受到高度赞赏

1 个解决方案

#1

Check Oracle's documentation BOUNDFILLER:

查看Oracle的文档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.

它允许您定义与任何列不对应的字段。

#1