SQL Loader脚本帮助添加SYSDATE,USER [英] SQL Loader script help with adding SYSDATE, USER
问题描述
我正在尝试从文件加载数据,我想将CREATED_DATE和UPDATED_DATE设置为SYSDATE,将CREATE_BY和UPDATED_BY设置为USER
m使用:
CREATE TABLE CATALOG
(CNO NUMBER,
CTITLE VARCHAR2 b $ b 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主键CNO));
这里是数据文件:
1,Title 1
2,Title 2
3,Title 3
4,Title 4
5,Title 5
6,Title 6
载入资料
/ pre>
INFILEmydata.csv
INTO TABLE CATALOG
,OPTIONALLY ENCLOSED BY
(CNO,CTITLE,CREATED_BYUSER,CREATED_DATESYSDATE,UPDATED_BYUSER,UPDATED_DATESYSDATE)
当我尝试使用SQL Loader加载它...所有记录都被拒绝:
记录1:被拒绝 - 表CATALOG的列CREATED_BY出错
在逻辑记录结束之前未找到列(使用TRAILING NULLCOLS)
记录2:被拒绝 - 表上出错CATALOG,CREATED_BY列
在逻辑记录结束之前未找到列(使用TRAILING NULLCOLS)
.......
任何想法我做错了什么?先谢谢。
解决方案只要做Oracle的说:将
TRAILING NULLCOLS
添加到您的控制文件:LOAD DATA
INFILEmydata.csv
INTO TABLE CATALOG
FIELDS TERMINATED BY,OPTIONSALLY ENCLOSED BYTRAILING NULLCOLS
(CNO,CTITLE,CREATED_BYUSER ,CREATED_DATESYSDATE,UPDATED_BYUSER,UPDATED_DATESYSDATE)
只有前两列在输入文件中。
如果你不指定它,Oracle会尝试读取第三,第四等列,但是找不到一个。 / p>
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
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:
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.
解决方案Just do what Oracle says: add
TRAILING NULLCOLS
to your control file: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.
这篇关于SQL Loader脚本帮助添加SYSDATE,USER的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!