Re: Help with Long Column Problem

postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote in message news:<a20d28ee.0108080344.3c8e6238_at_posting.google.com>...
> 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

Sybrand,

Thanks for the response. I've used the COPY command on several occasions for one time moves and it has worked flawlessly. However, this time the customer requirement is that the data synchronization be done using internally scheduled oracle jobs (via the DBMS_JOB package). If I can't find a way to overcome this, I may have to use an OS job running a SQL*PLUS script using the COPY command. (The requirement is that the data be synchronized every five minutes or less.)

I'd like to stay away from this if possible because Windows NT's batch file scheduling is limited and the aforementioned customer requirement. Received on Thu Aug 09 2001 - 14:35:37 CEST