Why can i not call COUNT on a varray column in a select statement using oracle

Question

I have a simple array type definition like this

 CREATE OR REPLACE TYPE "LIST_OF_NAMES_T" IS VARRAY(10) OF VARCHAR2 (100); 

Now I have a column items of type LIST_OF_NAMES_T in my table TABLE1

Why am I getting an error when selecting the count of the array in a select statement like this:

SELECT items.COUNT FROM TABLE1;

Oracle tells me

ORA-00904: "items"."COUNT ": invalid identifier

Answer 1

10.2 documentation: Using Collection Methods

The following apply to collection methods:

• Collection methods cannot be called from SQL statements.

...

11.2 documentation: Collection Methods

A collection method invocation can appear anywhere that an invocation of a PL/SQL subprogram of its type (function or procedure) can appear, except in a SQL statement.

Versions before 12c:

SQL> CREATE OR REPLACE TYPE "LIST_OF_NAMES_T" IS VARRAY(10) OF VARCHAR2 (100);
 2 /
Type created.
SQL> create table table1(c1 LIST_OF_NAMES_T);
Table created.
SQL> insert into table1 values(LIST_OF_NAMES_T('A', 'B'));
1 row created.
SQL> commit;
SQL> create function f1(p_list_of_names list_of_names_t) return number as
 2 begin
 3 return p_list_of_names.count;
 4 end;
 5 /
Function created.
SQL> select f1(items) from table1;
 F1(ITEMS)
----------
 2

In 12c and later, you can define functions on the fly, so this also works:

SQL> with function f2(p_list_of_names list_of_names_t) return number as
 2 begin
 3 return p_list_of_names.count;
 4 end;
 5 select f2(items) from table1;
 6 /
 F2(ITEMS)
----------
 2