OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » Sqlldr Help!

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

Sqlldr Help! [message #70255]

Wed, 15 May 2002 14:05

jeff g
Messages: 5
Registered: June 2001

Junior Member

I have a set of data that only represents 6 columns
out of my 30 columns I have in my table. I have taken
care of any columsn that have ref. intergerity on them
and all non null columns. But my control file only
takes those 6 columns and any required columns and
leaves all other colums out. This is obvisouly not the
right soultion as data ends up in the wrong place.

Here is a sample of my ctl file and a couple lines out of my .dat file.

LOAD DATA
INFILE 'newmembers.csv'
BADFILE 'newmembers.bad'
DISCARDFILE 'newmembers.dsc'

INTO TABLE "PERSONS"
APPEND
FIELDS TERMINATED BY ','

(PERSONID "PERSONSPERSONID_SEQ.nextval",
COMPANYID constant 583623,
LOCATIONID constant 206354,
CREATEDATE "to_date(sysdate,'yyyy-mon-dd:hh24:mi:ss')",
TITLE,
FNAME,
LNAME,
EMAILADDRESS,
DEPARTMENTID constant 60,
HEARDID constant 10,
FIRMSIZEID constant 60,
NOEMAIL constant 1,
EMAILNEWSLETTER constant 1,
EMAILARTICLEALERT constant 1,
SENDEMAILINFO constant 1,
LEADSENT constant 1,
USERNAME,
PASSWORD,
USERSTATUS constant 1,
ATTEMPTS constant 0,
PACREDITBALANCE constant 1,
LEADQUALITYID constant 99,
DONOTCALL constant 0
)

data example

1st line

PERSONID,COMPANYID,LOCATIONID,
PARENTPERSONID,AFFINITYPARTNERID,REFERRINGPERSONID,POSITIONLEVELID,PREFIXID,CREATEDATE,TITLE,FNAME,LNAME,EMAILADDRESS,DEPARTMENTID,HEARDID,REFERER,FIRMSIZEID,AGREED,BADEMAIL,NOEMAIL,EMAILNEWSLETTER,EMAILARTICLEALERT,SENDEMAILINFO,LEADSENT,PHONENUMBER,PHONENUMBEREXT,MOBILENUMBER,PAGERNUMBER,FAXNUMBER,USERNAME,PASSWORD,USERSTATUS,LASTACTIVITY,ATTEMPTS,SESSIONID,DOCUMENTID,HIGHESTDEGREE,CONTRACTSTAFFER,PACREDITBALANCE,SESSIONDATA,LEADQUALITYID,DONOTCALL

2nd line
,,,,,,,,,BUSINESS INTEGRATION MANAGER,EDWARD,WHALEN JR,ewhalen@hasbro.com,,,,,,,,,,,,,,,,,ewhalen
jr@hasbro.com,password,,,,,,,,,,,

The first line is also the table desc, when I run it the first line always gets inserted and everthing else does not. The first line however never gets inserted correctly.

Any help would be greatly apperciated also a site or book with good documentation on sqlldr would be great.

Thanks
Jeff

Report message to a moderator

Re: Sqlldr Help! [message #70368 is a reply to message #70255]

Tue, 04 June 2002 07:54

Mike
Messages: 417
Registered: September 1998

Senior Member

you need to specify the start and end positions of each field such as title, name, etc.

Report message to a moderator

Previous Topic:	select statement
Next Topic:	concatenate parts of a row in sql loader

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 11:09:42 CDT 2024