OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » sqlldr

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

sqlldr [message #146428]

Thu, 10 November 2005 06:43

AlinaC
Messages: 45
Registered: November 2005
Location: India

Member

Hi,

I'm trying to insert data using sqlloader utility of oracle, with the follwing ctl & datafile and want to load a blank field as null,but unable to do so. Data is being inserted into table but blank field is not inserted as null. Can anyone help me? Where should I modify my ctl file.

sample ctl file

load data
INFILE 'student'
BADFILE 'student.bad'
DISCARDFILE 'student.dis'
into table student
fields terminated by ','
TRAILING NULLCOLS
("ROLL" CHAR NULLIF "ROLL" = BLANKS,
"PHONE" CHAR NULLIF "PHONE"=BLANKS)

sample datafile

A001,743234
A002,
A003,567890
,646476

Table

Name Null? Type
-----------------------------------
--------------
ROLL CHAR(5)
PHONE
VARCHAR2(10)

Sample Inserted data

ROLL PHONE
----- ---------
A001 743234
A002
A003 567890
646476

But I want to insert data as follows-

ROLL PHONE
----- ---------
A001 743234
A002 null
A003 567890
null 646476

Regards,

Alina
alina_chatterjee@yahoo.com

Report message to a moderator

Re: sqlldr [message #146503 is a reply to message #146428]

Thu, 10 November 2005 18:47

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

I have moved your post and expect that someone in the 'Server Utilities' forum can help you.

David

Report message to a moderator

Re: sqlldr [message #146505 is a reply to message #146428]

Thu, 10 November 2005 19:27

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

Senior Member
Account Moderator

oracle@mutation#desc mutation scott.student

Table:scott.student
           Name                      Null?    Type
           ------------------------- -------- ------------------------
    1      ROLL                               CHAR(5)
    2      PHONE                              VARCHAR2(10)

oracle@mutation#query mutation scott.student

no rows selected

oracle@mutation#sqlldr userid=scott/tiger control=student.ctl

SQL*Loader: Release 9.2.0.4.0 - Production on Thu Nov 10 20:26:28 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 4
oracle@mutation#cat student.ctl
load data
INFILE 'student.data'
BADFILE 'student.bad'
DISCARDFILE 'student.dis'
into table student
fields terminated by ','
TRAILING NULLCOLS
(ROLL NULLIF ROLL="(null)",
PHONE NULLIF PHONE="(null)")
oracle@mutation#cat student.data
001,743234
A002,
A003,567890
,646476
oracle@mutation#query mutation scott.student

ROLL  PHONE
----- ----------
001   743234
A002
A003  567890
      646476

Report message to a moderator

Re: sqlldr [message #146711 is a reply to message #146428]

Sat, 12 November 2005 22:12

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Your original control file works fine. The difference is only in how the data is displayed, depending on your set commands, as shown below.

scott@ORA92> SELECT * FROM student
  2  /

ROLL  PHONE
----- ----------
A001  743234
A002
A003  567890
      646476

scott@ORA92> SET NULL NULL

scott@ORA92> SELECT * FROM student
  2  /

ROLL  PHONE
----- ----------
A001  743234
A002  NULL
A003  567890
NULL  646476

scott@ORA92>

Report message to a moderator

Previous Topic:	User Migration
Next Topic:	restrict duplicate data through sql*loader

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 26 19:45:55 CST 2024