0

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

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

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.