DB / SQLLoader version: 11.2.0.4
Platform: Oracle Linux 6.5
I have a table and a flat file with data like below
SQL> create table test1 (someDate date, someName varchar2(35), someId number);
Table created.
$ cat /home/flatfile/scrap/mydata.dat
John,8
Krishna,6
Steve,4
$
$
I want the values in the above flat file to be populated to TEST1 . someDate Column needs to be populated using SYSDATE-1 .
Once loaded , the table should look like below .
SQL> select sysdate from dual;
SYSDATE
-----------------
08-FEB-2017 16:34
SQL> select * from test1;
SOMEDATE SOMENAME SOMEID
----------------- ----------------------------------- ----------
07-FEB-2017 16:35 John 8
07-FEB-2017 16:36 Krishna 6
07-FEB-2017 16:36 Steve 4
I tried the below control file. But, it didn't work. Any idea why ?
Although SOMENAME values (John, Krishna, Steve) are field1 in the flat file, it is the second field in the table. So, I need to let SQLLoader know that too.
$ cat loader.ctl
OPTIONS
(
ROWS=10000
)
load data
infile '/home/flatfile/scrap/mydata.dat'
into table test1
fields terminated by ","
(someDate "SYSDATE-1" ,
someName,
someId
)
No records are insert with the below execution. No messages printed either
$ sqlldr scott/tiger@10.7.12.83:1521/ORCL control=loader.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on Wed Feb 8 11:18:43 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
$