OraFAQ Forum: Server Utilities » Not able to load the from the flat file.

Home » RDBMS Server » Server Utilities » Not able to load the from the flat file. (Oracle 10g)

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

Not able to load the from the flat file. [message #381317]

Fri, 16 January 2009 05:41

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.

Report message to a moderator

Re: Not able to load the from the flat file. [message #381329 is a reply to message #381317]

Fri, 16 January 2009 06:43

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

Senior Member
Account Moderator

Add the parameter READSIZE.

Regards
Michel

Report message to a moderator

Re: Not able to load the from the flat file. [message #382100 is a reply to message #381329]

Wed, 21 January 2009 00:00

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/toc.htm

Report message to a moderator

Re: Not able to load the from the flat file. [message #382118 is a reply to message #382100]

Wed, 21 January 2009 00:42

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

Senior Member
Account Moderator

The link is under the READSIZE word, just click on it.

Regards
Michel

Report message to a moderator

Re: Not able to load the from the flat file. [message #382168 is a reply to message #382118]

Wed, 21 January 2009 04:01

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.

Report message to a moderator

Re: Not able to load the from the flat file. [message #382181 is a reply to message #382168]

Wed, 21 January 2009 04:42

Michel Cadot
Messages: 68647
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

Report message to a moderator

Re: Not able to load the from the flat file. [message #382184 is a reply to message #382181]

Wed, 21 January 2009 04:47

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.

Report message to a moderator

Re: Not able to load the from the flat file. [message #382187 is a reply to message #382184]

Wed, 21 January 2009 04:51

Michel Cadot
Messages: 68647
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

Report message to a moderator

Re: Not able to load the from the flat file. [message #382556 is a reply to message #382187]

Fri, 23 January 2009 00:21

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

Report message to a moderator

Re: Not able to load the from the flat file. [message #382558 is a reply to message #382556]

Fri, 23 January 2009 00:38

Michel Cadot
Messages: 68647
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

Report message to a moderator

Re: Not able to load the from the flat file. [message #382563 is a reply to message #382558]

Fri, 23 January 2009 00:49

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.

Report message to a moderator

Re: Not able to load the from the flat file. [message #382564 is a reply to message #381317]

Fri, 23 January 2009 00:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ls -l filename?

Report message to a moderator

Re: Not able to load the from the flat file. [message #382565 is a reply to message #382556]

Fri, 23 January 2009 00:52

Barbara Boehmer
Messages: 9090
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

Report message to a moderator

Re: Not able to load the from the flat file. [message #382568 is a reply to message #382564]

Fri, 23 January 2009 00:55

Barbara Boehmer
Messages: 9090
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.

Report message to a moderator

Re: Not able to load the from the flat file. [message #382569 is a reply to message #382563]

Fri, 23 January 2009 00:58

Littlefoot
Messages: 21808
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.

Report message to a moderator

Re: Not able to load the from the flat file. [message #382607 is a reply to message #382569]

Fri, 23 January 2009 03:52

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