OraFAQ Forum: Server Utilities » sqlldr and default values

Home » RDBMS Server » Server Utilities » sqlldr and default values - Can this be done???

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

sqlldr and default values - Can this be done??? [message #70866]

Wed, 31 July 2002 13:01

Ronni B
Messages: 1
Registered: July 2002

Junior Member

Can I default a null value using sqlldr? I don't want to use when for two runs if I don't have to - plus I would like some flexability.

Thanks!
Ronni

Report message to a moderator

Re: sqlldr and default values - Can this be done??? [message #70869 is a reply to message #70866]

Thu, 01 August 2002 06:17

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

you can use decode or NVL
in the following sample, col1 will be inserted with 'no value' and col2 with '0' when a null value is encountered.

load data 
infile * 
insert 
into table mytable
fields terminated by ',' 
(col1 "nvl(:col1,'no value')", col2 "nvl(:col2,0)", 
col3) 
begindata 
.......
.......

Report message to a moderator

Re: sqlldr and default values - Can this be done??? [message #71993 is a reply to message #70866]

Mon, 17 February 2003 10:45

Waibale C.S Simon
Messages: 1
Registered: February 2003

Junior Member

Hi all-
I'm trying something similar using sqlldr with decode.

------Control file------
load data
infile 'c:pmsassets4.csv'
DISCARDFILE 'assets4.dsc'
Truncate
into table mfi_inputs_load
fields terminated by ',' optionally enclosed by '"' TRAILING NULLCOLS
(
ASH_CODE "decode(:ASH_CODE,
B1,AD1101,
B2,AD1102,
AD1101)",
FILLER01 FILLER,
VALUE "to_number(:VALUE,'99999999D99')",
YEAR_CODE "nvl(:YEAR_CODE,'2002')",
QUARTER_CODE "nvl(:QUARTER_CODE,'Q4')",
WEF_DATE SYSDATE,
MFI_CODE "nvl(:MFI_CODE,'MFI_077')"
)

---------'c:pmsassets4.csv'---------
B1, Cash ,"675,685",,,,
B2, Deposits in financial institutions ,"1,170,796",,,,

---------Excerpt of 'c:pmsassets4.out'-------

Record 1: Rejected - Error on table MFI_INPUTS_LOAD, column ASH_CODE.
ORA-00984: column not allowed here

Record 2: Rejected - Error on table MFI_INPUTS_LOAD, column ASH_CODE.
ORA-00984: column not allowed here

Report message to a moderator

Previous Topic:	Exporting objects to SQL statement
Next Topic:	urgent::dynamic sql in pro*C

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 15 16:12:48 CDT 2024