Home » RDBMS Server » Server Utilities » SQL Loader, CTL file: How to DEFAULT a value ?
SQL Loader, CTL file: How to DEFAULT a value ? [message #357533] Wed, 05 November 2008 15:48 Go to next message
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

Re: SQL Loader, CTL file: How to DEFAULT a value ? [message #357777 is a reply to message #357533] Thu, 06 November 2008 09:27 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
,SERVICEID CONSTANT 0
Previous Topic: Impdp so slow
Next Topic: problem different date format loading using sqlldr
Goto Forum:
  


Current Time: Wed Dec 07 12:26:09 CST 2016

Total time taken to generate the page: 0.14205 seconds