Home » RDBMS Server » Server Utilities » Decode value for sqlldr (Oracle 10g, windows xp)
Decode value for sqlldr [message #506667] Tue, 10 May 2011 07:39 Go to next message
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 1367 times)
Re: Decode value for sqlldr [message #506668 is a reply to message #506667] Tue, 10 May 2011 07:43 Go to previous messageGo to next message
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 #506669 is a reply to message #506667] Tue, 10 May 2011 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post create table statement and your control file.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

[Updated on: Tue, 10 May 2011 07:50]

Report message to a moderator

Re: Decode value for sqlldr [message #506678 is a reply to message #506669] Tue, 10 May 2011 08:24 Go to previous messageGo to next message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi,

Table Syntax has already been posted in my first post. Control file attached.

Oracle version is "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - "



Regards


Pradeep
  • Attachment: test.ctl
    (Size: 0.18KB, Downloaded 1356 times)
Re: Decode value for sqlldr [message #506682 is a reply to message #506678] Tue, 10 May 2011 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CREATE TABLE? Where?

I can't download files.

Regards
Michel
Re: Decode value for sqlldr [message #506707 is a reply to message #506682] Tue, 10 May 2011 12:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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>

Re: Decode value for sqlldr [message #506727 is a reply to message #506707] Tue, 10 May 2011 23:28 Go to previous message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi Barbara,

Thanks for your great help



Regards

Pradeep
Previous Topic: Loading Multiple XML files using SQL*Loader
Next Topic: Logical backups
Goto Forum:
  


Current Time: Thu Mar 28 10:22:12 CDT 2024