varrays oracle ORA-02330

Question

I'm develop a simple app for learn about oracle and database object-relational with objects and varrays... I did the next code:

this is my varrays

SQL> create or replace type software_va as varray(3) of varchar2(30);
2 /

here is an object that I created:

SQL> create or replace type cargo1 as object(
 2  id_cargo number,
 3  nom_cargo varchar2(20),
 4  suc ref sucursal);
 5  /

when I try to create the table at this way:

SQL> create table cargos of cargo1(
2  primary key(id_cargo),
3  manejosoft software_va);

I got this error:

ERROR en line 3:
ORA-02330: datatype specification not allowed

I don't understand why I got this error and don't know if I have something wrong

Answer 1

If you want a relational table with both object and varray columns, this should work, and still has a primary key based on the object's ID:

create table cargos
(
    cargo cargo1,
    manejosoft software_va,
    constraint cargos_pk primary key (cargo.id_cargo)
);

Table created.

insert into cargos values (cargo1(1, 'test'), software_va('a', 'b', 'c'));

1 row created.

insert into cargos values (cargo1(1, 'dup test'), software_va('d', 'e', 'f'));

insert into cargos values (cargo1(1, 'dup test'), software_va('d', 'e', 'f'))
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.CARGOS_PK) violated

select * from cargos;

CARGO(ID_CARGO, NOM_CARGO)
--------------------------------------------------------------------------------
MANEJOSOFT
--------------------------------------------------------------------------------
CARGO1(1, 'test')
SOFTWARE_VA('a', 'b', 'c')

select c.cargo.nom_cargo
from cargos c
where c.cargo.id_cargo = 1;

CARGO.NOM_CARGO
--------------------
test

If you wanted an object table then you couldn't have the varray column as mentioned in comments:

create table cargos of cargo1
(
    primary key(id_cargo)
);