Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to set the CTL file field in SQLLDR. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

The position keyword is used to specify the start and end positions of the column

Position (MVR n): starting from the m character and ending to the n character as the column value

Position (* + 2:15): the way to specify a numeric value directly is called an absolute offset. If you use the * sign, it is a relative offset, indicating that where the previous field ends, it starts this time, and the relative cheap amount can also 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 only the column length for the other columns.

FILLER: FILLER is specified in the control file, indicating that the column values are not imported into the table.

Ordinary

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 delimiter

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 columns than imported tables

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

Newline character processing

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. |

16Jane Blackie 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 the contents of the file to a large field

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, '99999999')"

FILEOWNER POSITION (* + 1:34)

FILENAME POSITION (* + 1) char "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 2369 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 1498 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 1743 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 1730 JUNSANSI F:\ oracle\ script\ ldr_case11_4.log

Load the line number of each line

Load data

Infile *

Into table t

Replace

(

Seqno RECNUM / / load the line number of each line

Text Position (1VR 1024)

)

BEGINDATA

Testline1

Testline2

On how to SQLLDR CTL file field settings to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.