RE: SQL Loader Problem

From: Figueiredo, Pedro <PFigueiredo_at_europeantelecom.at>
Date: Sat, 2 Sep 2000 00:18:10 -0000
Message-Id: <10606.116106@fatcity.com>

Hi,

I had a similar situation migrating from sqlserver7 to oracle8. My basic problem was with carriage returns. The quotes were OK as I was using text delimiters.

I'll describe what I've done:

exported from sqlserver7 with field delimiter (ex. <end>) and row delimiter (ex. <row>\n --> maintaining the paragraph and putting an additional row control string)

bcp DB.usr.customer out d:\ddrive\CUSTOMER.dat -w -t "<end>" -r "<row>\n"
-U" " -P"" -S ntsqlserver

ftp the files to UNIX host (ascii)
Process .dat with vi. ^M were on the file. vi CUSTOMER.dat :1,$ v/<row>^M/j --> this command joins all the lines with the fake CR. The lines that have <row>^M are real end of record. Repeat this command until no more substitions are done (^M is Ctrl-V + Ctrl-M).

:1,$ s/^M/<prg>/g --> substitute ^M by <prg>. Will need them later. :1,$ s/<row><prg>/<end>/g --> take off the <prg> marks from real records. Notice that they were initially <row>^M.

Load into DB Use .ctl file like this one:

load data
infile 'CUSTOMER.dat'
into table CUSTOMER
fields terminated by '<end>'
(MANDATORID ,
CUSTOMERID ,
COMPANYFORM "replace(:COMPANYFORM,'<prg>',chr(10))" ANNOTATION CHAR(1000) "replace(:ANNOTATION,'<prg>',chr(10))" )

<prg> are substituted for CR (chr(10)) when the records are loaded. Default buffer for sql*loader is 255 characters. For larger fiels we have to specify how much we need to use (ex. CHAR(1000)).

Well... it works.

Pedro Figueiredo

-----Original Message-----

From: Weaver, Walt [mailto:wweaver_at_rightnowtech.com] Sent: sexta-feira, 1 de Setembro de 2000 21:59 To: Multiple recipients of list ORACLE-L Subject: RE: SQL Loader Problem

I've faced the same problems using sqlloader against large amounts of formatted data, migrating data from MySQL to Oracle. I solved my problems by exporting the MySQL data using a special record delimeter, then specifying the delimiter as a hex string in the control file. Since you're on 8i you'll be able to do this - it's a new feature, I believe.

--Walt Weaver

Bozeman, Montana, USA

-----Original Message-----

Sent: Thursday, August 31, 2000 4:36 PM
To: Multiple recipients of list ORACLE-L

Dear all,
I am facing lot of problems in loading the data from flat files(generated from progress database) to Oracle 8i database. One of the columns in the data contains a text(maximum 4000 characters) which contains some formatted(carriage return keyssingle quotes ,double quotes etc..) data
One of my friends helped me a lot in reaching out a near solution for this. I am facing difficulty when there is a huge data as I will have to do lot of formatting in the dat file.
At present I am managing with the help of Sql Server(MS)'s DTS tool.But it is rejecting lot of records saying buffer size is small.I am not finding options to increase the buffer size.May be it is OS/Sql Sqrver related. I would like to know if any of you had problems in loading such data and how you overcome.Pl suggest me if there are any links or third party tools available.
Your help is highly appreciated.

Suren
Oracle DBA,Entegram

Get Your Free Email At, http://www.rediffmail.com

Partcipate in crazy Re.1 auctions at http://www.rediff.com/auctions

--

Author: surendra tirumala
INET: surendra_tirumala_at_rediffmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Author: Weaver, Walt
INET: wweaver_at_rightnowtech.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message