Home » RDBMS Server » Server Utilities » NEWBIE: First time using SQL*Loader, plz help
NEWBIE: First time using SQL*Loader, plz help [message #234760] Thu, 03 May 2007 02:20 Go to next message
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
Re: NEWBIE: First time using SQL*Loader, plz help [message #234775 is a reply to message #234760] Thu, 03 May 2007 02:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
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
Re: NEWBIE: First time using SQL*Loader, plz help [message #234787 is a reply to message #234760] Thu, 03 May 2007 03:10 Go to previous messageGo to next message
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...
Re: NEWBIE: First time using SQL*Loader, plz help [message #234790 is a reply to message #234787] Thu, 03 May 2007 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What is your command?
What is in your log file?
What is your Oracle version?

Regards
Michel
Re: NEWBIE: First time using SQL*Loader, plz help [message #234797 is a reply to message #234760] Thu, 03 May 2007 03:29 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
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
Re: NEWBIE: First time using SQL*Loader, plz help [message #234816 is a reply to message #234809] Thu, 03 May 2007 04:04 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
10.2.0.1
Re: NEWBIE: First time using SQL*Loader, plz help [message #234817 is a reply to message #234797] Thu, 03 May 2007 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
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
Re: NEWBIE: First time using SQL*Loader, plz help [message #234820 is a reply to message #234760] Thu, 03 May 2007 04:16 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: NEWBIE: First time using SQL*Loader, plz help [message #234825 is a reply to message #234822] Thu, 03 May 2007 04:26 Go to previous messageGo to next message
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

Re: NEWBIE: First time using SQL*Loader, plz help [message #234863 is a reply to message #234825] Thu, 03 May 2007 06:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>använd TRAILING NULLCOLS
??
May be it is looking for trailing nullcols?
Re: NEWBIE: First time using SQL*Loader, plz help [message #234899 is a reply to message #234760] Thu, 03 May 2007 07:28 Go to previous message
hristo
Messages: 258
Registered: May 2007
Senior Member
I have solved the problem. I tried to load data into a table owned by sys...so i created a new user and the tables and it works like a charm...
Previous Topic: to insert data into a particular posistion that has missed
Next Topic: Passing parameters to a .ctl file
Goto Forum:
  


Current Time: Wed Dec 25 12:18:56 CST 2024