Hello people I am having an issue while geting an extract from SQL Server and Loading it into Oracle 11g with SQL Loader.
I assume the data is extracted from some tool in Windows env.
Data contains Greek characters, and I can see in Notepad++ CR LF characters at the end of each line
Here is 1 scrambled record data.txt:
9758294063|1-1006cr|M161125|123456789|Residential|Residential|59|KOL Triple Play UK +555|143|COL Triple Play|Residential|7|7|M|1900-01-01 00:00:00|ΙΔΙΩΤΗΣ|High value|Orange|ΣΑΜ ΓΡΙΚ ΚΑΡ 5|ΤΟΡΠΙΛ|45333|ΣΠΥΡΟΥ 5|ΤΟΡΠΙΛ|ΤΟΡΠΙΛΕΣΚΟΥ|46444|2017-02-10 00:00:00|1|1700769|20,095550|001|2017-04-01 00:00:00
My table:
CREATE TABLE PEO.DATA
(
CLI VARCHAR2(60 CHAR),
CUSTOMERID VARCHAR2(25 CHAR) NOT NULL,
CUSTOMERCODE VARCHAR2(50 CHAR),
AFM VARCHAR2(50 CHAR),
CUSTOMERSEGMENT VARCHAR2(23 CHAR),
DIMPRODUCTSEGMENT VARCHAR2(23 CHAR),
DIMPRODUCTCLASSIFICATIONSK VARCHAR2(5 CHAR) NOT NULL,
PRODUCTCLASSIFICATION VARCHAR2(100 CHAR),
DIMPRODUCTSK VARCHAR2(7 CHAR),
PRODUCTNAME VARCHAR2(100 CHAR),
CUSTOMERTYPE VARCHAR2(23 CHAR),
CUSTOMERTENURE VARCHAR2(10 CHAR),
NODE_TENURE VARCHAR2(20 CHAR),
SEX VARCHAR2(3 CHAR),
DATE_OF_BIRTH VARCHAR2(39 CHAR),
PROFESSION VARCHAR2(120 CHAR),
TREATMENT VARCHAR2(50 CHAR),
CUSTOMER_EXPERIENCE VARCHAR2(20 CHAR),
BILLING_ADDRESS VARCHAR2(231 CHAR),
BILLING_CITY VARCHAR2(80 CHAR),
BILLING_CODE VARCHAR2(30 CHAR),
SERV_ADDRESS VARCHAR2(4000 CHAR),
SERV_CITY VARCHAR2(255 CHAR),
SERV_REGION VARCHAR2(255 CHAR),
SERV_ZIPCODE VARCHAR2(255 CHAR),
INSTALL_DT VARCHAR2(39 CHAR),
NBR_OF_LINES VARCHAR2(10 CHAR),
DIMSUBSCRIPTIONSK VARCHAR2(15 CHAR) NOT NULL,
BILLED_REVENUE_AVG VARCHAR2(25 CHAR),
PROFESSION_CODE VARCHAR2(15 CHAR),
EXPORTDATE DATE
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
And my control file:
OPTIONS (DIRECT=FALSE)
load data
infile '/DBFS/vasilis/path/Data.txt'
into table PEO.DATA
fields terminated by "|"
(CLI,CUSTOMERID,CUSTOMERCODE,AFM,CUSTOMERSEGMENT,DIMPRODUCTSEGMENT,DIMPRODUCTCLASSIFICATIONSK,PRODUCTCLASSIFICATION,DIMPRODUCTSK,PRODUCTNAME,CUSTOMERTYPE,CUSTOMERTENURE,NODE_TENURE,SEX,DATE_OF_BIRTH,PROFESSION,TREATMENT,CUSTOMER_EXPERIENCE,BILLING_ADDRESS,BILLING_CITY,BILLING_CODE,SERV_ADDRESS,SERV_CITY,SERV_REGION,SERV_ZIPCODE,INSTALL_DT,NBR_OF_LINES,DIMSUBSCRIPTIONSK,BILLED_REVENUE_AVG,PROFESSION_CODE,EXPORTDATE DATE "YYYY-MM-DD HH24:MI:SS")
Loader file is clean and all rows loaded.
While I can see records in SQL Plus, from GUI Tools i.e. Toad I see all field blanks. However doing a lenght shows that data actually exists.
SELECT LENGTH(CLI) FROM PEO.DATA WHERE ROWNUM=1;
LENGTH(CLI)
-----------
21
1 row selected.
SELECT * FROM PEO.DATA WHERE ROWNUM=1;
CLI
------------------------------------------------------------
CUSTOMERID CUSTOMERCODE
------------------------- --------------------------------------------------
AFM CUSTOMERSEGMENT
-------------------------------------------------- -----------------------
DIMPRODUCTSEGMENT DIMPRODUCTCLASSIFICATIONSK
----------------------- --------------------------
PRODUCTCLASSIFICATION
--------------------------------------------------------------------------------
DIMPRODUCTSK
------------
PRODUCTNAME
--------------------------------------------------------------------------------
CUSTOMERTYPE CUSTOMERTENURE NODE_TENURE SEX
----------------------- -------------- -------------------- ---
DATE_OF_BIRTH
---------------------------------------
PROFESSION
--------------------------------------------------------------------------------
TREATMENT CUSTOMER_EXPERIENCE
-------------------------------------------------- --------------------
BILLING_ADDRESS
--------------------------------------------------------------------------------
BILLING_CITY
--------------------------------------------------------------------------------
BILLING_CODE
------------------------------
SERV_ADDRESS
--------------------------------------------------------------------------------
SERV_CITY
--------------------------------------------------------------------------------
SERV_REGION
--------------------------------------------------------------------------------
SERV_ZIPCODE
--------------------------------------------------------------------------------
INSTALL_DT NBR_OF_LINES DIMSUBSCRIPTIONSK
--------------------------------------- ------------ -----------------
BILLED_REVENUE_AVG PROFESSION_CODE EXPORTDATE
------------------------- --------------- ----------
1 row selected.
Any help, much appreciated :-)