Re: ORA-12838 error on a stored procedure

On Mar 22, 11:23 am, "DVD" <billi.da..._at_gmail.com> wrote:
> Hi,
> i'd like to ask you about an oracle error, to understand where my
> stored procedure fails:
>
> ORA-12838: cannot read/modify an object after modifying it in parallel
>
> My stored procedure works as follow:
>
> 1) SELECT on a table called TEMP
>
> 2) it creates a cursor for iteration:
>
> OPEN cursor
> FOR sqlstr
>
> FETCH cursor
> BULK COLLECT INTO row_id, nome_ind;
>
> CLOSE cur_val;
>
> where row_id e name_ind type is column type (table TEMP), in
> particular row_id type is ROWID
>
> 3) it starts to loop, calling a pl/sql function:
>
> FOR i IN name_ind.FIRST .. name_ind.LAST
> LOOP
> ...
>
> res := insert_delete(CHARTOROWID (row_id (i)), name_ind (i));
>
> END LOOP;
> COMMIT;
>
> Function insert_delete do two steps, without a COMMIT, the final
> commit is external:
>
> 1) INSERT INTO NAMES ... WHERE ROWID = row_id (i)
>
> 2) DELETE FROM TEMP WHERE ROWID = row_id (i)
>
> The stored procedure works good called with TOAD, with few rows; but
> it fails if called in production environment with the oracle error.
>
> Can you help me about this error?
>
> Thanks.

The procedure you posted is incorrect.
One single rowid won't occur in two tables ever.

--
Sybrand Bakker
Senior Oracle DBA