Why is this check for null associative array in PL/SQL failing?

Question

I have an associative array created by a type of rowtype of a table column.

To give an example, this is how it is(the table names are different, but the structure is the same):

This is the DDL of the table

CREATE TABLE employees
  (
     id     NUMBER,
     name   VARCHAR2(240),
     salary NUMBER
  );

Here's what my procedure is doing:

DECLARE
    TYPE table_of_emp
      IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER;
    emp TABLE_OF_EMP;
BEGIN
    IF emp IS NULL THEN
      dbms_output.Put_line('Null associative array');
    ELSE
      dbms_output.Put_line('Not null');
    END IF;
END;

I assume this should result in "Null associative array" being printed. However, the if condition fails and the execution jumps to the else part.

Now if I put in a for loop to print the collection values

DECLARE
    TYPE table_of_emp
      IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER;
    emp TABLE_OF_EMP;
BEGIN
    IF emp IS NULL THEN
      dbms_output.Put_line('Null associative array');
    ELSE
      dbms_output.Put_line('Not null');

      FOR i IN emp.first..emp.last LOOP
          dbms_output.Put_line('Emp name: '
                               || Emp(i).name);
      END LOOP;
    END IF;
END;

then the program unit raises an exception, referencing the for loop line

ORA-06502: PL/SQL: Numeric or value error

which I presume is because of the null associative array. Is the error being raised because of null associative array?

So why is the first check failing then? What am I doing wrong?

The database server is Oracle 11g EE (version 11.2.0.3.0 64 bit)

I have not done plsql in a while, but in your for loop (`dbms_output.Put_line('Emp name: ' || Emp(i).name);`) should `Emp(i).name` be `emp(i).name`? — Jacob Schoen, Sep 13 '12 at 18:00
@jschoen variable names are case insensitive, so it doesn't matter — Sathyajith Bhat, Sep 13 '12 at 18:01

Shannon Severance · Accepted Answer · 2015-12-03T23:48:21.517

I assume this should result in "Null associative array" being printed. That assumption is wrong for associative arrays. They exist when declared, but are empty. It would be correct for other types of PL/SQL collections:

Until you initialize it, a nested table or varray is atomically null; the collection itself is null, not its elements. To initialize a nested table or varray, you use a constructor, a system-defined function with the same name as the collection type. This function constructs collections from the elements passed to it.

You must explicitly call a constructor for each varray and nested table variable. Associative arrays, the third kind of collection, do not use constructors. Constructor calls are allowed wherever function calls are allowed. Initializing and Referencing Collections

Compare:

SQL> declare
  2      type varchar2_100_aa is table of varchar2(100) index by binary_integer;
  3      test varchar2_100_aa;
  4  begin
  5      test(1) := 'Hello';
  6      dbms_output.put_line(test(1));
  7  end;
  8  /
Hello

PL/SQL procedure successfully completed.

SQL> declare
  2      type varchar2_100_va is varray(100) of varchar2(100);
  3      test varchar2_100_va;
  4  begin
  5      test(1) := 'Hello';
  6      dbms_output.put_line(test(1));
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 5

Variable array done correctly:

SQL> declare
  2      type varchar2_100_va is varray(10) of varchar2(100);
  3      test varchar2_100_va;
  4  begin
  5      test := varchar2_100_va(); -- not needed on associative array
  6      test.extend; -- not needed on associative array
  7      test(1) := 'Hello';
  8      dbms_output.put_line(test(1));
  9  end;
 10  /
Hello

PL/SQL procedure successfully completed.

Because the associative array is empty first and last are null, which is why your second example results in ORA-06502: PL/SQL: Numeric or value error:

SQL> declare
  2      type varchar2_100_aa is table of varchar2(100) index by binary_integer;
  3      test varchar2_100_aa;
  4  begin
  5      dbms_output.put_line(test.count);
  6      dbms_output.put_line(coalesce(to_char(test.first), 'NULL'));
  7      dbms_output.put_line(coalesce(to_char(test.last), 'NULL'));
  8      test(1) := 'Hello';
  9      dbms_output.new_line;
 10      dbms_output.put_line(test.count);
 11      dbms_output.put_line(coalesce(to_char(test.first), 'NULL'));
 12      dbms_output.put_line(coalesce(to_char(test.last), 'NULL'));
 13  end;
 14  /
0
NULL
NULL

1
1
1

PL/SQL procedure successfully completed.

EDIT Also note that associative arrays can be sparse. Looping over the numbers between first and last will raise an exception for any collection that is sparse. Instead use first and next like so: (Last and prev to loop the other direction.)

SQL> declare
  2      type varchar2_100_aa is table of varchar2(100) index by binary_integer;
  3      test varchar2_100_aa;
  4      i binary_integer;
  5  begin
  6      test(1) := 'Hello';
  7      test(100) := 'Good bye';
  8      dbms_output.put_line(test.count);
  9      dbms_output.put_line(coalesce(to_char(test.first), 'NULL'));
 10      dbms_output.put_line(coalesce(to_char(test.last), 'NULL'));
 11      dbms_output.new_line;
 12  --
 13      i := test.first;
 14      while (i is not null) loop
 15          dbms_output.put_line(to_char(i, '999')  || ' - ' || test(i));
 16          i := test.next(i);
 17      end loop;
 18  end;
 19  /
2
1
100

   1 - Hello
 100 - Good bye

PL/SQL procedure successfully completed.

This is the correct answer. But for me, it also highlights the weirdness of Oracle sometimes. Surely the whole point of a variable-sized "array" (or "table" in PLSQL-speak) is that you don't know in advance how many records you'll have at execution-time (i.e. it could easily be zero). And the whole point of using arrays at all is so that you can loop on them!!. It therefore seems completely counter-intuitive to have to check that the list has non-zero length before being able to loop on it. Why can't the parser simply loop zero times, like it does in standard implicit cursors? — cartbeforehorse, Jun 26 '13 at 12:23

score 5 · Answer 2 · answered Sep 13 '12 at 18:02

5

I'm not going to answer why the first check is failing. I've never thought of doing anything like that and am quite surprised that it doesn't raise an error.

The reason why you're getting an exception raised on the loop is, as you've noted, that the index emp.first does not exist.

Rather than checking for nulls, you should really be checking for the existence of this index. Which you can do be using the .exists(i) syntax:

if not emp.exists(emp.first) then
   dbms_output.put_line('Nothing in here.');
end if;

answered Sep 13 '12 at 18:02

Ben

51,770
36
127
149

Ben, Oracle documentation says "You cannot use EXISTS with an associative array." - http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm#CJAEFFID having said that, the program unit runs succesfully. Hrm. – Sathyajith Bhat Sep 13 '12 at 18:06
Yes @Sathya, sorry missed the fact that you were doing it from a table rowtype. Does it run successfully? It's failing for me? It'll work if this is the rowtype of a cursor. – Ben Sep 13 '12 at 18:12
yes, as I edited it, strangely enough it runs succesfully despite the documentation saying otherwise – Sathyajith Bhat Sep 13 '12 at 18:13
Most strange [I can't get it work with a table rowtype](http://www.sqlfiddle.com/#!4/0f48a/9) as per your original comment; [with a cursor](http://www.sqlfiddle.com/#!4/0f48a/10) it's easy. I'm assuming, as it's you, that your minimal example is correct :-). – Ben Sep 13 '12 at 18:16
Why don't just simply use if emp.count = 0 then null otherwise not it will work – Nick Krasnov Sep 13 '12 at 18:17
wondering if that's a limitation of SQL Fiddle, Nicholas's emp.count check works fine as well (and something that obvious didn't strike me) – Sathyajith Bhat Sep 13 '12 at 18:21
SQL Fiddle just cannot handle emp%rowtype. IF datatype is specified explicitly it works – Nick Krasnov Sep 13 '12 at 18:23
[It seems to be able to](http://www.sqlfiddle.com/#!4/0f48a/22) though that's taking minimal example to the limits! It could easily be a limitation though as you've both said. – Ben Sep 13 '12 at 18:26
The note saying that `exists` can not be used on associative arrays is not in the 10g R2 documentation. It seems unlikely that Oracle would remove support for `exists` on associative arrays. One or the other must have a documentation bug. – Shannon Severance Sep 13 '12 at 20:20

Why is this check for null associative array in PL/SQL failing?

2 Answers2

Linked