Re: emulating SQLPLUS COPY

"Malcolm Dew-Jones" <yf110_at_vtn1.victoria.tc.ca> wrote in message news:3cae4695_at_news.victoria.tc.ca...
> Hello...
>
> I can use SQL PLus COPY command to copy a large table (with no constraints
> or indexes) and it takes about 35 minutes.
>
> I want to do the same thing via PL/SQL, but it takes much longer (ten
> hours).
>
> My PL/SQL code has the following very simple outline. Initially the local
> table is empty, and has no constraints or indexes.
>
>
> cursor C is select * from remote_table
>
> one_row local_table%rowtype; -- both tables have same layout
>
> open C
> loop
> fetch C into one_row
> exit when end of data
>
> insert into local_table values
> ( one_row.col1 ,
> one_row.col2 ,
> ... ,
> one_row.col67
> );
>
> end loop
> close C
>
> As you can see it's very simple. It just loops over the remote table
> rows, and for each row it does an insert into the local table.
>
> However, as I said, this is *much* slower than the COPY command. What is
> the trick to speed this up?
>
> I need to use a procedure (not the COPY command) because eventually I need
> to do some additional processing of each row.
>
> Suggestions welcome, thanks.

No version, as usual with almost all posters, and as also usual the answer is version specific.
If you have 8i, refer to your pl/sql documentation for bulk collects and bulk inserts.
Other than that the most efficient solution would be to write a Pro*C program, so you can do array fetch and array insert.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address