I have strange behavior using optionally enclosed by '"' in my .ctl file.
it doesn't works as expected and I do understand why. Could you please help me?
here the .ctl file:
OPTIONS (PARALLEL=TRUE,DIRECT=TRUE,ROWS= '150000',BINDSIZE= '45000000',SKIP= '1')
UNRECOVERABLE
LOAD DATA
INFILE 'C:\Users\test_enclosed_fields.csv'
APPEND
INTO TABLE TEST_ENCLOSED_FIELDS
fields terminated by ';' optionally enclosed by '"'
TRAILING NULLCOLS
(EconomicSectorOrigin,EntityProvider,ApplSnd,ExtractionTS,SecCd,SecCdSrc,SecCdLabel,IssrCd,IssrSrc,IssrLabel)
Here the ddl:
CREATE TABLE "PVT_ECO_SECT_DESC"
( "ECONOMICSECTORORIGIN" VARCHAR2(15 BYTE),
"ENTITYPROVIDER" VARCHAR2(20 BYTE),
"APPLSND" VARCHAR2(10 BYTE),
"EXTRACTIONTS" NUMBER(*,0),
"SECCD" VARCHAR2(50 BYTE),
"SECCDSRC" VARCHAR2(30 BYTE),
"SECCDLABEL" VARCHAR2(150 BYTE),
"ISSRCD" VARCHAR2(20 BYTE),
"ISSRSRC" VARCHAR2(30 BYTE),
"ISSRLABEL" VARCHAR2(100 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT);And here 2 lines with enclosed fields:
LI;BNPPIP;DCC;20170810020335;597673;"Public administration and defence; compulsory soci";O848;"Public administration and defence; compulsory soci";YYY;FFFF;
LI;BNPPIP;DCC;20170810020335;584063;"Public administration and defence; compulsory soci";O145;"Public administration and defence; compulsory soci";DDD;WWW;
the fields which contains the text "Public administration and defence; compulsory soci" are not processed as enclosed fields and they are split into 2 fields by the separator ';' This causes misalignment of the next fields and the data inserted are wrong.
Could you please help me? I not understand what is wrong in my ctl file.
Thanks and regards
I'm not seeing this issue:
C:\Users\csaxon\Documents\Scripts>type test_enclosed_fields.csv
LI;BNPPIP;DCC;20170810020335;597673;"Public administration and defence; compulsory soci";O848;"Public administration and defence; compulsory soci";YYY;FFFF;
LI;BNPPIP;DCC;20170810020335;584063;"Public administration and defence; compulsory soci";O145;"Public administration and defence; compulsory soci";DDD;WWW;
C:\Users\csaxon\Documents\Scripts>sqlldr userid=chris/chris@db control=sqlldr.ctl
SQL*Loader: Release 12.2.0.1.0 - Production on Mon Aug 21 17:11:16 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Path used: Direct
SQL*Loader-281: Warning: ROWS parameter ignored in parallel mode.
Load completed - logical record count 1.
Table PVT_ECO_SECT_DESC:
1 Row successfully loaded.
Check the log file:
sqlldr.log
for more information about the load.
C:\Users\csaxon\Documents\Scripts>sql112
C:\Users\csaxon\Documents\Scripts>sql.exe chris/chris@db
SQLcl: Release 17.2.0 Production on Mon Aug 21 17:11:32 2017
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
SQL> set sqlformat ansiconsole
SQL> set lines 160
SQL> /
ECONOMICSECTORORIGIN ENTITYPROVIDER APPLSND EXTRACTIONTS SECCD SECCDSRC SECCDLABEL ISSRCD ISSRSRC ISSRLABEL
LI BNPPIP DCC 20170810020335 584063 Public administration and defence; compulsory soci O145 Public administration and defence; compulsory soci DDD WWW
SQL>
But then I don't have a copy of SQL*Loader 11.2.0.2 handy. So either you're hitting a bug in that version or something else is going on.
Try upgrading SQL*Loader to the latest version (12.2.0.1). If that still doesn't fix it, show us exactly what you've done that's lead to the problem. i.e. a copy-paste like I've done above.