使用sqldr连接两个值会抛出错误

Question

我有一个表，我想使用sqldr加载数据，表的DDL如下，

create table abc.AccountDataDump (
CIF_ID  varchar2(20),
ACCOUNT_NO varchar2(30),
TURNOVER number(15,2),
CASH_WITHDRAWAL number(15,2),
CASH_DEPOSIT number(15,2),
MONTH number(2),
YEAR number(4) );

我有ctl文件如下，

LOAD DATA
INFILE '/home/sijo/Downloads/testcash/CXPS_CASHDATA_MONTHLY_APR21.TXT' 
badfile '/home/sijo/Downloads/testcash/cash.bad' 
discardfile '/home/sijo/Downloads/testcash/cash.rej' 
TRUNCATE INTO TABLE CXPSADM_sijo_47z50.AccountDataDump 
FIELDS TERMINATED BY '~|' 
TRAILING NULLCOLS 
( 
"CIF_ID", 
"ACCOUNT_NO" "A_F_||:ACCOUNT_NO", 
"TURNOVER", 
"CASH_WITHDRAWAL", 
"CASH_DEPOSIT" , 
"MONTH" , 
"YEAR" 
) 

基本上，我试图在"ACCOUNT_NO"前加上'A_F_'。但是它抛出的错误如下所示。in文件中的账号是正确的

"Record 1: Rejected - Error on table CXPSADM_SIJO_47Z50.ACCOUNTDATADUMP, column "ACCOUNT_NO".
ORA-00984: column not allowed here"

如果我用"ACCOUNT_NO"替换"ACCOUNT_NO" "A_F_||:ACCOUNT_NO"，那么它工作得很好。请帮助

Answer 1

Replace

"ACCOUNT_NO" "A_F_||:ACCOUNT_NO", 

ACCOUNT_NO "'A_F_'||:ACCOUNT_NO",

让我告诉你它是如何工作的

[ftpfdm@scglvdoracd0006 ~]$ cat t.ctl
LOAD DATA
INFILE '/home/ftpfdm/t.dat'
badfile '/home/ftpfdm/t.bad'
discardfile '/home/ftpfdm/t.dsc'
TRUNCATE INTO TABLE TEST1.LOADER_EXAMPLE
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
CIF_ID,
ACCOUNT_NO "'A_F_'||:ACCOUNT_NO"
)
[ftpfdm@scglvdoracd0006 ~]$ cat t.dat
1;A2
2;B2
[ftpfdm@scglvdoracd0006 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 1 11:37:12 2021
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL> truncate table test1.loader_Example ;
Table truncated.
SQL> desc test1.loader_example
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
CIF_ID                                             NUMBER
ACCOUNT_NO                                         VARCHAR2(50)
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
[ftpfdm@scglvdoracd0006 ~]$ sqlldr control=t.ctl
Username:/ as sysdba
SQL*Loader: Release 19.0.0.0.0 - Production on Wed Sep 1 11:37:32 2021
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Path used:      Conventional
Commit point reached - logical record count 2
Table TEST1.LOADER_EXAMPLE:
2 Rows successfully loaded.
Check the log file:
t.log
for more information about the load.
[ftpfdm@scglvdoracd0006 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 1 11:37:40 2021
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL> select * from test1.loader_example ;
CIF_ID ACCOUNT_NO
---------- --------------------------------------------------
1 A_F_A2
2 A_F_B2
SQL>