Re: Assorted questions about uploading a flat ascii text file ported from an IBM mainframe

Another question is how much time may it take. You could write a PL/SQL package to use the dbms packages for file interaction but that might prove to be very slow on very big tables.

If we are talking about > 10000 line then probably SQL*Loader is becoming interesting. It is by far the fastest way to read flat file into Oracle. Advantage is also that you can very easily change you file format and the SQL*Loader load file. If it comes to speed my opinion is that SQL*Loader is unbeatable.

Greetz

Ron

damorgan <dan.morgan_at_ci.seattle.wa.us> wrote in message news:<3C642A65.1DDD087E_at_ci.seattle.wa.us>...
> 1. how much data? One time only or repeated loads. I like UTL_FILE for ongong
> jobs with small to moderate amounts of data because it allows the work to be
> scheduled using DBMS_JOBS and error logging to be done inside the database. I
> like SQL*Plus for one-time only and very large loads.
>
> 2. I like DBMS_UTILITY.COMMA_TO_TABLE but that is just a personal preference
> because it is remarkably easy to implement.
>
> Daniel Morgan
>
>
>
> george lewycky wrote:
>
> > Assorted questions about uploading a flat ascii text file ported from
> > an IBM mainframe
> >
> > 1. What is the best/easiest method or utility for uploading the data
> > to an Oracle database (running on Win NT). I&#8217;m hearing and
> > finding out about more than I expected.
> >
> > I&#8217;d appreciate hearing about past experiences, the pro&#8217;s
> > and con&#8217;s, web sites, etc
> >
> > I&#8217;ve been told a mixture of the following: UTL_File, SQL*PLUS,
> > Discoverer, Export/Import, Migration Workbench (OTN), SQLLOADER,
> > Oracle Bulk Loader
> >
> > 2. Parsing the fields (eg, comma or &#8220;,&#8221;). Which works best
> >
> > create table customer
> > ( authcust char(05) not null,
> > name char(25) not null,
> > address1 char(25),
> > address2 char(25),
> > address3 char(25),
> > address4 char(25),
> > citycountry char(18),
> > state char(02),
> > zip char(05),
> > phone char(10),
> > contact char(25) )
> > ;
> >
> > Below is a sample of the flat file that I parsed with double colons
> > and a comma since spaces and other characters exist.
> >
> > "10066","ABC BUS MANUFACTURING ","1506 30TH STREET
> > ","NORTHWES
> > "10068","ABC RAIL PRODUCTS CORP. ","11TH & WASHINGTON STREETS","
> > "10070","ABCO REFRIGERATION SUPPLY","49-70 31ST STREET ","
> > "10090","ABEE PRINTING CORP. ","305-1 KNICKERBOCKER AVE. ","
> >
> > Thanks in Advance
> >
> > George R Lewycky
> > NY City Transit Authority
> > Brooklyn NYC USA
> >
> > gelewyc_at_nyct.com
Received on Sun Mar 03 2002 - 21:48:24 CET