Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Loader script help with adding SYSDATE, USER

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.

like image 440
relyt Avatar asked Aug 14 '10 20:08

relyt


People also ask

What is append in SQL Loader?

The APPEND parameter is one of the options you can use when loading data into a table that is not empty. See Loading Data into Nonempty Tables. To load data into a table that is empty, you would use the INSERT parameter. See Loading Data into Empty Tables.

What is SQL Loader used for?

SQL*Loader loads data from external files into tables of an Oracle database. It has a powerful data parsing engine that puts little limitation on the format of the data in the datafile. You can use SQL*Loader to do the following: Load data across a network.

What is trailing Nullcols in SQL * Loader?

The TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns. See Handling Short Records with Missing Data. The remainder of the control file contains the field list, which provides information about column formats in the table being loaded.


1 Answers

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.

like image 103
Codo Avatar answered Sep 21 '22 16:09

Codo