Re: remote database and cursor

Daud wrote:
> DA Morgan wrote:
> > Daud wrote:
> > > I have the procedure below. It retrieves rows from a table at a remote
> > > database (over WAN link) and inserts into a local table. My question
> > > is, at Point A (where the cursor to remote table has been opened) is it
> > > possible for a glitch in the WAN to cause my procedure to fail?
> >
> > Yes.
> >
> > But unless you have a paleolithic version of Oracle this is a poor
> > use of a cursor loop. Look to using array processing with BULK COLLECT
> > and FORALL.
> >
> > Demos under Array Processing in Morgan's Library at www.psoug.org
> > --
> > Daniel Morgan
> > University of Washington
> > Puget Sound Oracle Users Group
>
> How does it work with cursor? It doesn't pull all the records from the
> remote table in one go?
> I wouldn't use array processing ... no point when I am retrieving a few
> rows only most of the time.

The code you submitted pulls one record at a time. Even if you only plan to retrieving only a few records, you should reconsider if the number of records is greater than 1. This is because every fetch includes a free sql*net roundtrip (in a roundtrip both sides need to confirm to each other they are available again). What you are doing now is converting a
insert ...
select from <remote table>
in a cursor for loop.
This is way less efficient, even in a local database, let alone when you are doing this distributed.
Hopefully you aren't trying to make your code as inefficient as possible.

-- 
Sybrand Bakker
Senior Oreacle DBA