這是我嘗試使用 SQL Loader 加載的控制檔案。但是，我只能加載 1 條記錄，并且無法將 TRL（資料檔案的最后一行）加載到 LTD 列中。我需要能夠將“TRL 02 0001 56778 34 999 111”值加載到 LTD 列中。感謝您對此的幫助。
樣本資料：
HDR
12|45|3|SUE|US
TRL 02 0001 56778 34 999 111
控制檔案：
OPTIONS (SKIP=1)
LOAD DATA
INFILE '*.TXT'
BADFILE 'A.bad'
INTO TABLE A
REPLACE
WHEN (1:3) != 'TRL'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
ID FILLER ,
LTD ,
CAGE ,
SUPP FILLER ,
CODE ,
NAME ,
DBA_NAME FILLER ,
CNTRY_CODE ,
STATUS CONSTANT "U",
RECORD_ID "S.nextval"
)
INTO TABLE A
REPLACE
WHEN (1:3) = 'TRL'
(
LTD CHAR(300),
STATUS CONSTANT "U",
RECORD_ID "S.nextval"
);
uj5u.com熱心網友回復：
當您插入多個表時，您必須使用position（否則它將不起作用）。此外，第二張桌子缺少trailing nullcols.
因此，對于樣本目標表和序列：
SQL> CREATE TABLE a
2 (
3 id NUMBER,
4 ltd VARCHAR2 (30),
5 cage VARCHAR2 (5),
6 supp NUMBER,
7 code VARCHAR2 (5),
8 name VARCHAR2 (5),
9 dba_name NUMBER,
10 cntry_code VARCHAR2 (5),
11 status VARCHAR2 (1),
12 record_id NUMBER
13 );
Table created.
SQL> CREATE SEQUENCE s;
Sequence created.
SQL>
控制檔案如下所示：
OPTIONS (SKIP=1)
LOAD DATA
INFILE * --> modified this (as I have sample data in the control file)
BADFILE 'A.bad'
INTO TABLE A
REPLACE
WHEN (1:3) != 'TRL'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
ID FILLER ,
LTD ,
CAGE ,
SUPP FILLER ,
CODE ,
NAME ,
DBA_NAME FILLER ,
CNTRY_CODE ,
STATUS CONSTANT "U",
RECORD_ID "S.nextval"
)
INTO TABLE A
REPLACE
WHEN (1:3) = 'TRL'
TRAILING NULLCOLS --> added this
(
LTD POSITION(1) CHAR(300), --> added POSITION
STATUS CONSTANT "U",
RECORD_ID "S.nextval"
)
begindata
HDR
12|45|3|SUE|US
TRL 02 0001 56778 34 999 111
測驗：
SQL> $sqlldr scott/tiger@orcl control=test41.ctl log=test41.log
SQL*Loader: Release 18.0.0.0.0 - Production on Pet Lip 10 08:37:05 2022
Version 18.5.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 1
Commit point reached - logical record count 2
Table A:
1 Row successfully loaded.
Table A:
1 Row successfully loaded.
Check the log file:
test41.log
for more information about the load.
SQL> select * from a;
ID LTD CAGE SUPP CODE NAME DBA_NAME CNTRY S RECORD_ID
---------- ------------------------------ ----- ---------- ----- ----- ---------- ----- - ----------
45 3 US U 1
TRL 02 0001 56778 34 999 111 U 2
SQL>
對我來說看起來不錯。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/489474.html
標籤：甲骨文 sql-loader
上一篇：在單個查詢的WITH子句中包含多個行內函式和多個CTE
下一篇：提取資料如果