将INFILE的timestanp插入到SQLLOADER的列中 [英] insert timestanp of INFILE into a column from SQLLOADER

问题描述

我有以下要求，

通过shell为文件夹中的CSV文件调用sqlldr脚本，文件名也附有时间戳。

我需要将该时间戳插入到表的列中。

例如：
table：
t1（c1 varchar，c2 varchar，c3 timestamp）;

p>

控制文件：
加载数据
infile'file.csv'
将
追加到表t1
字段终止由| TRAILING NULLCOLS
（c1，c2）

csv_file：cat file_csv_101010112233.csv

1111 | 1

2222 | 2

OUTPUT：
select * from t1;

c1 c2 c3

1111 1 101010112233

2222 2 101010112233

注意：我不想要sys时间戳

解决方案

需要一个shell脚本包装调用sqlldr。首先改变控制文件，使时间戳列有一个占位符，如：

  ... 
 C3 CONSTANT REPLACE_ME，
 ... 
  

并保存为模板。

包装器应备份原始csv文件，从文件名获取时间戳，然后使用类似sed的方法将模板控制文件中的REPLACE_ME文本替换为保存的时间戳记数据，并将其保存到工作副本，然后使用工作副本调用Sqlldr。

我在想其他方法来做到这一点，并提出了一个。对您的环境可能不可行，但仍然要考虑的事情。

如果你可以获得数据文件名到一个列（也许load_log表，例如，在加载开始时填充），您可以通过调用返回名称的函数来分配它：

  C3 package.function
  

更多信息： SQL * Loader字段列表参考

I have a requirement as below,

Am calling sqlldr script via shell for the CSV files present in a folder, File name also has Timestamp attached with it.

I need to insert that timestamp into a column of table. Kindly suggest me how i can achieve this.

eg: table: t1(c1 varchar,c2 varchar,c3 timestamp);

control file : load data infile 'file.csv' append into table t1 fields terminated by "|" TRAILING NULLCOLS ( c1, c2)

csv_file : cat file_csv_101010112233.csv

1111|1

2222|2

OUTPUT : select * from t1;

c1 c2 c3

1111 1 101010112233

2222 2 101010112233

Note : I dont want the sys timestamp

解决方案

I think you will need a shell script wrapper around calling sqlldr. First alter the control file so the timestamp column has a placeholder like:

...
C3 CONSTANT REPLACE_ME,
...

And save it as a template.

The wrapper should back up the original csv file, get the timestamp from the filename, then use something like sed to replace the "REPLACE_ME" text in the template control file with saved timestamp data and save it to a working copy, then call Sqlldr using the working copy.

I was thinking of other ways to do this and came up with one. May not be feasible for your environment but something to keep in mind anyway.

If you can get the data file name into a column (maybe a load_log table for example that would get populated at the start of the load), you could assign it like this by calling a function that returns the name:

C3 "package.function"

More info: SQL*Loader Field List Reference

这篇关于将INFILE的timestanp插入到SQLLOADER的列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！