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

It looks like you're trying to mix an [object table](http://docs.oracle.com/cd/E11882_01/server.112/e25789/tablecls.htm#sthref177) with a relational table that has a `varray` column; you're trying to add the `software_va` column (?) in the [object properties](http://docs.oracle.com/cd/E11882_01/server.112/e17118/statements_7002.htm#i2126768) clause, which isn't going to work. Did you want a relational table with both `object` and `varray` columns, maybe? — Alex Poole, Sep 12 '12 at 15:41

Alex Poole · Accepted Answer · 2012-09-12T16:49:46.927

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)
);

varrays oracle ORA-02330

1 Answers1