Re: Converting LONG to NUMERIC

You will have to use PL SQL.

create a plsql column as long read the table's long column into it, and insert the

value into another table where the column type is number.

PLSQL does not care about columns type, I have used this to convert long column to varchar2.

Yigal

ydran_at_usa.net

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!
>
> ^ 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
Received on Thu Apr 09 1998 - 14:11:52 CDT