Re: Converting LONG to NUMERIC

Hi Cristian,

PL/SQL can do the trick. First, create a new table defined as you originally intended. Next, write a PL/SQL cursor loop to retrieve all rows from the old table, read the LONG column values into a LONG variables, convert them to NUMBER, and store the result in your new table. For example, suppose the old table is:

SQL> DESC OLDTABLE

 Name                            Null?    Type
 ------------------------------- -------- ----
 PK                                       VARCHAR2(10)
 TEXT                                     LONG

(PK being the primary key), then your new table would be:

SQL> DESC NEWTABLE

 Name                            Null?    Type
 ------------------------------- -------- ----
 PK                                       VARCHAR2(10)
 NR                                       NUMBER

Now, create a loop like:
DECLARE
   CURSOR cur IS SELECT pk, text FROM oldtable;    cText LONG;
   nNr NUMBER;
BEGIN
  FOR rec IN cur LOOP
    cText := rec.text;
    nNr := TO_NUMBER(cText);
    INSERT INTO newtable VALUES (rec.pk, nNr);   END LOOP;
END;
/

Unlike LONG columns, PL/SQL LONG variables can be manipulated. You may have to do some extra work to handle ORA-06502 is case some of your LONG columns cannot be converted; also, when converting 100,000 rows, check your rollback segments.

Good luck,

Paul.

cal.pampa_at_usa.net wrote in article <6gir8q$c3m$1_at_nnrp1.dejanews.com>...
> 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 - 12:42:35 CDT