Re: Converting LONG to NUMERIC

From: Joel Garry <joelga_at_pebble.ml.org>
Date: 10 Apr 1998 17:12:02 -0700
Message-ID: <6gmcgi$vec$1@pebble.ml.org>

In article <6gir8q$c3m$1_at_nnrp1.dejanews.com>, <cal.pampa_at_usa.net> wrote:
>Hello,
>
>I made a mistake when defining one Oracle table: defined one of its columns as
>being of type LONG when it really should have been numeric. I thought "LONG"
>stood for "LONG INTEGER" and later learned it meant a string up to 2 GB in
>size.
>
>Now the table has over 100,000 records and I wish to convert the data type of
>that column and keeping the data in the process...
>
>What I tried, using SQL-Plus:
>
>Creating a new table with the correct structure and populating it with the
>INSERT INTO statement. But that failed, "INCORRECT USE OF LONG DATA TYPE".
>
>Creating a new table with only the primary key and the LONG column, and later
>use the UPDATE statement, with the TO_NUMBER function... this didn't work
>either, with the same message as above.
>
>The only thing that seemed to work was creating a query in MS-Access to create
> a new table with the PK and LONG field, so I could later update the new
>table, but due to the large number of rows, the query had to be interrupted
>after 5 hours of non-stop work...
>
>If someone knows of some efficient way of recovering that data into a new
>table having the correct structure, please let me know...
>
>Thanks in advance!

The export/truncate/redefine/import someone posted would be my choice, but if you want to be able to edit the data while it is out fof the db for some reason, use spool and select to get it to a flat file, then sql*loader to import.

>
>^ Cristian Lucas
>^ From Buenos Aires, Argentina
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading

--
These opinions are my own and not necessarily those of Information Quest

jgarry@eiq.com                           http://www.informationquest.com

http://ourworld.compuserve.com/homepages/joel_garry "See your DBA?" I AM the @#%*& DBA! Received on Fri Apr 10 1998 - 19:12:02 CDT