Re: How to get data into Oracle over a mediocre network connection

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 02 Jun 2007 17:37:24 -0700
Message-ID: <1180831042.775627@bubbleator.drizzle.com>

Martin T. wrote:
> On Jun 2, 5:36 pm, DA Morgan <damor..._at_psoug.org> wrote:

>> Martin T. wrote:
>>> On Jun 1, 11:01 pm, joel garry <joel-ga..._at_home.com> wrote:
>>>> On Jun 1, 4:58 am, "Martin T." <bilbothebagginsb..._at_freenet.de> wrote:
>>>>> On Jun 1, 12:01 am, joel garry <joel-ga..._at_home.com> wrote:
>>>>>> On May 31, 8:39 am, "Martin T." <bilbothebagginsb..._at_freenet.de>
>>>>>> wrote:
>>>>> (...)
>>>>> We have now tried out the solution with SQLLoader and it seems that
>>>>> this is indeed the tool for the job. Using SQLLoader we see (nearly)
>>>>> the same performance as with a simple copy over the network.
>>>>> So it will be SQLLoader + PL/SQL script that copies the data from the
>>>>> temporary table to the real target table.
>>>> What is it that makes you need PL/SQL over plain old SQL?
>>>> jg
>>> Hm ... I guess it's that we want to UPDATE the actual table with the
>>> data that we put into the temp table. (And PL/SQL is also plain enough
>>> for me :-)
>>> cheers,
>>> Martin
>> Enough perhaps but far less efficient which, I believe, was the point
>> of Joel's statement. SQL rather than PL/SQL is a good guideline.
>> --
>> Daniel A. Morgan

>
> Hm. Really? Surely a *single* SQL stmt will be more efficient than a
> PL/SQL block, but if I have to use an SQL script that runs dozens of
> SQL stmts I'm not too sure that it will make a big difference to a PL/
> SQL block (with dozens of stmts).
>
> whatever, there's a time for every tool ... :)
>
> cheers,
> Martin

I think you misinterpreted what I intended. Better:

BEGIN
   SELECT
   INSERT
   UPDATE
   DELETE
   COMMIT
END; then

BEGIN
   FOR r IN cursor LOOP
END; Sorry if that wasn't clear.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sat Jun 02 2007 - 19:37:24 CDT