OraFAQ Forum: Server Utilities » Inserting fixed length data without delimeter

Home » RDBMS Server » Server Utilities » Inserting fixed length data without delimeter

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

Inserting fixed length data without delimeter [message #322550]

Sat, 24 May 2008 07:13

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

Report message to a moderator

Re: Inserting fixed length data without delimeter [message #322605 is a reply to message #322550]

Sat, 24 May 2008 16:57

Littlefoot
Messages: 21823
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

Report message to a moderator

Re: Inserting fixed length data without delimeter [message #322607 is a reply to message #322605]

Sat, 24 May 2008 21:24

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/ldr_concepts.htm#sthref714

Report message to a moderator

Re: Inserting fixed length data without delimeter [message #322608 is a reply to message #322550]

Sat, 24 May 2008 21:59

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Inserting fixed length data without delimeter [message #322727 is a reply to message #322607]

Mon, 26 May 2008 01:54

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,

Report message to a moderator

Re: Inserting fixed length data without delimeter [message #322739 is a reply to message #322727]

Mon, 26 May 2008 02:16

Littlefoot
Messages: 21823
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

Report message to a moderator

Re: Inserting fixed length data without delimeter [message #322750 is a reply to message #322739]

Mon, 26 May 2008 02:35

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,

Report message to a moderator

Re: Inserting fixed length data without delimeter [message #322876 is a reply to message #322750]

Mon, 26 May 2008 09:48

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Specifying the Position of a Data Field

Report message to a moderator

Re: Inserting fixed length data without delimeter [message #322878 is a reply to message #322750]

Mon, 26 May 2008 09:52

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

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

Report message to a moderator

Previous Topic:	SQL LOADER (Oracle 10g)
Next Topic:	EXP-00003: no storage definition found in export

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 05:56:57 CST 2024