Re: Help with Long Column Problem

gamebuddah_at_hotmail.com (Roger Carden) wrote in message news:<fbd13bc3.0108070638.2e36f0dd_at_posting.google.com>...
> Hello Everyone,
>
> I'm trying to move data between two databases and one of the columns
> that needs to be moved is a long. Currently I have a cursor which
> selects all the values from the remote database table (including the
> long column), runs through the lst, and inserts the data into the
> local database. This works fine if the value of the long column is
> under 32760 bytes.
>
> However, one of the rows in the remote database has a column which is
> currently over 45000 bytes! When I include this row in my cursor, the
> procedure bombs, however when I exclude it, the procedure moves all
> the other data.
>
> First: Is there anyway to move this data from within PL/SQL? (I am
> aware that I may be able to use the COPY command from SQL/PLUS but
> this will not meet the requirements that I have for this job.)
>
> Second: If there isn't a way to move it, is there a way to check the
> length of Long columns and exclude any over 32760 from my cursor?
>
> Any help would be greatly appreciated.
>
> Thanks,
> Roger Carden
> gamebuddah_at_hotmail.com

First: No, that's one of the reasons why Oracle invented LOBs. It is a pity you can't'/won't use the sql*plus copy command, it works like a charm.
Second: There is a way to move it by writing a pro*c program. Thomas Kyte
(http://asktom.oracle.com) should have an example on his site. Basically you need to process the long in chunks of no more than 32k. You could try to use dump or vsize to check the length, but they might not accept a long as parameter.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Wed Aug 08 2001 - 13:44:51 CEST