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

oracleoracle-11g-r2plsql

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

Best Answer

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
Related Question