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 |
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 #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.
|
|
|
|
|
|
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
|
|
|
|
|
|
|
|
|
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
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 16:15:54 CDT 2024
|