Re: intermittent commit on insert ?

"Sunil" <sunil_franklin_at_hotmail.com> wrote in message news:<TT4L8.8$DI4.88_at_news.oracle.com>...
> I need to insert into a table T1, data from two views V1 or V2. The name
> of the view will be passed in as a parameter to my procedure( l_view) . Also
> Each user has a limit on the number of records he can insert (say l_limit )
> . This is also passed into the procedure.
>
> I Have been thinking about dynamic sql on the lines of (
> 'insert into T1 select * from ' || l_view || ' where rownum < ' ||
> l_limit ) as row by row processing will be slow.
>
> But I am afraid of hitting the rollback segment limit. So I would like to
> commit when ever 'n' records are inserted ( again this can be passed as a
> parameter ) .
>
> Is this possible, somehow?
>

You may want to first find out what's the largest number of rows a user will insert. If possible use a single SQL.

But, if you have to use PL/SQL, I don't see how you can do this 'insert into ... select from' n rows at a time. An approach somewhat similar to what you wanted to do is to open a cursor and use PL/SQL array processing. As for intermittent commit, do it if it's needed. I don't think it'll hurt much.

>
> Regards,
> Sunil.
Received on Wed Jun 05 2002 - 11:57:58 CDT