OraFAQ Forum: Server Utilities » NEWBIE: First time using SQL*Loader, plz help

Home » RDBMS Server » Server Utilities » NEWBIE: First time using SQL*Loader, plz help

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

NEWBIE: First time using SQL*Loader, plz help [message #234760]

Thu, 03 May 2007 02:20

hristo
Messages: 258
Registered: May 2007

Senior Member

Hi!

Im trying to use SQL*Loader. I have some .dat files I want to load inte my database.
But how do I create the control file? How do I save it to get the .ctl format?
Im using WinXP.

This is a example of my file:

load data
infile 'SUPPLIERS.dat'
badfile 'sample.bad'
discardfile 'sample.dsc'
INTO table suppliers
fields terminated by ',' optionally enclosed by '"'
(id number(3),
name varchar2(30),
adress varchar2(45),
contact varchar2(30))

Will that work at all?

So two questions:

1. Save it as a .ctl
2. Is my control file correct?

Regards
Hristo

Report message to a moderator

Re: NEWBIE: First time using SQL*Loader, plz help [message #234775 is a reply to message #234760]

Thu, 03 May 2007 02:32

Michel Cadot
Messages: 68663
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1. .ctl is just plain text, nothing more, you can also calls it as you want, .ctl is just commonly use.

2. It seems correct if it matches your table.

Regards
Michel

Report message to a moderator

Re: NEWBIE: First time using SQL*Loader, plz help [message #234787 is a reply to message #234760]

Thu, 03 May 2007 03:10

hristo
Messages: 258
Registered: May 2007

Senior Member

Well, I had to make some changes to my controlfile:

load data
infile 'C:\SUPPLIERS.dat'
badfile 'sample.bad'
discardfile 'sample.dsc'
INTO table SYS.SUPPLIER
fields terminated by ',' optionally enclosed by '"'
(id,
name,
adress,
contact)

Now SQLLoader executes, but no data are being loaded into my table.
Any suggestions?
I dont get any errors...

Report message to a moderator

Re: NEWBIE: First time using SQL*Loader, plz help [message #234790 is a reply to message #234787]

Thu, 03 May 2007 03:18

Michel Cadot
Messages: 68663
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What is your command?
What is in your log file?
What is your Oracle version?

Regards
Michel

Report message to a moderator

Re: NEWBIE: First time using SQL*Loader, plz help [message #234797 is a reply to message #234760]

Thu, 03 May 2007 03:29

hristo
Messages: 258
Registered: May 2007

Senior Member

Well, I found the logfile. I will translate some parts of it:

Kolumnnamn Position Lngd Avsl Omgiv Datatyp
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST * , O(") CHARACTER
NAME NEXT * , O(") CHARACTER
ADDRESS NEXT * , O(") CHARACTER
CONTACT NEXT * , O(") CHARACTER
************
***********
Well, first of all, ID is not a character, its a number. How do I write in the control file to make it a number? It doesnt work the way I did before (se first post).

Kolumn hittades inte f�re slutet p� logisk post (anv�nd TRAILING NULLCOLS)
=
Coloumn was not find before end of logical post (use TRAILING NULLCOLS)

What does this mean?
**********
***********'
Post 1 avslagen - Fel i tabell SYS.CUSTOMER, kolumn ADDRESS.
Kolumn hittades inte f�re slutet p� logisk post (anv�nd TRAILING NULLCOLS)
Post 2 avslagen - Fel i tabell SYS.CUSTOMER, kolumn ADDRESS.
Kolumn hittades inte f�re slutet p� logisk post (anv�nd TRAILING NULLCOLS)
Post 3 avslagen - Fel i tabell SYS.CUSTOMER, kolumn ADDRESS.
Kolumn hittades inte f�re slutet p� logisk post (anv�nd TRAILING NULLCOLS)
Post 4 avslagen - Fel i tabell SYS.CUSTOMER, kolumn ADDRESS.
Kolumn hittades inte f�re slutet p� logisk post (anv�nd TRAILING NULLCOLS)
Post 5 avslagen - Fel i tabell SYS.CUSTOMER, kolumn ADDRESS.
Kolumn hittades inte f�re slutet p� logisk post (anv�nd TRAILING NULLCOLS)
Post 6 avslagen - Fel i tabell SYS.CUSTOMER, kolumn ADDRESS.
Kolumn hittades inte f�re slutet p� logisk post (anv�nd TRAILING NULLCOLS)
Post 7 avslagen - Fel i tabell SYS.CUSTOMER, kolumn ADDRESS.
Kolumn hittades inte f�re slutet p� logisk post (anv�nd TRAILING NULLCOLS)

Tabell SYS.CUSTOMER:
0 Rader har l�sts in.
7 Rader l�stes inte in pga. datafel.
0 Rader l�stes inte in pga. att alla WHEN-satser var felaktiga.
0 Rader l�stes inte in pga. att alla f�lt inneh�ll NULL-v�rden.

****************************************************************

1. Command? I just load data. Embarassed

Starts SQL*Loader with sqlldr system/oracle
then supplies the controlfile path c:\control.txt
2. ? I guess I have to include that in my controlfile? Will it be autogenerated?
3. 10g

[Updated on: Thu, 03 May 2007 04:01]

Report message to a moderator

Re: NEWBIE: First time using SQL*Loader, plz help [message #234809 is a reply to message #234797]

Thu, 03 May 2007 03:58

Michel Cadot
Messages: 68663
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1. command line: sqlldr ...
2. yes, you should add it
3. 10g what? 10.1.0.1, 10.1.0.2, 10.1.0.3, 10.1.0.4, 10.1.0.5, 10.1.0.6, 10.2.0.1, 10.2.0.2, 10.2.0.3, 10.2.0.4?

Regards
Michel

Report message to a moderator

Re: NEWBIE: First time using SQL*Loader, plz help [message #234816 is a reply to message #234809]

Thu, 03 May 2007 04:04

hristo
Messages: 258
Registered: May 2007

Senior Member

10.2.0.1

Report message to a moderator

Re: NEWBIE: First time using SQL*Loader, plz help [message #234817 is a reply to message #234797]

Thu, 03 May 2007 04:07

Michel Cadot
Messages: 68663
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Are you sure you data file satisfied:

Quote:

fields terminated by ',' optionally enclosed by '"'

It seems either they don't satified that or you don't have the 4 fields.

Post an exemple of your data (1 line).

Regards
Michel

Report message to a moderator

Re: NEWBIE: First time using SQL*Loader, plz help [message #234820 is a reply to message #234760]

Thu, 03 May 2007 04:16

hristo
Messages: 258
Registered: May 2007

Senior Member

100001;Reggaekolletivet regnb�gen;Inre cirkeln 25, 135 14 Sk�rholmen;Moo;

Dont I have to specifiy what kind of data I am loading for each column? Above the first one should be number, the next varchar2 and so one.

[Updated on: Thu, 03 May 2007 04:24]

Report message to a moderator

Re: NEWBIE: First time using SQL*Loader, plz help [message #234822 is a reply to message #234820]

Thu, 03 May 2007 04:24

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

fields terminated by ',' ??

According to

Quote:

100001;Reggaekolletivet regnb�gen;Inre cirkeln 25, 135 14 Sk�rholmen;Moo;

Looks like fields terminated by ';'

Lycka till

Report message to a moderator

Re: NEWBIE: First time using SQL*Loader, plz help [message #234825 is a reply to message #234822]

Thu, 03 May 2007 04:26

hristo
Messages: 258
Registered: May 2007

Senior Member

With a fellow Swede here I post the whole log file:

Detta �r den senaste, efter det att jag �ndrat , till ; i min ctl fil
************************************************

SQL*Loader: Release 10.2.0.1.0 - Production on To Maj 3 11:25:42 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Kontrollfil: c:\control.txt
Datafil: C:\CUSTOMERS.dat
Felfil: sample.bad
Bortsorteringsfil: sample.dsc
(Till�t alla bortsorteringar)

Antal att l�sa in: ALL
Antal att hoppa �ver: 0
Till�tna fel: 50
Bindvektor: 64 rader, maximalt 256000 byte
Forts�ttning: inget angivet
Anv�nd laddning: Konventionell

Tabell SYS.CUSTOMER, laddad fr�n varje logisk post.
INSERT g�ller f�r denna tabell: INSERT

Kolumnnamn Position Lngd Avsl Omgiv Datatyp
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST * ; O(") CHARACTER
NAME NEXT * ; O(") CHARACTER
ADDRESS NEXT * ; O(") CHARACTER
CONTACT NEXT * ; O(") CHARACTER

Post 1 avslagen - Fel i tabell SYS.CUSTOMER, kolumn NAME.
Kolumn hittades inte f�re slutet p� logisk post (anv�nd TRAILING NULLCOLS)
Post 2 avslagen - Fel i tabell SYS.CUSTOMER, kolumn NAME.
Kolumn hittades inte f�re slutet p� logisk post (anv�nd TRAILING NULLCOLS)
Post 3 avslagen - Fel i tabell SYS.CUSTOMER, kolumn NAME.
Kolumn hittades inte f�re slutet p� logisk post (anv�nd TRAILING NULLCOLS)
Post 4 avslagen - Fel i tabell SYS.CUSTOMER, kolumn NAME.
Kolumn hittades inte f�re slutet p� logisk post (anv�nd TRAILING NULLCOLS)
Post 5 avslagen - Fel i tabell SYS.CUSTOMER, kolumn NAME.
Kolumn hittades inte f�re slutet p� logisk post (anv�nd TRAILING NULLCOLS)
Post 6 avslagen - Fel i tabell SYS.CUSTOMER, kolumn NAME.
Kolumn hittades inte f�re slutet p� logisk post (anv�nd TRAILING NULLCOLS)
Post 7 avslagen - Fel i tabell SYS.CUSTOMER, kolumn NAME.
Kolumn hittades inte f�re slutet p� logisk post (anv�nd TRAILING NULLCOLS)

Tabell SYS.CUSTOMER:
0 Rader har l�sts in.
7 Rader l�stes inte in pga. datafel.
0 Rader l�stes inte in pga. att alla WHEN-satser var felaktiga.
0 Rader l�stes inte in pga. att alla f�lt inneh�ll NULL-v�rden.

tilldelat minnesutrymme f�r bindvektor: 66048 byte(64 rader)
Byte i inl�sningsbuffert: 1048576

Totalt antal �verhoppade logiska poster: 0
Totalt antal l�sta logiska poster: 7
Totalt antal avslagna logiska poster: 7
Totalt antal bortsorterade logiska poster: 0

K�rningen b�rjade To Maj 03 11:25:42 2007
K�rningen slutade To Maj 03 11:25:48 2007

Total l�ptid: 00:00:05.71
CPU-tid: 00:00:00.08

************************************************************

H�r kommer ctl filen:

load data
infile 'C:\CUSTOMERS.dat'
badfile 'sample.bad'
discardfile 'sample.dsc'
INTO table SYS.CUSTOMER
fields terminated by ';'optionally enclosed by '"'
(ID,
NAME,
ADDRESS,
CONTACT)

[Updated on: Thu, 03 May 2007 04:29]

Report message to a moderator