Re: Optimize Table

From: joel garry <>
Date: Fri, 1 Feb 2008 14:19:46 -0800 (PST)
Message-ID: <>

On Feb 1, 4:03 am, wrote:
> On Feb 1, 4:22 pm, "" <> wrote:

> > One more thing. How do u load a text file ( feilds delimited by " and
> > rows by newline) into a given table.
> > I know external table is involved but dont you have to specify the
> > columns in the CREATE TABLE statement.
> > I cant do that because I dont know the columns. How to create the
> > external table from a given table and read data from the text file to
> > it.
> > Thx.
> > You don't need the optimize table statement to convert it to Oracle.  You
> > want to convert a text file with specific delimited characters without
> > knowing the file definition first.  Okay.  How do you choose the data types
> > for the columns?  Do you just make everything varchar2(4000)?
> > You can use sqlloader or an external table to get the data loaded, but you
> > need to give the definition of the columns, which ones are NULL, what are
> > their types are there any constraints. (primary key, unique and others)
> > You could certainly write something to take a file name, parse it out and
> > create a table.  (embedding your business rules) You could write it in
> > pl/sql using the utl_file package and dbms_sql package.  You could use APEX
> > which would give you a GUI to import a file.  It makes a guess and you can
> > define columns etc.  It is a wizard like approuch.
> > Jim
> The definition of columns is in the target table where I want to put
> the data. Lets say the columns in the text file match those of the
> table but I cant create an external table by defining those columns.
> Is there any other way to create an external table without defining
> columns? create the external table exactly like the target table. If
> its possible then I simply get the data inserted into the original
> table like
> insert into target_tbl
> select * from external_tbl
> If there is a simpler way without creating an external table that
> would be great.
> thx

Tom Kyte wrote a script that, among other things, comes up with the sql*loader script for your table. See


-- is bogus.
"Lubrication, care and maintenance of tube expanders" huh huh
Received on Fri Feb 01 2008 - 16:19:46 CST

Original text of this message