Re: PL SQL code bad structured resulting in unique key violation

From: Carlos <miotromailcarlos_at_netscape.net>
Date: 28 Jun 2006 03:44:20 -0700
Message-ID: <1151491460.070463.218740@y41g2000cwy.googlegroups.com>

jullape_at_terra.es wrote:
> Hi,
>
> I've seen some PL-SQL code utterly flawed, to sum it up it looks
> like:
>
> [pseudocode]
>
> procedure p1 is
> CURSOR cur1 is
> SELECT ... FROM TABLE1, ....
> ;
>
> CURSOR cur2 is
> SELECT ...
> ;
>
> BEGIN
> -- open cur1
> -- open cur2 upon cur1 results and write to TABLE1
> ...
> -- in due time it reads a register inserted into table1 within this
> loop
> ...
> -- close cur2
> -- close cur1
>
> END;
>
> The problem is that cur1 shouldn't be reading the registers inserted
> in the loop, is there any general way to prevent cur1 to read the
> registers inserted into TABLE1 in the current transaction? I'm aware
> that the best way to sort it out is to rearrange the whole procedure,
> but it's huge and subtle.
>
> Thanks in advance
> David

> > The problem is that cur1 shouldn't be reading the registers inserted

And it does not (at least if you do not OPEN it each and every time you insert a new row).

sql> create table w (id_n number(38), c_txt varchar2(10));

tabla creada

sql> insert into w values (1, 'UNO');

1 fila creada.

sql> declare

  2     i integer :=0;
  3     v_ID_N   NUMBER(38);
  4     v_C_TXT  VARCHAR2(10);
  5     cursor cur_1 is
  6        select * from W;

  7 begin
  8 open cur_1;
  9 loop

 10       fetch cur_1 into v_ID_N, v_C_TXT;
 11       exit when cur_1%notfound;
 12       i:=i+1;
 13       dbms_output.put_line('i: ' ||to_char(i));
 14       insert into W values (i,to_char(i));

 15 end loop;
 16 close cur_1;
 17 end;
 18 /
i: 1

Procedimiento PL/SQL terminado correctamente.

sql> select * from w;

      ID_N C_TXT
---------- ----------

         1 UNO
         1 1

sql>

If the cursor read the new rows every time, it would end up as an infinite loop.

Cheers.

Carlos. Received on Wed Jun 28 2006 - 05:44:20 CDT