SQL Loader, CTL file: How to default a value

raksrules

Well-Known Member
Elite
Aug 30, 2007
9,922
1,754
378
Mumbai
I have a CTL file as follows

Code:
LOAD DATA
APPEND
INTO TABLE IMPORT_DETAILS_TMP 
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
( CLIENTNO	INTEGER EXTERNAL   
 ,SITENO	INTEGER EXTERNAL  
 ,CUSTLEV3ID
 ,CUSTLEV4ID
 ,PERIOD_END	DATE "MMDDYYYY"
 ,dummy1 FILLER
 ,CUSTLEV5ID 
 ,col8 FILLER
 ,col9 FILLER
 ,col10 FILLER
 ,col11 FILLER
 ,col12 FILLER
 ,col13 FILLER
 ,col14 FILLER
 ,dummy2 FILLER
 ,dummy4 FILLER
 )
Now here i want to add an additional column named SERVICEID which is something i do not receive in the data file. But i want to default some value (say 0) and insert the same using this control file in the table.
Effectively i want to control file as follows

Code:
LOAD DATA
APPEND
INTO TABLE IMPORT_DETAILS_TMP 
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
( CLIENTNO	INTEGER EXTERNAL   
 ,SITENO	INTEGER EXTERNAL  
 ,CUSTLEV3ID
 ,CUSTLEV4ID
 ,PERIOD_END	DATE "MMDDYYYY"
 ,dummy1 FILLER
 ,CUSTLEV5ID 
 ,col8 FILLER
 ,col9 FILLER
 ,col10 FILLER
 ,col11 FILLER
 ,col12 FILLER
 ,col13 FILLER
 ,col14 FILLER
 ,dummy2 FILLER
 ,dummy4 FILLER
 ,SERVICEID	DEFAULT 0
 )
Now when i run the sql loader command on this ctl file it throws an error as follows

Code:
SQL*Loader-350: Syntax error at line 22.
Expecting "," or ")", found "DEFAULT".
 ,SERVICEID     DEFAULT 0
How do i specify this DEFAULT for a column in the table IMPORT_DETAILS_TMP and populate the same without the corresponding data not being available in the data file
 

47Shailesh

Active Member
Disciple
Apr 4, 2008
777
20
31
72
Code:
LOAD DATA

APPEND

INTO TABLE IMPORT_DETAILS_TMP 

FIELDS TERMINATED BY "|"

TRAILING NULLCOLS

( CLIENTNO	INTEGER EXTERNAL   

 ,SITENO	INTEGER EXTERNAL  

 ,CUSTLEV3ID

 ,CUSTLEV4ID

 ,PERIOD_END	DATE "MMDDYYYY"

 ,dummy1 FILLER

 ,CUSTLEV5ID 

 ,col8 FILLER

 ,col9 FILLER

 ,col10 FILLER

 ,col11 FILLER

 ,col12 FILLER

 ,col13 FILLER

 ,col14 FILLER

 ,dummy2 FILLER

 ,dummy4 FILLER

[B] ,SERVICEID DECODE(:SERVICEID, NULL, GetDefaultData('Default', 'SERVICEID'), :SERVICEID)[/B]

 )
Hope it helps you.