OraFAQ Forum: Server Utilities » SQL Loader, CTL file: How to DEFAULT a value ?

Home » RDBMS Server » Server Utilities » SQL Loader, CTL file: How to DEFAULT a value ?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Loader, CTL file: How to DEFAULT a value ? [message #357533]

Wed, 05 November 2008 15:48

rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India

Senior Member

I have a CTL file as follows

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

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

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

[Updated on: Wed, 05 November 2008 15:49]

Report message to a moderator