Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: LONG DATATYPE
Thomas,
I thought that you would have to use DBMS_SQL.COLUMN_VALUE_LONG to pull out the value and assign it to a VARCHAR before you could use SUBSTR...
Jay!!!
Thomas Kyte wrote:
> A copy of this was sent to D S Tharpe <i5t_at_ornl.gov>
> (if that email address didn't require changing)
> On Tue, 03 Nov 1998 11:05:39 -0500, you wrote:
>
> >How can I take all the data from a table that has a field in it
> >defined as a long datatype and put it into a table that has the same
> >fields except for the long will now be going into a field that is 240
> >characters?
> >
> >
> >FROM TABLE
> > field 1 varchar2(10)
> > field 2 date
> > field 3 long
> >
> >
> >TO TABLE
> > field 1 varchar2(10)
> > field 2 date
> > field 3 varchar2(240)
>
> if all of the fields in field3 in the original table are 32k and less, then:
>
> begin
> for x in ( select * from old_table ) loop
> insert into new_table values ( x.field1, x.field2, substr(x.field3,1,240));
> end loop;
> end;
>
> >
> >TIA,
> >
> >D. Tharpe
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Government
> Herndon VA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
> Anti-Anti Spam Msg: if you want an answer emailed to you,
> you have to make it easy to get email to you. Any bounced
> email will be treated the same way i treat SPAM-- I delete it.
Received on Tue Nov 03 1998 - 12:33:21 CST