Re: remote database and cursor

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 24 Sep 2006 22:35:06 -0700
Message-ID: <1159162505.752199@bubbleator.drizzle.com>

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.

And the value of writing less than optimal code is?

If you learn to write the most efficient code all of the time ... you will be writing the most efficient code all of the time. Assuming you are not writing code for a single-user system the question you should consider is what will happen when multiple users execute the code at the same point-in-time.

I'd suggest you rethink your objection as the difference between the two is that it takes less code to do it more efficiently.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

Received on Mon Sep 25 2006 - 00:35:06 CDT