Re: How to load data from flatfile to Oracle

[Alan Ljungberg <Alan.Ljungberg@CNPL.ENBRIDGE.COM>]

Converting and loading a flatfile is a slow process since it processes and
reads and inserts row by row line by line.

We are working on a similar conversion here at this project.  We originally
looked at this type of process to convert from DB2 to Oracle however we
found to too slow.  For the volume of data we were dealing with it would
take days maybe weeks to run all the conversion scripts.

If you are only moving a small amount a handful of tables of data 100k rows
or so, with little data conversion the flat file approach should be fine.
We used SQLoader to load data into copies of the source tables in Oracle.
Then we just wrote conversion SQRs that copied and converted the data from
Oracle to Oracle.  Still not really fast but easily 100 times more so than
SQR reading converting flatfiles and inserting row by row.

Ask your Oracle DBAs about SQLoader they will know about it.

-Alan.






                    "Knapp,
                    Richard"              To:     SQR-USERS@list.iex.net
                    <KnappR@UMSYST        cc:
                    EM.EDU>               Subject:     Re: How to load data 
from flatfile to Oracle
                    Sent by:           Expire On:
                    "Discussion of
                    SQR, Brio
                    Software's
                    database
                    reporting
                    language"
                    <SQR-USERS@lis
                    t.iex.net>


                    09/05/2002
                    03:24 PM
                    Please respond
                    to sqr-users





I'm doubting anybody else's code is going to help you here.  The first step
is to analyze the legacy data then design your new database (Oracle) then
get as much info from the functional folks about how they want the
conversion done as you can eg what legacy codes become which Oracle codes,
etc..  With this in hand, open the flat file, create a loop, read in the
rows one at a time, evaluate, implement the conversion rules, insert the
Oracle row(s).

Richard Knapp
Database Programmer/Analyst
Administrative Systems Project
University of Missouri System
573-884-3157
knappr@umsystem.edu


-----Original Message-----
From: kirthi ajad [mailto:mymvs999@YAHOO.COM]
Sent: Thursday, September 05, 2002 4:02 PM
To: SQR-USERS@list.iex.net
Subject: How to load data from flatfile to Oracle


 Hi all,
        I am very new to SQR.I have data in text format. I have to load
data into
Oracle 8 by using  SQR ( thru flat file and the existing data is in
Informix. We are going out from informix and implementing for same in
Oracle) to load data into Oracle . Is there any option to load data from
SQR? Please help me in this regard.

If any one has sample code that would be great help to me.


 Thanks.




---------------------------------
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes