You Asked
OK this is embarrassing, in my defense I have never had cause to use SQL Loader.
I have a table:
CREATE TABLE "TIMS"."T6142_ACL_CST"
(
"T6141_TRK_NBR_TXT" VARCHAR2(50 CHAR) NOT NULL ENABLE,
"T6142_ACL_CST_SEQ_NBR" NUMBER(*,0) NOT NULL ENABLE,
"ACL_CST_AMT" NUMBER(15,2) NOT NULL ENABLE,
"CST_QLF_DES_TXT" VARCHAR2(100 CHAR),
"ADD_DM" TIMESTAMP (6) NOT NULL ENABLE,
"UPD_DM" TIMESTAMP (6) NOT NULL ENABLE,
"UPD_ID" VARCHAR2(8 CHAR) NOT NULL ENABLE,
"UPD_PGM_ID" VARCHAR2(12 CHAR) NOT NULL ENABLE
)
And a csv file:
1ZY2648X0300122765,PRECONSOLIDATION_BASIC_CHG,19.8600
1ZY2648X0300122765,PRECONSOLIDATION_CALC_CHG,18.6000
1ZY2648X0300122765,PRECONSOLIDATION_FUEL_CHG,1.2600
1ZV0865A0300078233,CHG_ADD_HNDL,8.0000
1ZV0865A0300078233,CHG_RESIDENTIAL,.0000
1ZV0865A0300078233,PRECONSOLIDATION_BASIC_CHG,37.1100
1ZV0865A0300078233,PRECONSOLIDATION_CALC_CHG,27.2700
1ZV0865A0300078242,CHG_ADD_HNDL,8.0000
1ZV0865A0300078242,CHG_RESIDENTIAL,.0000
1ZV0865A0300078242,PRECONSOLIDATION_BASIC_CHG,37.1100
1ZV0865A0300078242,PRECONSOLIDATION_CALC_CHG,27.2700
1ZV0865A0300078242,PRECONSOLIDATION_FUEL_CHG,1.8400
And finally a control file:
LOAD DATA
INFILE 'TRFF870B.dat'
INTO TABLE T6142_ACL_CST APPEND
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
T6141_TRK_NBR_TXT CHAR NULLIF (T6141_TRK_NBR_TXT=BLANKS)
, CST_QLF_DES_TXT CHAR NULLIF (CST_QLF_DES_TXT=BLANKS)
, ACL_CST_AMT DECIMAL EXTERNAL NULLIF (ACL_CST_AMT=BLANKS)
, T6142_ACL_CST_SEQ_NBR SEQUENCE (MAX)
, ADD_DM SYSDATE
, UPD_DM SYSDATE
, UPD_ID CONSTANT "TIMS"
, UPD_PGM_ID CONSTANT "SQLLOADER"
)
When I run sqlldr nothing loads and I get the following in the log file:
Record 12: Rejected - Error on table T6141_ACL.
ORA-00947: not enough values
For every record, (I grabbed the 12 one for the above example).
So here is the question:
Do I have to have values for every column in the csv file? I tried to indicate that defaults are to be used for the ones I did not have.
Also I already know the sequence number column is not going to populate like I want. For instance looking at the csv file the sequence number I want for the first three would be 1,2,3 and the next batch would be 1,2,3,4 and so forth. In other words I want the sequence to start over when the trk_nbr changes.
Thanks,
Bob P.
and Tom said...
ops$tkyte%ORA11GR2> CREATE TABLE "T6142_ACL_CST"
2 (
3 "T6141_TRK_NBR_TXT" VARCHAR2(50 CHAR) NOT NULL ENABLE,
4 "T6142_ACL_CST_SEQ_NBR" NUMBER(*,0) NOT NULL ENABLE ,
5 "ACL_CST_AMT" NUMBER(15,2) NOT NULL ENABLE,
6 "CST_QLF_DES_TXT" VARCHAR2(100 CHAR),
7 "ADD_DM" TIMESTAMP (6) NOT NULL ENABLE ,
8 "UPD_DM" TIMESTAMP (6) NOT NULL ENABLE ,
9 "UPD_ID" VARCHAR2(8 CHAR) NOT NULL ENABLE ,
10 "UPD_PGM_ID" VARCHAR2(12 CHAR) NOT NULL ENABLE
11 )
12 /
Table created.
ops$tkyte%ORA11GR2> !cat t.ctl
LOAD DATA
INFILE *
INTO TABLE T6142_ACL_CST append
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
T6141_TRK_NBR_TXT CHAR,
CST_QLF_DES_TXT CHAR,
ACL_CST_AMT CHAR,
T6142_ACL_CST_SEQ_NBR SEQUENCE (MAX) ,
add_dm "systimestamp",
upd_dm "systimestamp",
upd_id constant "TIMS",
upd_pgm_id constant "SQLLDR"
)
begindata
1ZY2648X0300122765,PRECONSOLIDATION_BASIC_CHG,19.8600
1ZY2648X0300122765,PRECONSOLIDATION_CALC_CHG,18.6000
1ZY2648X0300122765,PRECONSOLIDATION_FUEL_CHG,1.2600
1ZV0865A0300078233,CHG_ADD_HNDL,8.0000
1ZV0865A0300078233,CHG_RESIDENTIAL,.0000
1ZV0865A0300078233,PRECONSOLIDATION_BASIC_CHG,37.1100
1ZV0865A0300078233,PRECONSOLIDATION_CALC_CHG,27.2700
1ZV0865A0300078242,CHG_ADD_HNDL,8.0000
1ZV0865A0300078242,CHG_RESIDENTIAL,.0000
1ZV0865A0300078242,PRECONSOLIDATION_BASIC_CHG,37.1100
1ZV0865A0300078242,PRECONSOLIDATION_CALC_CHG,27.2700
1ZV0865A0300078242,PRECONSOLIDATION_FUEL_CHG,1.8400
ops$tkyte%ORA11GR2> !sqlldr / t
SQL*Loader: Release 11.2.0.2.0 - Production on Tue Jan 4 06:28:20 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 12
ops$tkyte%ORA11GR2> select * from t6142_acl_cst;
T6141_TRK_NBR_TXT T6142_ACL_CST_SEQ_NBR
-------------------------------------------------- ---------------------
ACL_CST_AMT
-----------
CST_QLF_DES_TXT
-------------------------------------------------------------------------------
ADD_DM
---------------------------------------------------------------------------
UPD_DM
---------------------------------------------------------------------------
UPD_ID UPD_PGM_ID
-------- ------------
1ZY2648X0300122765 1
19.86
PRECONSOLIDATION_BASIC_CHG
04-JAN-11 06.28.20.494457 AM
04-JAN-11 06.28.20.494457 AM
TIMS SQLLDR
1ZY2648X0300122765 2
18.6
PRECONSOLIDATION_CALC_CHG
04-JAN-11 06.28.20.494457 AM
04-JAN-11 06.28.20.494457 AM
TIMS SQLLDR
1ZY2648X0300122765 3
1.26
PRECONSOLIDATION_FUEL_CHG
04-JAN-11 06.28.20.494457 AM
04-JAN-11 06.28.20.494457 AM
TIMS SQLLDR
1ZV0865A0300078233 4
8
CHG_ADD_HNDL
04-JAN-11 06.28.20.494457 AM
04-JAN-11 06.28.20.494457 AM
TIMS SQLLDR
1ZV0865A0300078233 5
0
CHG_RESIDENTIAL
04-JAN-11 06.28.20.494457 AM
04-JAN-11 06.28.20.494457 AM
TIMS SQLLDR
1ZV0865A0300078233 6
37.11
PRECONSOLIDATION_BASIC_CHG
04-JAN-11 06.28.20.494457 AM
04-JAN-11 06.28.20.494457 AM
TIMS SQLLDR
1ZV0865A0300078233 7
27.27
PRECONSOLIDATION_CALC_CHG
04-JAN-11 06.28.20.494457 AM
04-JAN-11 06.28.20.494457 AM
TIMS SQLLDR
1ZV0865A0300078242 8
8
CHG_ADD_HNDL
04-JAN-11 06.28.20.494457 AM
04-JAN-11 06.28.20.494457 AM
TIMS SQLLDR
1ZV0865A0300078242 9
0
CHG_RESIDENTIAL
04-JAN-11 06.28.20.494457 AM
04-JAN-11 06.28.20.494457 AM
TIMS SQLLDR
1ZV0865A0300078242 10
37.11
PRECONSOLIDATION_BASIC_CHG
04-JAN-11 06.28.20.494457 AM
04-JAN-11 06.28.20.494457 AM
TIMS SQLLDR
1ZV0865A0300078242 11
27.27
PRECONSOLIDATION_CALC_CHG
04-JAN-11 06.28.20.494457 AM
04-JAN-11 06.28.20.494457 AM
TIMS SQLLDR
1ZV0865A0300078242 12
1.84
PRECONSOLIDATION_FUEL_CHG
04-JAN-11 06.28.20.494457 AM
04-JAN-11 06.28.20.494457 AM
TIMS SQLLDR
12 rows selected.
You'll want to bulk update the table after the load if your sequences actually need to be gap free and reset to one.
ops$tkyte%ORA11GR2> select T6141_TRK_NBR_TXT, T6142_ACL_CST_SEQ_NBR from T6142_ACL_CST;
T6141_TRK_NBR_TXT T6142_ACL_CST_SEQ_NBR
-------------------------------------------------- ---------------------
1ZY2648X0300122765 1
1ZY2648X0300122765 2
1ZY2648X0300122765 3
1ZV0865A0300078233 4
1ZV0865A0300078233 5
1ZV0865A0300078233 6
1ZV0865A0300078233 7
1ZV0865A0300078242 8
1ZV0865A0300078242 9
1ZV0865A0300078242 10
1ZV0865A0300078242 11
1ZV0865A0300078242 12
12 rows selected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> merge into T6142_ACL_CST
2 using ( select rowid rid,
3 row_number() over (partition by T6141_TRK_NBR_TXT order by T6142_ACL_CST_SEQ_NBR) rn
4 from T6142_ACL_CST ) X
5 on (T6142_ACL_CST.rowid = x.rid)
6 when matched then update set T6142_ACL_CST_SEQ_NBR = x.rn
7 /
12 rows merged.
ops$tkyte%ORA11GR2> select T6141_TRK_NBR_TXT, T6142_ACL_CST_SEQ_NBR from T6142_ACL_CST;
T6141_TRK_NBR_TXT T6142_ACL_CST_SEQ_NBR
-------------------------------------------------- ---------------------
1ZY2648X0300122765 1
1ZY2648X0300122765 2
1ZY2648X0300122765 3
1ZV0865A0300078233 1
1ZV0865A0300078233 2
1ZV0865A0300078233 3
1ZV0865A0300078233 4
1ZV0865A0300078242 1
1ZV0865A0300078242 2
1ZV0865A0300078242 3
1ZV0865A0300078242 4
1ZV0865A0300078242 5
12 rows selected.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment