Home » RDBMS Server » Server Utilities » Inserting fixed length data without delimeter
Inserting fixed length data without delimeter [message #322550] Sat, 24 May 2008 07:13 Go to next message
tahir_mehmood_satti
Messages: 4
Registered: August 2005
Location: rwp
Junior Member
Respected all,

I have a file in one line where there is no delimiter e.g(after 191 length the next record starts).
While using fixed length control file it insert one record up to 191 length and left all other records.
Please guide me how i can insert these fixed length continues records in oracle database.
Please Email me on my Email Address
tahir_mehmood_satti@yahoo.com.

Regards,
Tahir mehmood
Re: Inserting fixed length data without delimeter [message #322605 is a reply to message #322550] Sat, 24 May 2008 16:57 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If your database supports this feature, you might try with external table. Your "endless" single-line-file could then be analyzed by (PL/)SQL which enables more flexibility than SQL*Loader.

Probably not the most clever solution, but a straightforward (i.e. understandable) one would look like this:
LOOP
  var = SUBSTR(input, 191 * counter, 191)
  INSERT INTO some_table (column) VALUES (var)
  counter = counter + 1
END LOOP
Re: Inserting fixed length data without delimeter [message #322607 is a reply to message #322605] Sat, 24 May 2008 21:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/ldr_concepts.htm#sthref714
Re: Inserting fixed length data without delimeter [message #322608 is a reply to message #322550] Sat, 24 May 2008 21:59 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>While using fixed length control file it insert one record up to 191 length and left all other records.
Then most likely the control file was incorrect for the input data file.
Re: Inserting fixed length data without delimeter [message #322727 is a reply to message #322607] Mon, 26 May 2008 01:54 Go to previous messageGo to next message
tahir_mehmood_satti
Messages: 4
Registered: August 2005
Location: rwp
Junior Member
Sir,
Thanks for your cooporation actually my problem is that i have a data file in one line with variable data and there is no Record decimeter or line separator.And i just want to save these record in oracle data base the data structure is give below
*******************************
f1 VARCHAR2(1)
f2 VARCHAR2(1)
f3 VARCHAR2(24)
f4 VARCHAR2(15)
f5 VARCHAR2(15)
f6 VARCHAR2(10)
f7 VARCHAR2(10)
f8 VARCHAR2(24)
f9 VARCHAR2(10)
f10 VARCHAR2(3)
f11 VARCHAR2(3)
f12 VARCHAR2(7)
f13 VARCHAR2(4)
f14 VARCHAR2(3)
f15 VARCHAR2(3)
f16 VARCHAR2(7)
f17 VARCHAR2(1)
f18 VARCHAR2(1)
f19 VARCHAR2(1)
f20 VARCHAR2(24)
ORIG_CALLED VARCHAR2(24)
HDR VARCHAR2(95)

Regards,
Re: Inserting fixed length data without delimeter [message #322739 is a reply to message #322727] Mon, 26 May 2008 02:16 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
i have a data file in one line with variable data and there is no Record decimeter or line separator

If that's the case, I'm afraid that there's nothing you can do about it. How could you "automatize" the job (i.e. let a program do it) if you can't tell where one record ends and another one begins?
No fixed length + no delimiter = I don't see a way to do that
Re: Inserting fixed length data without delimeter [message #322750 is a reply to message #322739] Mon, 26 May 2008 02:35 Go to previous messageGo to next message
tahir_mehmood_satti
Messages: 4
Registered: August 2005
Location: rwp
Junior Member
Sir
As i mention then the size of one Record is 191 bytes after that second recod starts but there is no line delimeter.As in case of Sql Server we use BCP command please tell me if there is any command for Oracle SQL Loader.
Thanking u in Advance.
Ragards,
Re: Inserting fixed length data without delimeter [message #322876 is a reply to message #322750] Mon, 26 May 2008 09:48 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Specifying the Position of a Data Field
Re: Inserting fixed length data without delimeter [message #322878 is a reply to message #322750] Mon, 26 May 2008 09:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You can use:

INFILE datafile_name "fix 191"

in your SQL*Loader control file, as stated in the link that I provided previously to specify where the record ends, but you will still need something, either a position or a delimiter to determine where one field ends and the next begins.

[Updated on: Mon, 26 May 2008 09:55]

Report message to a moderator

Re: Inserting fixed length data without delimeter [message #322970 is a reply to message #322878] Mon, 26 May 2008 23:50 Go to previous message
tahir_mehmood_satti
Messages: 4
Registered: August 2005
Location: rwp
Junior Member
Respected Sir,
Thank u very much for your Cooperation and such an nice effort regarding my problm.
Wishing u best of luck in your future and in your whole life.
Bye and Take care
Previous Topic: SQL LOADER (Oracle 10g)
Next Topic: EXP-00003: no storage definition found in export
Goto Forum:
  


Current Time: Fri Dec 09 22:55:26 CST 2016

Total time taken to generate the page: 0.09583 seconds