Re: Need to move a long column to it's own table

I'll have to go back and check this, but I think you will find that you aren't copying the whole of your long. (unless it's a short long :). There is (used to be ?) a 32K limit to the size of a 'long' in PL/SQL, so you may only be copying the first 32K of each long in your loop.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Jeff Guttadauro wrote in message <37b1c71c.21962510_at_news>...
>Hello.
>
>I first created the other table...
>
>create table table2 (a number(10), b long);
>
>Then, this PL/SQL block worked for me:
>
>declare
> cursor long_cursor is select
> keyfield, longfield from table1;
>
>begin
> for i in long_cursor
> loop
> insert into table2 values (i.keyfield, i.longfield);
> end loop;
> commit;
>end;
>
>Hope this helps,
>-Jeff Guttadauro
>
>On Wed, 11 Aug 1999 18:57:56 +0200, "Sybrand Bakker"
><postmaster_at_sybrandb.demon.nl> wrote:
>
>>Hi all,
>>
>>Using Oracle 7.3.4 on NT4 sp3 at a customer, my firm designed an
application
>>with a long column in the most-heavily used table. The average record
length
>>is 42k, and we identified the presence of the long as the cause.
>>The solution seems to be easy: create a separate table and have the
primary
>>key of the source table and the long there.
>>So far so good. I know this question must have been answered before, and I
>>did research the deja archives: how to get the data their. Create table as
>>select won't work, insert select won't work, etc. I rather don't want to
>>write an OCI program to accomplish this, and there doesn't seem to be a
>>solution in PL/SQL.
>>
>>Any comments...
>>
>>Thanks,
>>
>>Sybrand Bakker, Oracle DBA
>>
>>
>>
>
Received on Wed Aug 11 1999 - 15:50:45 CDT