SQLLDR -- CTL file: field settings

Keywords: Oracle

The position keyword is used to specify the start and end positions of columns
position(m:n): refers to the column value from the m-th character to the n-th character
position(*+2:15): the way to directly specify the value is called absolute offset. If the * sign is used, it is the relative offset, indicating where the last field ends, where it starts this time, and where the relative cheap quantity can be calculated again.
position(*) char(9): the advantage of this relative offset + type and length is that you only need to specify the start position for the first column, and the other columns only need to specify the column length.
FILLER: the FILLER specified in the control file indicates that the column value is not imported into the table.

Common

Load DATA
INFILE *
INTO TABLE BONUS
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
BEGINDATA
SMITH,CLEAK,3904
ALLEN,SALESMAN,2891
WARD,SALESMAN,3128
KING,PRESIDENT,2523

No separator

LOAD DATA
INFILE *
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),
JOB position(7:15),
SAL position(17:20)
)
BEGINDATA
SMITH CLEAK     2891
ALLEN SALESMAN  2891
WARD  SALESMAN  3128
KING  PRESIDENT 2523

Fewer table columns than imported

LOAD DATA
INFILE *
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),
JOB position(7:15),
SAL position(17:20),
comm "0"
)
BEGINDATA
SMITH CLEAK     2891
ALLEN SALESMAN  2891
WARD  SALESMAN  3128
KING  PRESIDENT 2523

More than imported table columns

LOAD DATA
INFILE *
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:6),
TCOL FILLER position(8:11),
JOB position(13:21),
SAL position(23:26)
)
BEGINDATA
SMITH  7369 CLERK     800  20
ALLEN  7499 SALESMAN  1600 30
WARD   7521 SALESMAN  1250 30
JONES  7566 MANAGER   2975 20
MARTIN 7654 SALESMAN  1250 30
BLAKE  7698 MANAGER   2850 30
CLARK  7782 MANAGER   2450 10
KING   7839 PRESIDENT 5000 10
TURNER 7844 SALESMAN  1500 30
JAMES  7900 CLERK     950  30
FORD   7902 ANALYST   3000 20
MILLER 7934 CLERK     1300 10

Import different tables

LOAD DATA
INFILE *
TRUNCATE 
INTO TABLE BONUS
WHEN Tab='BON'
(
Tab FILLER position(1:3),
ENAME position(5:9),
JOB position(11:19),
SAL position(21:24)
)
INTO TABLE MANAGER
WHEN Tab='MGR'
(
Tab FILLER position(1:3),
MGRNO position(5:6),
MNAME position(8:14),
JOB position(16:28)
)
BEGINDATA
BON SMITH CLERK     3904
BON ALLEN SALER,M   2891
BON WARD  SALER,"S" 3128
BON KING  PRESIDENT 2523
MGR 10 SMITH   SALES MANAGER
MGR 11 ALLEN.W TECH MANAGER
MGR 16 BLAKE   HR MANAGER
TMP SMITH 7369 CLERK    800  20
TMP ALLEN 7499 SALESMAN 1600 30
TMP WARD  7521 SALESMAN 1250 30
TMP JONES 7566 MANAGER  2975 20

Line break handling

LOAD DATA
INFILE *
TRUNCATE INTO TABLE BONUS
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(ENAME,JOB,SAL)
BEGINDATA
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3182
KING,PRESIDENT,2523

Function usage

LOAD DATA
INFILE *
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),
JOB position(7:15),
SAL position(17:20),
comm "substr(:sal,1,1)"
)
BEGINDATA
SMITH CLEAK     3904
ALLEN SALESMAN  2891
WARD  SALESMAN  3128
KING  PRESIDENT 2523

Large field processing

LOAD DATA
INFILE * "str '\r\n'"
TRUNCATE INTO TABLE MANAGER
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(MGRNO,MNAME,JOB,REMARK char(100000))
BEGINDATA
10,SMITH,SALES MANAGER,This is SMITH.
He is a Sales Manager.|
11,ALLEN.W,TECH MANAGER,This is ALLEN.W.
He is a Tech Manager.|
16,BLAKE,HR MANAGER,"This is BLAKE. He is a Hr Manager. The jobs responsibilities are in the following:
1. Ensure the effective local implementation of corporate level HRinitiatives and new programs.
2. Take initiatives in defining HR strategy on attracting, hiring, integrating, developing, managing, rewarding and retaining talents for the sustainable development of company business.
3. Oversee stanard recruiting an procedures to ensure the company's staffing requirements are met in a timely manner, and interview management level candidates
4. Provide employees with fair and appropriate compensation and benefit, to ensure market competitiveness.
5. Develop, implement and oversee the training and development programs to upgrade the skills of the employee and to enhance the company's capabilities to met business goals and future challenges."

Load file contents to large fields

LOAD DATA
INFILE *
TRUNCATE INTO TABLE LOBTBL
(
  CREATE_DATE POSITION(1: 17) DATE 'YYYY-MM-DD HH24: MI',
  FILESIZE POSITION(*+1: 25) "to_number(:FILESIZE, '99，999，999')",
  FILEOWNER POSITION(*+1: 34),
  FILENAME POSITION(*+1) char(200) "substr(:FILENAME,instr(:FILENAME, '\\',-1)+1)",
  FILEDATA LOBFILE(FILENAME) TERMINATED BY EOF
)
BEGINDATA
2009-03-17  09:43 154     JUNSANSI F:\oracle\script\ldr_case11_1.ctl
2009-03-17  09:44 189     JUNSANSI F:\oracle\script\ldr_case11_1.dat
2009-03-17  09:44 2,369   JUNSANSI F:\oracle\script\ldr_case11_1.log
2009-03-16  16:50 173     JUNSANSI F:\oracle\script\ldr_case11_2.ctl
2009-03-16  16:49 204     JUNSANSI F:\oracle\script\ldr_case11_2.dat
2009-03-16  16:50 1,498   JUNSANSI F:\oracle\script\ldr_case11_2.log
2009-03-16  17:41 145     JUNSANSI F:\oracle\script\ldr_case11_3.ctl
2009-03-16  17:44 130     JUNSANSI F:\oracle\script\ldr_case11_3.dat
2009-03-16  17:44 1,743   JUNSANSI F:\oracle\script\ldr_case11_3.log
2009-03-17  11:01 132     JUNSANSI F:\oracle\script\ldr_case11_4.ctl
2009-03-17  11:02 188     JUNSANSI F:\oracle\script\ldr_case11_4.dat
2009-03-17  11:02 1,730   JUNSANSI F:\oracle\script\ldr_case11_4.log

Load line number of each line

load data 
infile * 
into table t 
replace 
(
seqno RECNUM //Load line number of each line 
text Position(1:1024)
) 
BEGINDATA 
testline1
testline2

Posted by jholzy on Fri, 01 May 2020 08:38:58 -0700

Programmer Group