How cast few atributes from relational table into varray on creating view

Question

I need to create VIEW with VARRAY casted from 3 atributes in relational table.

I tried many ways, but I didn't get a positive result. Also can't find a solution in the oracle documentation.

CREATE TABLE Client (
    ID_Client       NUMBER(7,0),
    Phone1          VARCHAR2(9),
    Phone2          VARCHAR2(9),
    Phone3          VARCHAR2(9),
    CONSTRAINT      Client_PK
        PRIMARY KEY (ID_Client)
    );
/
CREATE TYPE phone_vartyp IS VARRAY(3) OF VARCHAR2(9);
/
CREATE TYPE client_objtyp AS OBJECT (
    ID_Client       NUMBER(7,0),
    Phones        phone_vartyp
    );
/
CREATE VIEW client_objv OF client_objtyp
    WITH OBJECT IDENTIFIER (ID_Client)
    AS SELECT c.ID_Client,
        CAST(MULTISET(SELECT c.Phone1, c.Phone2, c.Phone3
            FROM Client c) AS phone_vartyp)
    FROM Client c; -- ORA-00932
/
CREATE VIEW client_objv OF client_objtyp
    WITH OBJECT IDENTIFIER (ID_Client)
    AS SELECT c.ID_Client,
       (SELECT CAST(c.Phone1, c.Phone2, c.Phone3) AS phone_vartyp FROM Client c) as phone_vartyp
    FROM Client c; -- ORA-00905
/
DROP TABLE Client;
DROP TYPE client_objtyp;
DROP TYPE phone_vartyp;

Answer 1

It seems you're overthinking this one. There's no need to use CAST or MULTISET or a subquery. The easiest way to create a VARRAY from a number of values is just to use the type as if it were a function and pass all of the elements as arguments.

The following appears to work:

CREATE OR REPLACE VIEW client_objv OF client_objtyp
    WITH OBJECT IDENTIFIER (ID_Client)
    AS SELECT c.ID_Client,
              phone_vartyp(c.Phone1, c.Phone2, c.Phone3)
    FROM Client c;