Home » RDBMS Server » Server Utilities » Sqlldr issue with Oracle 8i  () 1 Vote
icon9.gif  Sqlldr issue with Oracle 8i [message #199998] Fri, 27 October 2006 06:11 Go to next message
sherinpearl
Messages: 6
Registered: October 2006
Junior Member
I'm loading a dynamically created CSV file to a database table using sqlldr. The csv file has the fist line blank (which cannot be avoided). In Oracle 9i sqlldr loads this file. But in Oracle 8i, sqlldr gives me an error :

SQL*Loader-510: Physical record in data file (D:\RSEnduInterface\RSJavaService\HourlyStationGenFile.csv) is longer than the maximum(1048576)

I have tried SKIP thinking that it will skip the blank line, but it didn't. If i remove the first blank line & then try with Oracle 8i, it gets loaded without any problems. Please advise on how to load this file using oracle 8i.
Re: Sqlldr issue with Oracle 8i [message #200001 is a reply to message #199998] Fri, 27 October 2006 06:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> The csv file has the fist line blank (which cannot be avoided).
Why not? Skip=1 will work.
oracle@mutation#cat t1.dat

0000009921
0000009922
0000009923
0000009924
oracle@mutation#wc -l t1.dat
       5 t1.dat


oracle@mutation#cat somectl.ctl
LOAD DATA
infile 't1.dat'
replace
INTO TABLE t1
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
n decimal external
)

oracle@mutation#sqlldr userid=scott/tiger control=somectl.ctl skip=1

SQL*Loader: Release 9.2.0.7.0 - Production on Fri Oct 27 07:23:33 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 4
oracle@mutation#query mutation scott.t1

         N
----------
      9921
      9922
      9923
      9924

[Updated on: Fri, 27 October 2006 06:22]

Report message to a moderator

Re: Sqlldr issue with Oracle 8i [message #200005 is a reply to message #199998] Fri, 27 October 2006 06:41 Go to previous messageGo to next message
sherinpearl
Messages: 6
Registered: October 2006
Junior Member
Thanks for the reply. I have tried skip = 1. It works if the first line has atleast a whitespace, but when i tried with the first line having only a newline character(Pressed 'ENTER' in Textpad) skip doesn't work, rather sqlldr gives me error.

My control files looks like this :


load data
infile 'D:\RSEnduInterface\RSJavaService\HourlyFuelFile.csv'
into table USER_RS_HOURLY_FUEL_TBL
fields terminated by "," optionally enclosed by '"'
( fuel_name CHAR, iteration INTEGER EXTERNAL, fuel_date "to_date(:fuel_date,'DD/MM/YYYY')", hb CHAR, fuel_used DECIMAL EXTERNAL )



Please help
Re: Sqlldr issue with Oracle 8i [message #200009 is a reply to message #200005] Fri, 27 October 2006 06:54 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
post a sample datafile.
>>gives me error.
What error?
Re: Sqlldr issue with Oracle 8i [message #200013 is a reply to message #199998] Fri, 27 October 2006 07:14 Go to previous messageGo to next message
sherinpearl
Messages: 6
Registered: October 2006
Junior Member

Following is the error i have got :



SQL*Loader-510: Physical record in data file (D:\RSEnduInterface\RSJavaService\HourlyFuelFile.csv) is longer than the maximum(1048576)



I have attached a sample data file(HourlyFuelFile.csv)

My table schema is :
CREATE TABLE user_rs_hourly_fuel_tbl (
fuel_name VARCHAR2(50) NOT NULL,
iteration NUMBER NULL,
fuel_date DATE NULL,
hb VARCHAR2(20) NULL,
fuel_used FLOAT(126) NULL
)

Re: Sqlldr issue with Oracle 8i [message #200018 is a reply to message #200013] Fri, 27 October 2006 07:37 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I do not have an 8i binary around.
I cannot reroduce the same error in 9i.
But it seems that the files have ^M characters appended. Use any of methods discussed here.
http://www.orafaq.com/forum/m/142974/42800/?srch=remove+control+characters#msg_142974

I used this csv file ( uploaded) and was able to load it into database.
  • Attachment: newcsv.csv
    (Size: 1.09KB, Downloaded 256 times)

[Updated on: Fri, 27 October 2006 08:28]

Report message to a moderator

Re: Sqlldr issue with Oracle 8i [message #200241 is a reply to message #199998] Sun, 29 October 2006 22:14 Go to previous messageGo to next message
sherinpearl
Messages: 6
Registered: October 2006
Junior Member
I'm not working on a Unix machine, But on a windows machine. Anyway removing ^M characters didn't help. I have tried loading the file in both Oracle 8i & Oracle 9i. It works perfectly in Oracle 9i. But the problem occurs when trying to load with Oracle 8i. My development environment has Oracle 9i, But in production, it's Oracle 8i. So i need to solve this issue somehow & it's very urgent.. Please help.
Re: Sqlldr issue with Oracle 8i [message #200263 is a reply to message #200241] Mon, 30 October 2006 02:56 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please check metalink note Note:165317.1.
Seems to be your case. You need to set the attributes.
Re: Sqlldr issue with Oracle 8i [message #200276 is a reply to message #199998] Mon, 30 October 2006 03:37 Go to previous messageGo to next message
sherinpearl
Messages: 6
Registered: October 2006
Junior Member
Thank you so much for your reply. I'm not able to find Note:165317.1. It gives me the same page. Could you please post the link
Re: Sqlldr issue with Oracle 8i [message #200333 is a reply to message #200276] Mon, 30 October 2006 06:55 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You are lucky Smile. As of now, i could not even login into metalink.
Seems it is down.
And just a suggestion ( as i myself had done this before)
Did you search for 165317.1. or
165317.1 ?
Thats was typo in my previous post.Try it without the trailing period. Apologies.

[Updated on: Mon, 30 October 2006 09:05]

Report message to a moderator

Re: Sqlldr issue with Oracle 8i [message #200394 is a reply to message #200333] Mon, 30 October 2006 12:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
https://metalink.oracle.com/metalink/plsql/f?p=130:3:4084359846954528120::::p3_database_id,p3_docid,p3_show_header,p3_show_help,p3_bl ack_frame,p3_font:NOT,165317.1,1,1,1,helvetica
Re: Sqlldr issue with Oracle 8i [message #200440 is a reply to message #199998] Mon, 30 October 2006 23:01 Go to previous messageGo to next message
sherinpearl
Messages: 6
Registered: October 2006
Junior Member
Sorry for the trouble, but i'm not able to login to Metalink.
Could you please sent the contents of the page to sherinpearl@yahoo.com or post the content of tht page here?
Re: Sqlldr issue with Oracle 8i [message #200467 is a reply to message #200440] Tue, 31 October 2006 01:00 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I can't. The contents are copyrighted.

[Updated on: Tue, 31 October 2006 01:13]

Report message to a moderator

Previous Topic: Sql Loader problem
Next Topic: SQL*Plus Paths on Windows
Goto Forum:
  


Current Time: Fri Dec 02 16:18:43 CST 2016

Total time taken to generate the page: 0.18347 seconds