Home » RDBMS Server » Server Utilities » Not able to load the from the flat file. (Oracle 10g)
Not able to load the from the flat file. [message #381317] Fri, 16 January 2009 05:41 Go to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear All,
I am having a .txt file which has more records . Here i am trying to insert the data from flat file to the database with use of sql*loader.But the below error is thrown.

  1  CREATE TABLE test
  2  (x VARCHAR2(40),
  3  a NUMBER(35),
  4  b NUMBER(10),
  5  c NUMBER(20),
  6  d NUMBER(35),
  7  e NUMBER(30))
  8  ORGANIZATION EXTERNAL
 15  (DEFAULT DIRECTORY TEST_DIR
 16  LOCATION('LOAD.CSV')
 17* )
SQL> /

Table created.

SQL> SELECT * FROM test;
SELECT * FROM test
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04020: found record longer than buffer size supported, 524288, in
C:\LOADER\LOAD.CSV
ORA-06512: at "SYS.ORACLE_LOADER", line 52
ORA-06512: at line 1


SQL> ALTER TABLE test REJECT LIMIT UNLIMITED;

Table altered.

SQL> SELECT * FROM test;
SELECT * FROM test
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04020: found record longer than buffer size supported, 524288, in
C:\LOADER\LOAD.CSV
ORA-06512: at "SYS.ORACLE_LOADER", line 52
ORA-06512: at line 1



Need your kind suggestion .


Thanks and Regards,
Hammer.
Re: Not able to load the from the flat file. [message #381329 is a reply to message #381317] Fri, 16 January 2009 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add the parameter READSIZE.

Regards
Michel
Re: Not able to load the from the flat file. [message #382100 is a reply to message #381329] Wed, 21 January 2009 00:00 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear Michel,
Thank you for your reply.Can you give me the link for sql loader for oracle 10g with examples.



Thanks and Regards,
Hammer.

[Updated on: Wed, 21 January 2009 00:02]

Report message to a moderator

Re: Not able to load the from the flat file. [message #382103 is a reply to message #381317] Wed, 21 January 2009 00:06 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/toc.htm
Re: Not able to load the from the flat file. [message #382118 is a reply to message #382100] Wed, 21 January 2009 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The link is under the READSIZE word, just click on it.

Regards
Michel
Re: Not able to load the from the flat file. [message #382168 is a reply to message #382118] Wed, 21 January 2009 04:01 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear All,
I have successfully set the read size but i am getting the below error which hits my screen.Need your kind suggestion what exactly need to do.(Anything related to permission-this is my guess ,but i am not sure.)



C:\Documents and Settings\Administrator>SQLLDR scott/tiger CONTROL="C:\FAQ\TEST.CT
L" LOG="C:\FAQ\TEST1.LOG"

SQL*Loader: Release 10.1.0.2.0 - Production on Wed Jan 21 15:24:59 2009

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

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

C:\Documents and Settings\Administrator>


Thanks and Regards,
Hammer.
Re: Not able to load the from the flat file. [message #382181 is a reply to message #382168] Wed, 21 January 2009 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What don't you understand to "The system cannot find the file specified"?

Regards
Michel
Re: Not able to load the from the flat file. [message #382184 is a reply to message #382181] Wed, 21 January 2009 04:47 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear Michel,
I have pointed out to the exact path where the control file is located.


Thanks and Regards,
Hammer.
Re: Not able to load the from the flat file. [message #382187 is a reply to message #382184] Wed, 21 January 2009 04:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't prove the file exists and I tend to trust Oracle over poster.

Regards
Michel
Re: Not able to load the from the flat file. [message #382556 is a reply to message #382187] Fri, 23 January 2009 00:21 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear Michel,
I have the data file and control file both in the same drive 'c:\faq\test.dat','c:\faq\test.ctl'.My control file looks like below.

OPTIONS (ROWS=1000, readsize=12582912, BINDSIZE=12582912, SILENT=FEEDBACK,DISCARDS)
LOAD DATA
infile 'C:\FAQ\test.dat'
APPEND INTO TABLE TEST
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
A,
B,
C,
D,
E,
F,
G)


In command prompt i have issued the below command


Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>SQLLDR scott/tiger CONTROL='C:\FAQ\TEST.CTL'

SQL*Loader: Release 10.1.0.2.0 - Production on Fri Jan 23 11:46:37 2009

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

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

C:\Documents and Settings\Administrator>


I even use the "Find" command under the "Start" menu and it was able to locate the above mentioned files. It seems everything is there, but I am not sure why sql Loader is not able to locate the files, and kept on giving me the same messages.

Thanks in advance.


Thanks and Regards,
Hammer


Re: Not able to load the from the flat file. [message #382558 is a reply to message #382556] Fri, 23 January 2009 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have the data file and control file both in the same drive 'c:\faq\test.dat','c:\faq\test.ctl'.

Quote:
You didn't prove the file exists

...and is accessible.

Regards
Michel
Re: Not able to load the from the flat file. [message #382563 is a reply to message #382558] Fri, 23 January 2009 00:49 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear Michel,

>>
You didn't prove the file exists 



I think i am missing here how to prove that the file exists,by use of any commands.Ii will be great if you quote some examples.

Thanks And Regards,
Hammer.

Re: Not able to load the from the flat file. [message #382564 is a reply to message #381317] Fri, 23 January 2009 00:50 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
ls -l filename?
Re: Not able to load the from the flat file. [message #382565 is a reply to message #382556] Fri, 23 January 2009 00:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
Please post the results of the following from your operating system command prompt:

dir c:\faq\test.ctl
dir c:\faq\test.dat

and the following from sql*plus:

host dir c:\faq\test.ctl
host dir c:\faq\test.dat

It is possible you have missed some additional default extension. Otherwise, it is likely due to the operating system user that Oracle runs under lacking privileges to that directory. You can try editing the files from SQL*Plus in the default directory that Oracle uses without any paths, and copying and pasting into that then running sqlldr from SQL*Plus using the host command without any paths, for example:

SQL> EDIT test.ctl
-- then copy and paste from your c:\faq\test.ctl file
SQL> EDIT test.dat
-- then copy and paste from your c:\faq\test.dat file
SQL> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log




Re: Not able to load the from the flat file. [message #382568 is a reply to message #382564] Fri, 23 January 2009 00:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
BlackSwan wrote on Thu, 22 January 2009 22:50
ls -l filename?


He is using Windows, not Unix.
Re: Not able to load the from the flat file. [message #382569 is a reply to message #382563] Fri, 23 January 2009 00:58 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So here comes an example of a SQL*Loader session.

First, I'll create a table which will contain abbreviations of US states:
SQL> connect scott/tiger@ora10
Connected.
SQL> create table states (state varchar2(2));

Table created.

SQL> exit

Now let's check whether both control and input data file exist:
C:\Temp>dir states.*
 Volume in drive C has no label.
 Volume Serial Number is 259A-5DC9

 Directory of C:\Temp

23.01.2009  07:49               108 states.ctl
23.01.2009  07:52                13 states.txt
               2 File(s)            121 bytes
               0 Dir(s)  46.883.004.416 bytes free
Right! They are here!

What's written in there?
C:\Temp>type states.ctl
load data
infile 'c:\temp\states.txt'
replace
into table states
  (state char terminated by whitespace)
C:\Temp>
C:\Temp>type states.txt
AL
AK
AZ

Let's load the file:
C:\Temp>sqlldr scott/tiger@ora10 control=c:\temp\states.ctl log=c:\temp.states.log

SQL*Loader: Release 10.2.0.1.0 - Production on Pet Sij 23 07:53:46 2009

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

Commit point reached - logical record count 3

Finally, did we manage to load these 3 records?
C:\Temp>sqlplus scott/tiger@ora10

SQL> select * from states;

ST
--
AL
AK
AZ

SQL>

It appears that everything went just fine.

Can you reproduce these steps? Try and post the exact output, just as I did.
Re: Not able to load the from the flat file. [message #382607 is a reply to message #382569] Fri, 23 January 2009 03:52 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear All,


SQL> HOST SQLLDR TEST/TEST CONTROL=LOAD1.CTL

SQL*Loader: Release 10.1.0.2.0 - Production on Fri Jan 23 14:35:04 2009

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

SQL*Loader-500: Unable to open file (LOAD1.CTL)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.

SQL> EXIT;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\Documents and Settings\Administrator>DIR C:\ORAFAQ\LOAD.DAT
 Volume in drive C has no label.
 Volume Serial Number is EC8F-43E0

 Directory of C:\ORAFAQ

01/16/2009  04:14 PM        10,337,792 Load.dat
               1 File(s)     10,337,792 bytes
               0 Dir(s)   2,252,877,824 bytes free

C:\Documents and Settings\Administrator>DIR C:\ORAFAQ\LOAD1.CTL
 Volume in drive C has no label.
 Volume Serial Number is EC8F-43E0

 Directory of C:\ORAFAQ

01/23/2009  02:34 PM               365 LOAD1.CTL
               1 File(s)            365 bytes
               0 Dir(s)   2,252,873,728 bytes free

C:\Documents and Settings\Administrator>


But mistakenly my conrol file is stored in test.ctl.txt.Am sorry that i have stored in different extension.But when i saved the control file i have renamed into test.ctl.But automatically .txt extension is added additionally.


Below is my control file.

OPTIONS (ROWS=1000, readsize=12582912, BINDSIZE=12582912, SILENT=FEEDBACK,DISCARDS)
LOAD DATA
infile 'C:\ORAFAQ\LOAD.dat'
APPEND INTO TABLE test
FIELDS TERMINATED BY ''
OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(A,
B,
C,
D,
E,
F,
G,
H)


Actually i am loading data from a excel sheet which has more than sixty five thousand rows.But in some columns nulls are there.

Below is my sample data which is stored in .CSV format


LD1031	264000	0	0	0	3826	0	AUQ	DOV	EIN	880	22-Dec-07
LD1032	264000	0	0	0	4192	0	AUQ	IAD	LEJ	880	22-Dec-07
LD1033	264000	0	0	0	4192	0	AUQ	LEJ	IAD	880	22-Dec-07
LD1034	264000	0	0	0	2792	0	AUQ	VBG	BGR	880	22-Dec-07
LD1035	264000	0	0	0	1487	0	AUQ	YUM	YWG	880	22-Dec-07
LD1036	191198	756	687	0	1507	0	C6	FLL	YQB	614	22-Dec-07
LD1037	35200	132	70	0	1498	0	C6	MCO	YHZ	616	22-Dec-07
LD1038	105600	354	301	0	1498	0	C6	MCO	YHZ	619	22-Dec-07




I have tried the steps which is stated below.

sqlldr test/test control=c:\orafaq\load1.ctl log=c:\orafaq\temp.log



which was sucessfully loaded .

But only three thousand rows were loaded.Is there any thing i missed out in the control file.

I need to thank everyone who gave some ideas on this part .I have exported the data into a notepad with.txt extension and implemented the above steps.All the rows sucessfully loaded.

Thanks to Barbara,Little Foot,Michel,BlackSwan

Thanks and Regards,
Hammer.

[Updated on: Fri, 23 January 2009 04:34]

Report message to a moderator

Re: Not able to load the from the flat file. [message #382624 is a reply to message #382607] Fri, 23 January 2009 04:26 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
But only three thousand rows were loaded.Is there any thing i missed out in the control file.
Check the LOG file - in there you'll find the reason.


P.S.
Quote:
my conrol file is stored in test.ctl.txt
See? Oracle doesn't lie!

[Updated on: Fri, 23 January 2009 04:29]

Report message to a moderator

Previous Topic: Passing file name as parameter from a Concurrent Program, to a control file.
Next Topic: expdb can corrupt data
Goto Forum:
  


Current Time: Tue Dec 06 02:51:33 CST 2016

Total time taken to generate the page: 0.16065 seconds