I have created the below table stafftable but when I'm trying to insert it's showing an er-CodePudding

CREATE TYPE accounttype AS OBJECT( no varchar2(10), name varchar2(10), balance number(10), dob date, member function age return number );

CREATE TYPE BODY accounttype AS
MEMBER FUNCTION age RETURN NUMBER
AS
BEGIN
RETURN FLOOR(MONTHS_BETWEEN(sysdate,dob)/12);
END age;
END;
/

CREATE TYPE account_branchtype AS OBJECT( account REF accounttype, branch  varchar2(10) );

create type account_branchtabletype as table of account_branchtype;

create type stafftype as object(staff_id varchar2(20),name varchar2(20) ,sal number(20), other_details varchar2(20) , emp8 account_branchtabletype ,dob date , member function getage return number);

create or replace type body stafftype as member function getage return number
as
begin
return(round((sysdate-dob)/365));
end getage;
end;
/

create table stafftable of stafftype nested table emp8 store as relaccount_branch8;

insert into stafftable values(stafftype('S01','Captain','account',20000,'abc','24-apr-1993'));
insert into stafftable values(stafftype('S02','Thor','manager',30000,'pqr','14-jun-1993'));

insert into account_branchtable values('B01','manager','andheri',stafftabletype(stafftype('S01','Captain','account',20000,'abc','24-apr-1993')));
insert into account_branchtable values('B02','asst manager','sion',stafftabletype(stafftype('S02','Thor','manager',30000,'pqr','14-jun-1993')));

Showing error as inconsistent datatypes: expected schema.ACCOUNT_BRANCHTABLETYPE got CHAR when I'm trying to insert data into Stafftable.

Fiddle = https://dbfiddle.uk/zDdqEJdx.

CodePudding user response：

You do not have a table account_branchtable (and you probably don't want it).

What you probably want is to create a collection of REFs:

create type account_branchtabletype as table of REF account_branchtype;

Then create your staff type:

create type stafftype as object(
  staff_id      varchar2(20),
  name          varchar2(20),
  sal           number(20),
  other_details varchar2(20),
  emp8          account_branchtabletype,
  dob           date,
  member function getage return number
);

create or replace type body stafftype as member function getage return number
as
begin
return FLOOR(MONTHS_BETWEEN(sysdate,dob)/12);
end getage;
end;
/

Then create the table:

create table stafftable of stafftype (
  staff_id PRIMARY KEY
) nested table emp8 store as relaccount_branch8;

ALTER TABLE relaccount_branch8 ADD SCOPE FOR (COLUMN_VALUE) IS account_branch;

Then you can insert the staff with the nested table values:

insert into stafftable (staff_id, name, sal, other_details, emp8, dob)
values(
  'S01',
  'Captain',
  20000,
  'abc',
  account_branchtabletype(
    (SELECT REF(b) FROM account_branch b WHERE b.account.no = '19DCS001' AND b.branch = 'Manjalpur')
  ),
  DATE '1993-04-24'
);

fiddle