SQL Loader 脚本有助于添加 SYSDATE、USER
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3484952/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Loader script help with adding SYSDATE, USER
提问by relyt
I'm trying to load data from a file and I want to set CREATED_DATE and UPDATED_DATE to SYSDATE and CREATE_BY and UPDATED_BY to USER
我正在尝试从文件加载数据，我想将 CREATED_DATE 和 UPDATED_DATE 设置为 SYSDATE，将 CREATE_BY 和 UPDATED_BY 设置为 USER
Here the table that I'm working with:
这是我正在使用的表格：
CREATE TABLE CATALOG
(CNO NUMBER,
CTITLE VARCHAR2(25),
CREATED_BY VARCHAR2(10) NOT NULL,
CREATED_DATE DATE NOT NULL,
UPDATED_BY VARCHAR2(10) NOT NULL,
UPDATED_DATE DATE NOT NULL,
CONSTRAINT CATALOG_PK Primary Key (CNO));
and here is the data file:
这是数据文件：
1,"Title 1"
2,"Title 2"
3,"Title 3"
4,"Title 4"
5,"Title 5"
6,"Title 6"
and my control file:
和我的控制文件：
LOAD DATA
INFILE "mydata.csv"
INTO TABLE CATALOG
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(CNO,CTITLE,CREATED_BY "USER", CREATED_DATE "SYSDATE", UPDATED_BY "USER", UPDATED_DATE "SYSDATE")
When I try to load it with SQL Loader...all records are rejected:
当我尝试使用 SQL Loader 加载它时...所有记录都被拒绝：
Record 1: Rejected - Error on table CATALOG, column CREATED_BY.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 2: Rejected - Error on table CATALOG, column CREATED_BY.
Column not found before end of logical record (use TRAILING NULLCOLS)
.......
Any ideas what I'm doing wrong? Thanks in advance.
任何想法我做错了什么？提前致谢。
回答by Codo
Just do what Oracle says: add TRAILING NULLCOLSto your control file:
只需按照 Oracle 所说的去做：添加TRAILING NULLCOLS到您的控制文件中：
LOAD DATA
INFILE "mydata.csv"
INTO TABLE CATALOG
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(CNO,CTITLE,CREATED_BY "USER", CREATED_DATE "SYSDATE", UPDATED_BY "USER", UPDATED_DATE "SYSDATE")
This is required because only the first two columns are in the input file. The remaing ones are purely computed.
这是必需的，因为只有前两列在输入文件中。其余的纯粹是计算出来的。
If you don't specify it, Oracle tries to read a third, fourth etc. column, but can't find one.
如果您不指定它，Oracle 会尝试读取第三、第四等列，但找不到。
回答by Mamtha
In control file try this
在控制文件中试试这个
(CNO,CTITLE,CREATED_BY "USER", CREATED_DATE SYSDATE, UPDATED_BY "USER", UPDATED_DATE SYSDATE)
