14

Given this:

DECLARE
  TYPE T_ARRAY IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
  MY_ARRAY T_ARRAY;
  V_COUNT INTEGER;

I would like to do:

BEGIN
  -- ... some code filling the MY_ARRAY array

  -- obviously COUNT_ELEMENTS() does not exists, this is what I'm looking for :-)
  V_COUNT := COUNT_ELEMENTS(MY_ARRAY);

  DBMS_OUTPUT.PUT_LINE('My array containts ' || V_COUNT || ' elements.');
END;

Is there something better than creating a procedure doing a basic loop incrementing a counter? Maybe a PL/SQL native function already does this COUNT_ELEMENTS()?

26

I think this is what you're after:

V_COUNT := MY_ARRAY.COUNT;
8

Fortunately, I found in the existing PL/SQL code I have to maintain, a working "native" behavior:

V_COUNT := MY_ARRAY.COUNT;

should do the trick.

This one is very hard to find with Google, since "count" is more frequently referring to the SELECT COUNT(...) which can be found in SQL queries...

5

In case of a Nested-Table (i.e. without INDEX BY BINARY_INTEGER) you can also use CARDINALITY

V_COUNT := CARDINALITY(MY_ARRAY);

Important difference: In case of Nested-Table which is NULL, COUNT raises an exception, CARDINALITY returns NULL.

  • +1 for CARDINALITY. Although cardinality() does not work for varrays :( – Tagar May 11 '16 at 22:39
2
declare
   type array_t is varray(10) of number(10);
   array array_t := array_t(1,2,3,4,5,6,7,8,9,10);
c number(10):=0;
b number(10):=0;
begin<<outer>>
   for i in 1..array.count loop
    if( mod(i,2)=0)
then
 c:=c+i;
end if;
   end loop;
dbms_output.put_line(c);
begin
    for i in 1..array.count loop
 if( mod(i,2)<>0)
then
 b:=b+i;
end if;
   end loop;
dbms_output.put_line(b);
end;
end outer;
/
  • I would use a different variable name than 'array' for the type 'array_t'. I spent 20 minutes wrestling with my code before I realized 'array' was the variable instead of the type (since I use C, C#, and Java alot). – justdan23 Sep 26 at 22:28
-5

A basic method is:

for i in my_array.first .. my_array.last 
loop 
   v_count:=v_count+1;
end loop;
  • -1 this will not work for an associative array - see here for why – Jack Douglas Feb 27 '12 at 12:17
  • -1 this is slooow – Tagar May 11 '16 at 22:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.