How does select column into associative array from table work in Oracle database

Question

when I am multiplying the index by 3 instead of one, three rows are getting inserted into the associative array and also it seems that the 2nd and 3rd rows have no value present.

So how are the rows getting inserted when I write select last_name into emp_table(i*3) from employees.
Could you also tell what emp_table.first..emp_table.last is doing i.e. is it returning some kind of reference to the Associative array or is it just returning the first and last index of the columns of the array. in which case how does it work if the list is not ordered and has gaps.
What is happening when I delete a column, is that only the value is getting deleted and the index remains.

DECLARE
    TYPE emptype IS
        TABLE OF employees.last_name%TYPE INDEX BY PLS_INTEGER;
    emp_table emptype;
BEGIN
    FOR i IN 100..105 LOOP
        SELECT
            last_name
        INTO
            emp_table(i * 3)
        FROM
            employees
        WHERE
            employees.employee_id = i;

    END LOOP;
--emp_table.delete('303');
    FOR i IN emp_table.first..emp_table.last LOOP
--dbms_output.put_line(emp_table(i));
        dbms_output.put_line(i);
    END LOOP;

END;

William Robertson · Accepted Answer · 2022-03-26 16:24:16Z

If i = 100, then array element (i * 3) just means element 300. Your code puts the value for employee 100 into emp_table(300), it doesn't create three entries.

first and last are collection methods that return the first and last index values respectively.

emp_table.first..emp_table.last is part of FOR loop syntax and defines a loop that will step through all integers from emp_table.first to emp_table.last inclusive. This only works if the collection index consists of consecutive integers. It looks like your loop will print values of i from 300 to 315, which will work, but it will fail with "ORA-01403: no data found" if you try to access emp_table(i) for i = 301. Up to Oracle 19c, you have to use the next collection method to find the next value within a loop indexed by non-consecutive integers, or strings.

From Oracle 21c you can use the more convenient iterator syntax:

declare
    type aa is table of number index by pls_integer;
    a aa;
    n pls_integer := 1;
begin
    a(n) := 123;
    a(n*3) := 456;
    
    dbms_output.put_line('"myArray" contains '||a.count||' elements, from '||a.first||' to '||a.last);
    dbms_output.new_line();
    dbms_output.put_line('Indices:');  
    
    for i in indices of a loop
        dbms_output.put_line(i);
    end loop;

    dbms_output.new_line();
    dbms_output.put_line('Values:');  

    for v in values of a loop
        dbms_output.put_line(v);
    end loop;

    dbms_output.new_line();
    dbms_output.put_line('Pairs:');  
    
    for i, v in pairs of a loop
        dbms_output.put_line(i||': '||v);
    end loop;
end;

"myArray" contains 2 elements, from 1 to 3

Indices:
1
3

Values:
123
456

Pairs:
1: 123
3: 456

MT0 · Accepted Answer · 2022-03-26 12:05:12Z

Never loop through an associative array using a FOR loop unless you know that:

the items are going to be sequential; and
there are not going to be any deleted items.

(If these conditions hold then you also need to ask yourself why you are using an associative array and not a nested table collection type.)

If you want to loop through an associative array then use FIRST and NEXT:

DECLARE
  TYPE emptype IS TABLE OF employees.last_name%TYPE INDEX BY PLS_INTEGER;
  emp_table emptype;

  idx PLS_INTEGER;
BEGIN
  FOR i IN 100..105 LOOP
    SELECT last_name
    INTO   emp_table(i * 3)
    FROM   employees
    WHERE  employees.employee_id = i;
  END LOOP;

  emp_table.delete('303');

  idx := emp_table.FIRST;

  WHILE idx IS NOT NULL LOOP
    dbms_output.put_line(emp_table(idx));
    idx := emp_table.NEXT(idx);
  END LOOP;
END;
/

Which, for the sample data:

CREATE TABLE employees (employee_id, last_name) AS
SELECT 100, 'AAA' FROM DUAL UNION ALL
SELECT 101, 'BBB' FROM DUAL UNION ALL
SELECT 102, 'CCC' FROM DUAL UNION ALL
SELECT 103, 'DDD' FROM DUAL UNION ALL
SELECT 104, 'EEE' FROM DUAL UNION ALL
SELECT 105, 'FFF' FROM DUAL;

Outputs:

AAA
CCC
DDD
EEE
FFF

db<>fiddle here

Collectives™ on Stack Overflow

How does select column into associative array from table work in Oracle database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related