OraFAQ Forum: Server Utilities » problem with sqlldr

Home » RDBMS Server » Server Utilities » problem with sqlldr (Oracle 9i)

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

problem with sqlldr [message #309983]

Sun, 30 March 2008 21:09

kowalsky
Messages: 37
Registered: May 2003

Member

hi all,
I am trying to have sqlldr running against a file:
C:\oratest\20080318

Is it possible I get SQL*Loader-500: Unable to open file and 503 file not found just because the file name does not have an extension?

I can see that any file name I try it looks after whateverFileName.dat! Is there a way to have sqlldr working with files that do not have extensions?

Thanks,
kowalsky

Report message to a moderator

Re: problem with sqlldr [message #309992 is a reply to message #309983]

Sun, 30 March 2008 22:49

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Please post your file and control file.

Report message to a moderator

Re: problem with sqlldr [message #310013 is a reply to message #309983]

Sun, 30 March 2008 23:37

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Oracle and SQL*Loarder don't care of extensions.
A file name is from the first to the last character.

For more you have to post your statement and file names.

Regards
Michel

Report message to a moderator

Re: problem with sqlldr [message #310065 is a reply to message #309983]

Mon, 31 March 2008 02:45

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

I saw in windows there is actually saved as .txt extension but in its name the extension is not shown.
When I type the name without extension it shows no file.

Report message to a moderator

Re: problem with sqlldr [message #310079 is a reply to message #310065]

Mon, 31 March 2008 03:42

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Quote:

GO TO MY COMPUTER->TOOLS->FOLDER OPTIONS->VIEW And uncheck the option Hide Extensions For Known File Types

Then you are going to see full name of your file.

Can you please post the DATA FILE And CONTROL FILE.How the hell we are going to know what is the problem.

Report message to a moderator

Re: problem with sqlldr [message #310131 is a reply to message #309983]

Mon, 31 March 2008 06:34

kowalsky
Messages: 37
Registered: May 2003

Member

load data
infile 'C:\oratest\20080318'
BADFILE 'c:\oratest\badsyno.txt'
APPEND
into table synotest
fields terminated by "|" optionally enclosed by '"'
(id4, cityname, maxtemp, mintemp, precip DEFAULTIF(precip = "TR") , sog)

When I run this, sqlldr issues error messges as follows:
SQL*Loader-500: Unable to open file (C:\oratest\myfile.dat)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

If I change the extension of the file, then everything works (more specifically, if I change the name of the file to be 20080318.dat, then I can leave the INFILE statement unchanged, it will find the file),

thanks,
kowalsky

Report message to a moderator

Re: problem with sqlldr [message #310140 is a reply to message #310131]

Mon, 31 March 2008 07:12

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

My freind how many times i have to tell you please post the Data file 20080318 on forum. /forum/fa/1620/0/

/forum/fa/1620/0/

I think this is not complete file name 'C:\oratest\20080318'
that you are using in infile parameter.

Report message to a moderator

Re: problem with sqlldr [message #310201 is a reply to message #309983]

Mon, 31 March 2008 09:55

kowalsky
Messages: 37
Registered: May 2003

Member

Well rajatatrewal,
you'll just have to make that leap of faith and believe me on my word. I may not know why this is happening but I surely do know what my files are called ... actually, you made me curious ... take a look at the picture and try to tell me what is happening. I guess you don't need the data in the file ...

Attachment: pic2.JPG
(Size: 46.38KB, Downloaded 1985 times)

Report message to a moderator

Re: problem with sqlldr [message #310215 is a reply to message #310201]

Mon, 31 March 2008 10:30

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why don't you want to post your control file as requested?

Regards
Michel

Report message to a moderator

Re: problem with sqlldr [message #310217 is a reply to message #309983]

Mon, 31 March 2008 10:41

kowalsky
Messages: 37
Registered: May 2003

Member

Michel,
the control file was shown a few posts before; here it is again:

load data
infile 'C:\oratest\20080318'
BADFILE 'c:\oratest\badsyno.txt'
APPEND
into table synotest
fields terminated by "|" optionally enclosed by '"'
(id4, cityname, maxtemp, mintemp, precip DEFAULTIF(precip = "TR") , sog)

thanks,
kowalsky

Report message to a moderator

Re: problem with sqlldr [message #310218 is a reply to message #310217]

Mon, 31 March 2008 10:46

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Ok, now paste the LOG file.

Also post "dir c:\oratest".

Regards
Michel

Report message to a moderator

Re: problem with sqlldr [message #310222 is a reply to message #310218]

Mon, 31 March 2008 10:55

kowalsky
Messages: 37
Registered: May 2003

Member

SQL*Loader: Release 8.1.7.0.0 - Production on Mon Mar 31 11:52:27 2008

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Control File: c:\testdir\syno.ctl
Data File: C:\TestDir\20080325.dat
Bad File: c:\testdir\20080325.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional

Table SYNOTEST, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID4 FIRST * | O(") CHARACTER
CITYNAME NEXT * | O(") CHARACTER
MAXTEMP NEXT * | O(") CHARACTER
MINTEMP NEXT * | O(") CHARACTER
PRECIP NEXT * | O(") CHARACTER
DEFAULT if PRECIP = 0X5452(character 'TR')
SOG NEXT * | O(") CHARACTER

SQL*Loader-500: Unable to open file (C:\TestDir\20080325.dat)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.

C:\TestDir>dir
Volume in drive C has no label.
Volume Serial Number is 305E-C541

Directory of C:\TestDir

03/31/2008 11:06 AM <DIR> .
03/31/2008 11:06 AM <DIR> ..
03/13/2008 09:47 AM 5,990 20080313
03/26/2008 04:35 PM 5,989 20080325
03/31/2008 11:21 AM 826 20080325.bad
03/31/2008 11:52 AM 200 syno.ctl
03/31/2008 11:52 AM 1,414 syno.log
5 File(s) 14,419 bytes
2 Dir(s) 11,460,278,784 bytes free

C:\TestDir>

thanks,
kowalsky

Report message to a moderator

Re: problem with sqlldr [message #310226 is a reply to message #310222]

Mon, 31 March 2008 11:06

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What is the relation between c:\testdir\syno.ctl you actually use and c:\oratest\... you think you use?

Regards
Michel

Report message to a moderator

Re: problem with sqlldr [message #310227 is a reply to message #309983]

Mon, 31 March 2008 11:15

kowalsky
Messages: 37
Registered: May 2003

Member

Michel,
I am sorry for creating so much confusion ...
I know I should have paid attention, but the scripts run on at least three different machines, I've been in front of each and opened the forum and pasted whatever was on that particular machine.
On each machine, though, everything is consistent.

Let's take this one below:

C:\TestDir>dir
Volume in drive C has no label.
Volume Serial Number is 305E-C541

Directory of C:\TestDir

03/31/2008 11:06 AM <DIR> .
03/31/2008 11:06 AM <DIR> ..
03/13/2008 09:47 AM 5,990 20080313
03/26/2008 04:35 PM 5,989 20080325
03/31/2008 11:21 AM 826 20080325.bad
03/31/2008 11:52 AM 200 syno.ctl
03/31/2008 11:52 AM 1,414 syno.log
5 File(s) 14,419 bytes
2 Dir(s) 11,460,278,784 bytes free

C:\TestDir>

The log file is:
SQL*Loader: Release 8.1.7.0.0 - Production on Mon Mar 31 11:52:27 2008

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Control File: c:\testdir\syno.ctl
Data File: C:\TestDir\20080325.dat
Bad File: c:\testdir\20080325.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional

Table SYNOTEST, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID4 FIRST * | O(") CHARACTER
CITYNAME NEXT * | O(") CHARACTER
MAXTEMP NEXT * | O(") CHARACTER
MINTEMP NEXT * | O(") CHARACTER
PRECIP NEXT * | O(") CHARACTER
DEFAULT if PRECIP = 0X5452(character 'TR')
SOG NEXT * | O(") CHARACTER

SQL*Loader-500: Unable to open file (C:\TestDir\20080325.dat)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.

The control file is:
load data
infile 'C:\testdir\20080325'
APPEND
into table synotest
fields terminated by "|" optionally enclosed by '"'
(id4, cityname, maxtemp, mintemp, precip DEFAULTIF(precip = "TR") , sog)

Thanks,
kowalsky

Report message to a moderator

Re: problem with sqlldr [message #310234 is a reply to message #309983]

Mon, 31 March 2008 11:40

kowalsky
Messages: 37
Registered: May 2003

Member

problem soved!
just write the file name with a dot at the end:
infile 'C:\testdir\20080325.'
and that's it,

thanks,
kowalsky

Report message to a moderator

Re: problem with sqlldr [message #310300 is a reply to message #310234]

Mon, 31 March 2008 20:09

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Thanks for posting that answer. I remember running into that problem a couple years ago and couldn't remember how I solved it.

Too many things to remember with Oracle !!

Report message to a moderator

Re: problem with sqlldr [message #535332 is a reply to message #310300]

Tue, 13 December 2011 09:31

windwind
Messages: 3
Registered: December 2011
Location: delhi

Junior Member

but with AIX , this trick is not working.
kindly suggest some other way .... as i'm stuck

Report message to a moderator

Re: problem with sqlldr [message #535338 is a reply to message #535332]

Tue, 13 December 2011 09:46

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

No idea; however, how much would it cost if you actually add an extension to that file?

Report message to a moderator

Re: problem with sqlldr [message #535341 is a reply to message #535332]

Tue, 13 December 2011 09:48

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

Senior Member

Change your file name so that it has an extension and use the same extension in your control file, so that you do not have conflicts with default values. You can use .dat or .txt or .csv or whatever you like.

Report message to a moderator

Re: problem with sqlldr [message #535343 is a reply to message #535341]

Tue, 13 December 2011 09:59

windwind
Messages: 3
Registered: December 2011
Location: delhi

Junior Member

so you guys are saying ..... this is not possible. Pls help me if possible

Report message to a moderator

Re: problem with sqlldr [message #535344 is a reply to message #535343]

Tue, 13 December 2011 10:00

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

Senior Member

Is it not possible for you to copy or rename the file?

Report message to a moderator

Re: problem with sqlldr [message #535345 is a reply to message #535344]

Tue, 13 December 2011 10:02

windwind
Messages: 3
Registered: December 2011
Location: delhi

Junior Member

unfortunatly not .... is this not possible ?

Report message to a moderator

Re: problem with sqlldr [message #535351 is a reply to message #535345]

Tue, 13 December 2011 10:13

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

*Maybe* it is possible; you'd need to wait for someone who uses AIX and knows its tips & tricks. I'm not one of these people.

Report message to a moderator

Previous Topic:	Import whole database to another database.. is it possible?
Next Topic:	sqlldr help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 06:36:06 CST 2024