I got error "ora-03001: unimplemented feature" when I try to insert a record on table below. I've searched all night, still no luck. I'm using Oracle10g express edition.
create or replace type MajorsType As varray(20) of varchar2(10);
create or replace type studenttype as object (
stuID varchar2(5),
lastName varchar2(15),
firstName varchar2(12),
majors MajorsType)
INSTANTIABLE
NOT FINAL;
create table Student of StudentType (
constraint student_stuID_pk PRIMARY KEY(stuID));
INSERT INTO student values StudentType ('S999', 'Smith', 'John', MajorsType('Math', 'Accounting'));
It's a simple syntax error: the VALUES clause requires everything to be wrapped in parentheses:
SQL> INSERT INTO student
2 values ( StudentType ('S999', 'Smith', 'John', MajorsType('Math', 'Accounting')))
3 /
1 row created.
SQL>
This applies whether we're passing in several scalar values or a single type.
The one case when it doesn't apply is an insert in PL/SQL using a RECORD type. Which is not relevant to your situation, but I'm mentioning it for completeness.
Inserting a RECORD variable would look something like this
declare
r23 t23%rowtype; -- record declaration
begin
r23.id := 1;
r23.created := sysdate;
-- insert using record variable
insert into t23
values r23;
end;