OraFAQ Forum: SQL & PL/SQL » how do associate array working

Home » SQL & PL/SQL » SQL & PL/SQL » how do associate array working

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how do associate array working [message #626997]

Thu, 06 November 2014 07:03

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi,

DECLARE 
    -- Associative array indexed by string: 
    TYPE population 
      IS TABLE OF NUMBER -- Associative array type 
    INDEX BY VARCHAR2(64); --  indexed by string 
    city_population POPULATION; --  Associative array variable 
    i               VARCHAR2(64); -- Scalar variable 
BEGIN 
    -- Add elements (key-value pairs) to associative array: 
    City_population('Chennai') := 2000000; 
    City_population('Mubai') := 750000; 
    City_population('Ap') := 1000000; 
    -- Change value associated with key 'Ap': 
    City_population('Ap') := 2001; 
    -- Print associative array: 
    i := city_population.first; -- Get first element of array 
    WHILE i IS NOT NULL LOOP 
        dbms_output.Put_line ('Population of ' 
                              || i 
                              || ' is ' 
                              || City_population(i)); 
        i := city_population.NEXT(i); -- Get next element of array 
    END LOOP; 
END;

Population of Ap is 2001
Population of Chennai is 2000000
Population of Mubai is   750000

Is this out put order by first letter's of input?(A,C,M)

Report message to a moderator

Re: how do associate array working [message #626999 is a reply to message #626997]

Thu, 06 November 2014 07:33

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

It's in the order of the index. The index is the city name.

Report message to a moderator

Re: how do associate array working [message #627011 is a reply to message #626997]

Thu, 06 November 2014 08:34

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

For an associative array with VARCHAR2 key values; ordering is based on the binary values of the characters in the string, unless the NLS_COMP initialization parameter is set to ANSI, in which case the ordering is based on the locale-specific sort order specified by the NLS_SORT initialization parameter.

Please refer documentaion

http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/05_colls.htm

Report message to a moderator

Re: how do associate array working [message #627013 is a reply to message #627011]

Thu, 06 November 2014 08:52

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Precisely, quote from docs,

Quote:

Indexes are stored in sort order, not creation order. For string types, sort order is determined by the initialization parameters NLS_SORT and NLS_COMP.

Edit MC: change code to quote tags.

[Updated on: Thu, 06 November 2014 09:23] by Moderator

Report message to a moderator

Re: how do associate array working [message #627022 is a reply to message #627013]

Thu, 06 November 2014 10:10

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Lalit Kumar B wrote on Thu, 06 November 2014 09:52

Precisely, quote from docs

Which creates a problem. It should be NLS_SORT only, same as in SQL:

SQL> alter session set nls_sort=binary
  2  /

Session altered.

SQL> with t as (
  2             select 'A' x,nlssort('A') y from dual union
  3             select 'B',nlssort('B') from dual union
  4             select 'a',nlssort('a') from dual union
  5             select 'b',nlssort('b') from dual
  6            )
  7  select  *
  8    from  t
  9    order by x
 10  /

X Y
- ----
A 4100
B 4200
a 6100
b 6200

SQL> alter session set nls_sort=french
  2  /

Session altered.

SQL> with t as (
  2             select 'A' x,nlssort('A') y from dual union
  3             select 'B',nlssort('B') from dual union
  4             select 'a',nlssort('a') from dual union
  5             select 'b',nlssort('b') from dual
  6            )
  7  select  *
  8    from  t
  9    order by x
 10  /

X Y
- --------
A 14000100
a 14000200
B 19000100
b 19000200

SQL> alter session set nls_sort=german
  2  /

Session altered.

SQL> with t as (
  2             select 'A' x,nlssort('A') y from dual union
  3             select 'B',nlssort('B') from dual union
  4             select 'a',nlssort('a') from dual union
  5             select 'b',nlssort('b') from dual
  6            )
  7  select  *
  8    from  t
  9    order by x
 10  /

X Y
- --------
a 14000100
A 14000200
b 19000100
B 19000200

SQL> alter session set nls_sort=binary_ci
  2  /

Session altered.

SQL> with t as (
  2             select 'A' x,nlssort('A') y from dual union
  3             select 'B',nlssort('B') from dual union
  4             select 'a',nlssort('a') from dual union
  5             select 'b',nlssort('b') from dual
  6            )
  7  select  *
  8    from  t
  9    order by x
 10  /

X Y
- ----
a 6100
A 6100
b 6200
B 6200

SQL> alter session set nls_sort=french_ci
  2  /

Session altered.

SQL> with t as (
  2             select 'A' x,nlssort('A') y from dual union
  3             select 'B',nlssort('B') from dual union
  4             select 'a',nlssort('a') from dual union
  5             select 'b',nlssort('b') from dual
  6            )
  7  select  *
  8    from  t
  9    order by x
 10  /

X Y
- --------
a 14000200
A 14000200
b 19000200
B 19000200

SQL> alter session set nls_sort=german_ci
  2  /

Session altered.

SQL> with t as (
  2             select 'A' x,nlssort('A') y from dual union
  3             select 'B',nlssort('B') from dual union
  4             select 'a',nlssort('a') from dual union
  5             select 'b',nlssort('b') from dual
  6            )
  7  select  *
  8    from  t
  9    order by x
 10  /

X Y
- --------
a 14000100
A 14000100
b 19000100
B 19000100

SQL>

As you can see, changing NLS_SORT changes row order but not the number of rows returned by the UNION even when we user case insensitive NLS_SORT. But as soon as be use linguistic NLS_COMP with case insensitive NLS_SORT number of rows returned by the UNION changes since upper and lower case are now consirered same and UNION removes duplicates:

SQL> alter session set nls_comp=linguistic
  2  /

Session altered.

SQL> alter session set nls_sort=binary
  2  /

Session altered.

SQL> with t as (
  2             select 'A' x,nlssort('A') y from dual union
  3             select 'B',nlssort('B') from dual union
  4             select 'a',nlssort('a') from dual union
  5             select 'b',nlssort('b') from dual
  6            )
  7  select  *
  8    from  t
  9    order by x
 10  /

X Y
- ----
A 4100
B 4200
a 6100
b 6200

SQL> alter session set nls_sort=french
  2  /

Session altered.

SQL> with t as (
  2             select 'A' x,nlssort('A') y from dual union
  3             select 'B',nlssort('B') from dual union
  4             select 'a',nlssort('a') from dual union
  5             select 'b',nlssort('b') from dual
  6            )
  7  select  *
  8    from  t
  9    order by x
 10  /

X Y
- --------
A 14000100
a 14000200
B 19000100
b 19000200

SQL> alter session set nls_sort=german
  2  /

Session altered.

SQL> with t as (
  2             select 'A' x,nlssort('A') y from dual union
  3             select 'B',nlssort('B') from dual union
  4             select 'a',nlssort('a') from dual union
  5             select 'b',nlssort('b') from dual
  6            )
  7  select  *
  8    from  t
  9    order by x
 10  /

X Y
- --------
a 14000100
A 14000200
b 19000100
B 19000200

SQL> alter session set nls_sort=binary_ci
  2  /

Session altered.

SQL> with t as (
  2             select 'A' x,nlssort('A') y from dual union
  3             select 'B',nlssort('B') from dual union
  4             select 'a',nlssort('a') from dual union
  5             select 'b',nlssort('b') from dual
  6            )
  7  select  *
  8    from  t
  9    order by x
 10  /

X Y
- ----
A 6100
B 6200

SQL> alter session set nls_sort=french_ci
  2  /

Session altered.

SQL> with t as (
  2             select 'A' x,nlssort('A') y from dual union
  3             select 'B',nlssort('B') from dual union
  4             select 'a',nlssort('a') from dual union
  5             select 'b',nlssort('b') from dual
  6            )
  7  select  *
  8    from  t
  9    order by x
 10  /

X Y
- --------
A 14000200
B 19000200

SQL> alter session set nls_sort=german_ci
  2  /

Session altered.

SQL> with t as (
  2             select 'A' x,nlssort('A') y from dual union
  3             select 'B',nlssort('B') from dual union
  4             select 'a',nlssort('a') from dual union
  5             select 'b',nlssort('b') from dual
  6            )
  7  select  *
  8    from  t
  9    order by x
 10  /

X Y
- --------
A 14000100
B 19000100

SQL>

So I have no idea why Oracle decided to use NLS_SORT + NLS_COMP for associative arrays, but look what happens. NLS_SORT alone has no effect:

SQL> set serveroutput on
SQL> alter session set nls_sort=binary
  2  /

Session altered.

SQL> DECLARE
  2      TYPE assoc_array_type IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(64);
  3      v_assoc_array assoc_array_type;
  4      i               VARCHAR2(64); -- Scalar variable
  5  BEGIN
  6      v_assoc_array('A') := 'A';
  7      v_assoc_array('a') := 'a';
  8      v_assoc_array('B') := 'B';
  9      v_assoc_array('b') := 'b';
 10      i := v_assoc_array.first;
 11      WHILE i IS NOT NULL LOOP
 12          dbms_output.Put_line(v_assoc_array(i));
 13          i := v_assoc_array.NEXT(i);
 14      END LOOP;
 15  END;
 16  /
A
B
a
b

PL/SQL procedure successfully completed.

SQL> alter session set nls_sort=french
  2  /

Session altered.

SQL> DECLARE
  2      TYPE assoc_array_type IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(64);
  3      v_assoc_array assoc_array_type;
  4      i               VARCHAR2(64); -- Scalar variable
  5  BEGIN
  6      v_assoc_array('A') := 'A';
  7      v_assoc_array('a') := 'a';
  8      v_assoc_array('B') := 'B';
  9      v_assoc_array('b') := 'b';
 10      i := v_assoc_array.first;
 11      WHILE i IS NOT NULL LOOP
 12          dbms_output.Put_line(v_assoc_array(i));
 13          i := v_assoc_array.NEXT(i);
 14      END LOOP;
 15  END;
 16  /
A
B
a
b

PL/SQL procedure successfully completed.

SQL> alter session set nls_sort=german
  2  /

Session altered.

SQL> DECLARE
  2      TYPE assoc_array_type IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(64);
  3      v_assoc_array assoc_array_type;
  4      i               VARCHAR2(64); -- Scalar variable
  5  BEGIN
  6      v_assoc_array('A') := 'A';
  7      v_assoc_array('a') := 'a';
  8      v_assoc_array('B') := 'B';
  9      v_assoc_array('b') := 'b';
 10      i := v_assoc_array.first;
 11      WHILE i IS NOT NULL LOOP
 12          dbms_output.Put_line(v_assoc_array(i));
 13          i := v_assoc_array.NEXT(i);
 14      END LOOP;
 15  END;
 16  /
A
B
a
b

PL/SQL procedure successfully completed.

SQL> alter session set nls_sort=binary_ci
  2  /

Session altered.

SQL> DECLARE
  2      TYPE assoc_array_type IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(64);
  3      v_assoc_array assoc_array_type;
  4      i               VARCHAR2(64); -- Scalar variable
  5  BEGIN
  6      v_assoc_array('A') := 'A';
  7      v_assoc_array('a') := 'a';
  8      v_assoc_array('B') := 'B';
  9      v_assoc_array('b') := 'b';
 10      i := v_assoc_array.first;
 11      WHILE i IS NOT NULL LOOP
 12          dbms_output.Put_line(v_assoc_array(i));
 13          i := v_assoc_array.NEXT(i);
 14      END LOOP;
 15  END;
 16  /
A
B
a
b

PL/SQL procedure successfully completed.

SQL> alter session set nls_sort=french_ci
  2  /

Session altered.

SQL> DECLARE
  2      TYPE assoc_array_type IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(64);
  3      v_assoc_array assoc_array_type;
  4      i               VARCHAR2(64); -- Scalar variable
  5  BEGIN
  6      v_assoc_array('A') := 'A';
  7      v_assoc_array('a') := 'a';
  8      v_assoc_array('B') := 'B';
  9      v_assoc_array('b') := 'b';
 10      i := v_assoc_array.first;
 11      WHILE i IS NOT NULL LOOP
 12          dbms_output.Put_line(v_assoc_array(i));
 13          i := v_assoc_array.NEXT(i);
 14      END LOOP;
 15  END;
 16  /
A
B
a
b

PL/SQL procedure successfully completed.

SQL> alter session set nls_sort=german_ci
  2  /

Session altered.

SQL> DECLARE
  2      TYPE assoc_array_type IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(64);
  3      v_assoc_array assoc_array_type;
  4      i               VARCHAR2(64); -- Scalar variable
  5  BEGIN
  6      v_assoc_array('A') := 'A';
  7      v_assoc_array('a') := 'a';
  8      v_assoc_array('B') := 'B';
  9      v_assoc_array('b') := 'b';
 10      i := v_assoc_array.first;
 11      WHILE i IS NOT NULL LOOP
 12          dbms_output.Put_line(v_assoc_array(i));
 13          i := v_assoc_array.NEXT(i);
 14      END LOOP;
 15  END;
 16  /
A
B
a
b

PL/SQL procedure successfully completed.

SQL>

So we are forced to use NLS_COM also. As a result we can't create associative array with same index value but in different case when case insensitive sort is needed:

SQL> alter session set nls_comp=linguistic
  2  /

Session altered.

SQL> alter session set nls_sort=binary
  2  /

Session altered.

SQL> DECLARE
  2      TYPE assoc_array_type IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(64);
  3      v_assoc_array assoc_array_type;
  4      i               VARCHAR2(64); -- Scalar variable
  5  BEGIN
  6      v_assoc_array('A') := 'A';
  7      v_assoc_array('a') := 'a';
  8      v_assoc_array('B') := 'B';
  9      v_assoc_array('b') := 'b';
 10      i := v_assoc_array.first;
 11      WHILE i IS NOT NULL LOOP
 12          dbms_output.Put_line(v_assoc_array(i));
 13          i := v_assoc_array.NEXT(i);
 14      END LOOP;
 15  END;
 16  /
A
B
a
b

PL/SQL procedure successfully completed.

SQL> alter session set nls_sort=french
  2  /

Session altered.

SQL> DECLARE
  2      TYPE assoc_array_type IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(64);
  3      v_assoc_array assoc_array_type;
  4      i               VARCHAR2(64); -- Scalar variable
  5  BEGIN
  6      v_assoc_array('A') := 'A';
  7      v_assoc_array('a') := 'a';
  8      v_assoc_array('B') := 'B';
  9      v_assoc_array('b') := 'b';
 10      i := v_assoc_array.first;
 11      WHILE i IS NOT NULL LOOP
 12          dbms_output.Put_line(v_assoc_array(i));
 13          i := v_assoc_array.NEXT(i);
 14      END LOOP;
 15  END;
 16  /
A
a
B
b

PL/SQL procedure successfully completed.

SQL> alter session set nls_sort=german
  2  /

Session altered.

SQL> DECLARE
  2      TYPE assoc_array_type IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(64);
  3      v_assoc_array assoc_array_type;
  4      i               VARCHAR2(64); -- Scalar variable
  5  BEGIN
  6      v_assoc_array('A') := 'A';
  7      v_assoc_array('a') := 'a';
  8      v_assoc_array('B') := 'B';
  9      v_assoc_array('b') := 'b';
 10      i := v_assoc_array.first;
 11      WHILE i IS NOT NULL LOOP
 12          dbms_output.Put_line(v_assoc_array(i));
 13          i := v_assoc_array.NEXT(i);
 14      END LOOP;
 15  END;
 16  /
a
A
b
B

PL/SQL procedure successfully completed.

SQL> alter session set nls_sort=binary_ci
  2  /

Session altered.

SQL> DECLARE
  2      TYPE assoc_array_type IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(64);
  3      v_assoc_array assoc_array_type;
  4      i               VARCHAR2(64); -- Scalar variable
  5  BEGIN
  6      v_assoc_array('A') := 'A';
  7      v_assoc_array('a') := 'a';
  8      v_assoc_array('B') := 'B';
  9      v_assoc_array('b') := 'b';
 10      i := v_assoc_array.first;
 11      WHILE i IS NOT NULL LOOP
 12          dbms_output.Put_line(v_assoc_array(i));
 13          i := v_assoc_array.NEXT(i);
 14      END LOOP;
 15  END;
 16  /
a
b

PL/SQL procedure successfully completed.

SQL> alter session set nls_sort=french_ci
  2  /

Session altered.

SQL> DECLARE
  2      TYPE assoc_array_type IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(64);
  3      v_assoc_array assoc_array_type;
  4      i               VARCHAR2(64); -- Scalar variable
  5  BEGIN
  6      v_assoc_array('A') := 'A';
  7      v_assoc_array('a') := 'a';
  8      v_assoc_array('B') := 'B';
  9      v_assoc_array('b') := 'b';
 10      i := v_assoc_array.first;
 11      WHILE i IS NOT NULL LOOP
 12          dbms_output.Put_line(v_assoc_array(i));
 13          i := v_assoc_array.NEXT(i);
 14      END LOOP;
 15  END;
 16  /
a
b

PL/SQL procedure successfully completed.

SQL> alter session set nls_sort=german_ci
  2  /

Session altered.

SQL> DECLARE
  2      TYPE assoc_array_type IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(64);
  3      v_assoc_array assoc_array_type;
  4      i               VARCHAR2(64); -- Scalar variable
  5  BEGIN
  6      v_assoc_array('A') := 'A';
  7      v_assoc_array('a') := 'a';
  8      v_assoc_array('B') := 'B';
  9      v_assoc_array('b') := 'b';
 10      i := v_assoc_array.first;
 11      WHILE i IS NOT NULL LOOP
 12          dbms_output.Put_line(v_assoc_array(i));
 13          i := v_assoc_array.NEXT(i);
 14      END LOOP;
 15  END;
 16  /
a
b

PL/SQL procedure successfully completed.

SQL>

SY.

Report message to a moderator

Previous Topic:	insert data
Next Topic:	ORA-01427: single-row subquery returns more than one row

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 23:02:06 CDT 2024