Re: Snapshot too old

Patrick you are committing across a fetch , can not be done. also if this is a stored procedures being called from a remote client don't do it as you are losing control from the client.

Patrick Elliott <pelliott_at_isd.net> wrote in article <32b27dd6.0_at_usamrid.isd.net>...
> I am having a puzling problem. I have a pl/sql procedure that opens a
> cursor in a loop, inserts records from the cursor into a table, and if
> there are any errors, the error messages are inserted into a third
> table via an exception section. I am committing after every insert,
> the tables are empty before the procedure starts, but I am still
> getting a "SNAPSHOT TOO OLD" message. Here is a fragment of code
> similar to what I am working with, but simplified:
>
> CREATE OR REPLACE PROCEDURE proc1 AS
> BEGIN
> DECLARE
> CURSOR c1 IS
> SELECT a, b
> FROM t1;
> BEGIN
> FOR c1_rec IN c1 LOOP
> BEGIN
> INSERT INTO t2
> VALUES (c1_rec.a, c1_rec.b);
> COMMIT;
> EXCEPTION
> WHEN OTHERS THEN
> DECLARE
> err_msg VARCHAR2(256) := SQLERRM;
> BEGIN
> INSERT INTO problems <<<<Error points here.
> VALUES (SYSDATE, err_msg);
> COMMIT;
> END;
> END;
> END LOOP;
> END;
> END proc1;
>
> Any answers to this question would be greatly appreciated.
>
>
Received on Sun Dec 15 1996 - 00:00:00 CST