Re: PL/SQL Arrays - 2 dimensional?

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Wed, 19 Feb 2003 10:00:40 +0300
Message-ID: <b2va68$c6j$1@babylon.agtel.net>

SQL> select banner from v$version;

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production PL/SQL Release 9.2.0.2.1 - Production
CORE 9.2.0.2.0 Production
TNS for 32-bit Windows: Version 9.2.0.2.0 - Production NLSRTL Version 9.2.0.2.0 - Production

SQL> DECLARE
  2 TYPE AssocArray IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);   3 lst AssocArray;
  4 idx VARCHAR2(10);
  5 BEGIN
  6 lst('1') := 'A';
  7 lst('2') := 'B';

  8    dbms_output.Put_line(' lst(''1'') = '||lst('1'));
  9    dbms_output.Put_line(' lst(''2'') = '||lst('2'));
 10    dbms_output.Put_line(' lst.Count = '||lst.Count);

 11 idx := lst.LAST;
 12 dbms_output.Put_line(' lst.last = '|| nvl(idx,'NULL'));  13 dbms_output.Put_line(' lst.delete(''2'')');  14 lst.DELETE('2');
 15 dbms_output.Put_line(' lst.Count = '||lst.Count);  16 idx := lst.LAST;
 17 dbms_output.Put_line(' lst.last = '|| nvl(idx,'NULL'));  18 dbms_output.Put_line(' lst(''2'') := ''B''');  19 lst('2') := 'B';
 20 idx := lst.LAST;

 21    dbms_output.Put_line(' lst.last = '|| nvl(idx,'NULL'));
 22    dbms_output.Put_line(' lst(''1'') = '||lst('1'));
 23    dbms_output.Put_line(' lst(''2'') = '||lst('2'));
 24    dbms_output.Put_line(' lst.Count = '||lst.Count||' !!!');

 25 idx := lst.LAST;
 26 dbms_output.Put_line(' lst.last = '|| nvl(idx,'NULL'));  27 dbms_output.Put_line(' lst.delete(''2'')');  28 lst.DELETE('2');
 29 dbms_output.Put_line(' lst.Count = '||lst.Count||' !!!');  30 idx := lst.LAST;
 31 dbms_output.Put_line(' lst.last = '|| nvl(idx,'NULL')||' !!!' );  32 END;
 33 /
lst('1') = A
lst('2') = B

lst.Count = 2
lst.last = 2
lst.delete('2')
lst.Count = 1
lst.last = 1

lst('2') := 'B'

lst.last = 2
lst('1') = A
lst('2') = B
lst.Count = 1 !!!
lst.last = 2
lst.delete('2')

lst.Count = 0 !!!
lst.last = NULL !!!

PL/SQL procedure successfully completed.

As you can see, array element count is not being increased properly after deleting an element and reinserting it. Not sure if this bug has an active TAR, but there's a forum thread on ML regarding it: DocId #312597.995

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Rene Nyffenegger" <rene.nyffenegger_at_gmx.ch> wrote in message
news:b2u9on$1fgvm0$1_at_ID-82536.news.dfncis.de...


>

> > Yes. Multidimensional and associative (INDEX BY VARCHAR2) arrays are

> > introduced in 9.2 (with some unpleasant bugs in the latter, still not fixed

> > in 9.2.0.2).

>

> Which are those bugs?

>

> Rene

>

>

>

> --

>   no sig today

>

>

Received on Wed Feb 19 2003 - 01:00:40 CST