| Decode value for sqlldr [message #506667] |
Tue, 10 May 2011 07:39  |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi,
I am trying to upload attached csv data in below described table.
Name Null? Type
------------ -------- --------------------------
MSISDN VARCHAR2(20)
PREFERENCES VARCHAR2(100)
PHONE_DIG NUMBER
There is some character type and null MSISDN records in csv file. due to this I want 0 in my PHONE_DIG column.
Accroding to attached ctl file i am not able to do that.
Kindly help in this
Pradeep
-
Attachment: test.csv
(Size: 0.11KB, Downloaded 1721 times)
|
|
|
|
| Re: Decode value for sqlldr [message #506668 is a reply to message #506667] |
Tue, 10 May 2011 07:43  |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
MSISDN PREFERENCES PHONE_DIG
--------------- -------------------- ----------
9999888890 rec 99
8899898989 rec 88
9317517818 rec 93
9988901745 rec2 99
----null-- rac null
9988901745 rec2 99
6 rows selected.
|
|
|
|
|
|
|
|
|
|
| Re: Decode value for sqlldr [message #506707 is a reply to message #506682] |
Tue, 10 May 2011 12:13 |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You showed what you are getting, but not what you want instead. I am guessing from your description that when your phone_dig is null, you want 0 instead. If that is so, then you can just wrap an NVL function around your SUBSTR function. If this is not what you want, then please post an example of what you want. The following also serves as an example of how to post a complete test case inline, as Michel was asking for, not as attachments, with csv file, ctl file, and create table statement (not just structure).
-- test.csv:
9999888890,rec
8899898989,rec
9317517818,rec
9988901745,rec2
,rac
9988901745,rec2
abccdred,rac
ac123cdes,rac
-- test.ctl:
load data
infile 'test.csv'
into table test
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(MSISDN,
PREFERENCES,
PHONE_DIG "NVL (SUBSTR (:MSISDN, 1, 4), 0)")
-- DDL (create table):
CREATE TABLE test
(MSISDN VARCHAR2(20),
PREFERENCES VARCHAR2(100),
PHONE_DIG NUMBER)
/
-- load and results:
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SCOTT@orcl_11gR2> SET NULL null
SCOTT@orcl_11gR2> COLUMN preferences FORMAT A11
SCOTT@orcl_11gR2> SELECT * FROM test
2 /
MSISDN PREFERENCES PHONE_DIG
-------------------- ----------- ----------
9999888890 rec 9999
8899898989 rec 8899
9317517818 rec 9317
9988901745 rec2 9988
null rac 0
9988901745 rec2 9988
6 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
