Interesting error :)

From: Alexandr Alesinsky <Alexandr_Alesinsky%f45.n461.z2.fidonet.org_at_kcci.kharkov.ua>
Date: 1996/04/09
Message-ID: <316a80f2_at_f45.n461.z2.fidonet.org>#1/1

Reply-To: al_at_investor.kharkov.ua

Yesterday I have found interesting error.

I created quite simple package

create or replace package access_rights as   pragma restrict_references(access_rights,wnds);   function accessible(id_subacc number) return number;   pragma restrict_references(accessible,wnds,wnps); end;
/

create or replace package body access_rights as

  cursor access_rights_curs is select num_acc,id_subacc,id_subacc_exclude --1
    from rights where id_user=user order by num_acc,id_subacc;            --1
  cursor subaccs(acc number) is                                           --2
    select id_subacc from subacc where num_acc=acc;                       --2

  type acc_table_type is table of number(1) index by binary_integer;   sub acc_table_type;

  function accessible(id_subacc number) return number as   begin
    return sub(id_subacc);
   exception
    when NO_DATA_FOUND then
   return 0;
  end;

begin
  for rrec in access_rights_curs loop
    if rrec.num_acc is not null then

      for brec in subaccs(rrec.num_acc) loop
       sub(brec.id_subacc):=1;
      end loop;

    end if;
    if rrec.id_subacc is not null then
      sub(rrec.id_subacc):=1;
    end if;
    if rrec.id_subacc_exclude is not null then       sub(rrec.id_subacc_exclude):=0;
    end if;
  end loop;
end;
/

And a table:
create table RIGHTS (id_user varchar2(10), num_acc number(4),id_subacc number(10),id_subacc_exclude number(10));

with following contents
USER1 467 null null
USER1 null 586 null
USER1 null 585 null
USER1 null 404 null
USER1 null 100 null
USER1 null null 336

When all are written exactly as above, all is OK, but if cursors declarations are reaaranged and cursor "subaccs" is declared after the "access_rights_curs" then access_rights_cursor become a mad - it return '467 null 467' in the first record, then four correct records and then 'null null null'.

Moreover, if we change type name for table from acc_table_type to table_type, then server process crash during compilation of this package with signal 11.

All were tested on Oracle 7.1.4.1 and 7.1.6.2 for SCO ODT 3.0.

Alexander Alesinsky
Commercial bank "Basis",
Kharkov, Ukraine

• GoldVRED 2.41+
  • Origin: Default Origin (FidoNet 2:461/45)

Received on Tue Apr 09 1996 - 00:00:00 CEST