Re: Oracle For Fun

In article <1075232332.172716_at_yasure>, damorgan_at_x.washington.edu says...

<snip>
>
> Since I blew I'll post a solution now ... I was thinking in terms of
> some variation on the following:
>
> CREATE OR REPLACE PROCEDURE fast_proc (
> p_array_size IN PLS_INTEGER DEFAULT 100)
> IS
>
> TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
> l_data ARRAY;
>
> CURSOR c IS
> SELECT *
> FROM all_objects;
>
> BEGIN
> OPEN c;
> LOOP
> FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;
>
> FORALL i IN 1..l_data.COUNT
> INSERT INTO t2 VALUES l_data(i);
>
> EXIT WHEN c%NOTFOUND;
> END LOOP;
> CLOSE c;
> END fast_proc;
> /
>
> of which many possibilities exist. One of which Billy V posted. The
> point I would hope more junior developers take away from this is that
> while cursors definitely have their uses ... they should not be the
> first thing one thinks of any more. Ok ... shouldn't be the first thing
> after the 'obvious' DML statement.
>
>

So BULK COLLECT tells Oracle to retrieve all the rows together rather than reading each row individually?

And the LIMIT is what - to specify the maximum number of rows that should be retrieved?

I really must read-up on this stuff...

-- 

jeremy