Re: Optimize Table

On Feb 1, 4:03 am, mansoor..._at_gmail.com wrote:
> On Feb 1, 4:22 pm, "news.verizon.net" <kenned..._at_verizon.net> 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 http://asktom.oracle.com/tkyte/flat/

jg

--
@home.com is bogus.
"Lubrication, care and maintenance of tube expanders" huh huh
http://www.specsales.com.au/html/teco_tube_tools.html