CODEWITHSUNDEEP
oracle-databaseplsqlsql-loader
Sijo Kurien
Sijo Kurien

Reputation: 135

Concat two values using sqlldr throws error

I have a table to which i want to load data using sqlldr, The DDL of the table is as below,

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) );

I have the ctl file as below,

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" 
 )

Basically i am trying to prepend 'A_F_' to the "ACCOUNT_NO" but it is throwing the error as below. The account number in the in file is proper

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

If i replace "ACCOUNT_NO" "A_F_||:ACCOUNT_NO", with just "ACCOUNT_NO", then it is working fine. Please help

Upvotes: 0

Views: 131

Answers (1)

Roberto Hernandez
Roberto Hernandez

Reputation: 8528

Replace

"ACCOUNT_NO" "A_F_||:ACCOUNT_NO",

by

ACCOUNT_NO "'A_F_'||:ACCOUNT_NO",

Let me show you how it works

Demo

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

Upvotes: 1

Related Questions