Re: Converting LONG to NUMERIC

From: Chris Hamilton <toneczar_at_erols.com>
Date: 9 Apr 1998 19:59:12 GMT
Message-ID: <01bd63f1$f5a150e0$73344b9b@chrish.hq.usace.army.mil>

cal.pampa_at_usa.net wrote ...

Hi Cristian,

> 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". There are two approaches.

The easy one (tested under 8.0.3). This will work if ALL values in that LONG column are numbers:

1. Export the table.
2. Truncate the table.
3. Alter the column datatype from LONG to NUMBER (which you can only do if the column is empty).
4. Import the data. The data in the export file goes right into the number field without complaint.

The other method:

1. Rename the table, from MYTAB to MYTAB_OLD (for example).
2. Export the table (just in case).
3. Create a new table (MYTAB) with the same structure except for the NUMBER datatype instead of the LONG.
4. Write a PL/SQL program that opens a cursor to fetch each row from the MYTAB_OLD, then insert it into the new MYTAB table.

This will be much faster than Access, and more reliable.

Chris

---

Chris Hamilton -- christopher.h.hamilton_at_usace.army.mil U.S. Army Corps of Engineers
http://www.serve.com/cowpb/chamilton.html Received on Thu Apr 09 1998 - 14:59:12 CDT