sql-loaderoracle19c

Loading a huge CSV file with high performance in Oracle table

I have a CSV File which its size is about 20 Gig. The file has three Persian columns. I want to load it to Oracle Table. I searched and found that sql loader has high performance. But, when I load the file in the table, Persian data is not loaded in the right order. In fact, it is because Persian data is the right to left language. I use this control file:

 OPTIONS (SKIP=0, ERRORS=500, PARALLEL=TRUE, MULTITHREADING=TRUE, DIRECT=TRUE, 
 SILENT=(ALL))
 load data
 CHARACTERSET UTF8
 infile '/home/oracle/part.csv'
 APPEND
 into table Fact_test
 fields terminated by ','
 trailing nullcols(
 A_ID INTEGER,
 T_ID,
 G_ID,
 TRTYPE,
 ORRETURNED,
 MECH,
 AMN,
 TRAM INTEGER,
 USERID INTEGER,
 USERS INTEGER,
 VERID INTEGER,
 TRSTAMP CHAR(4000),
 OPR_BRID INTEGER
 )

File is like this:

 A_ID,T_ID,g_id,TrType,ORRETURNED,Mech,Amn,Tram,UserID,UserS,VerID,TRSTAMP,OPR_BRID
 276876739075,154709010853,4302,بروفق,اصلی,غیر سبک,بررسی,86617.1,999995,NULL,NULL,1981-11-16 13:23:16,2516

When I export the table in excel format, I receive this, some numbers become negative:

 (A_ID,T_ID,g_id,TrType,ORRETURNED,Mech,Amn,Tram,UserID,UserS,VerID,TRSTAMP,OPR_BRID) values (276876739075,'154709010853',411662402610,'4302','غیر بررسی','اصلي','سبک',-1344755500,-1445296167,-1311201320,909129772,'77.67',960051513);

The problem is when the data loaded, some columns have negative number and order of some columns change.

Would you please guide me how to solve the issue?

Any help is really appreciated.

Solution

  • Problem solved: I change the control file to this one:

      load data
  CHARACTERSET UTF8
  infile '/home/oracle/test_none.csv'
  APPEND
  into table Fact_test
  FIELDS terminated BY ','
  trailing nullcols(
  A_ID CHAR(50),
  T_ID CHAR(50),
  G_ID CHAR(50),
  TRTYPE,
  ORRETURNED,
  MECH,
  AMN,
  TRAM CHAR(50),
  USERID,
  USERS CHAR(50),
  VERID CHAR(50),
  TRSTAMP,
  OPR_BRID CHAR(50)
  )