can someone help me on this following errors on my CTL file?

Question

I want to insert the rejected records in a log and to the temp table that i created . but this is my problem on codes , i'm new in creating CTL and Shell Script .

here's my code :

LOAD DATA
INFILE 'open_account .csv'
BADFILE 'open_account.bad'
DISCARDFILE 'open_account.dsc'
APPEND
INTO TABLE accountrequest_temp
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' | TRAILING NULLCOLS
(Branch,
Account_Type,
Title,
FirstName,
Lastname,
Birthday,
WorkPhone,
HomePhone,
Address,
State,
Zip,
Email,);

here's my error :

$ sqlldr hr/password, control=loaddata.ctl

SQL*Loader: Release 11.2.0.2.0 - Production on Tue Aug 17 11:20:06 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-350: Syntax error at line 7.
Illegal combination of non-alphanumeric characters
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' | TRAILING NULLCO

Answer 1

Quite a few errors. To mention some of them:

• don't terminate column list with a semi-colon
  • that's why you got the "Illegal combination ..." error
• remove pipe sign (|) in front of trailing nullcolls
• remove space in infile file name

When fixed, it works. I re-used table accountRrequest (with a typo in its name) we created yesterday, just renamed it. That's why my column list differs from yours a little bit.

Control file:

LOAD DATA
INFILE 'open_account.csv'
BADFILE 'open_account.bad'
DISCARDFILE 'open_account.dsc'
APPEND
INTO TABLE accountrequest_temp
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS
(
requestid,
Branch,
Account_Type,
Title,
FirstName,
Lastname,
Birthday,
WorkPhone,
HomePhone,
Address,
State,
Zip,
Email,
status
)

Sample data (in the open_account.csv file):

1,Brnch, type1,Mr.,Little,Foot,,123456,543123,Ilica 20,Croatia,10000,[email protected],Entered

Loading session:

SQL> $sqlldr scott/tiger@kc11gt control=test39.ctl log=test39.log

SQL*Loader: Release 11.2.0.1.0 - Production on Uto Kol 17 07:18:34 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 1

Result:

SQL> select * from accountrequest_temp;

 REQUESTID BRANCH          ACCOUNT_TYPE    TITL FIRSTNAME       LASTNAME        BIRTHDAY
---------- --------------- --------------- ---- --------------- --------------- -------------------
 WORKPHONE  HOMEPHONE ADDRESS                        STATE                  ZIP
---------- ---------- ------------------------------ --------------- ----------
EMAIL                          STATUS
------------------------------ ----------
         1 Brnch           type1           Mr.  Little          Foot
    123456     543123 Ilica 20                       Croatia              10000
[email protected]                Entered


SQL>