This is my first question. So Im trying to use act_no varchar2(10), act_name varchar2(10) of type accounttype in account_branchtype. So I entered the command = create or replace type account_branchtype as object(act_no ref accounttype, act_name ref accounttype, act_branch varchar2(10));

But its not working. I have pasted the code please help me how to use act_no, act_name in account_branchtype.

create or replace type  accounttype as object(act_no varchar2(10), act_name varchar2(10),      act_balance number(10), act_dob date, member function age return number);

create or replace type body accounttype as member function age return number
2 as
3 begin
4 return(round((sysdate-dob)/365));
5 end age;
6 end;
7 /

create or replace type account_branchtype as object(act_no ref accounttype, act_name ref accounttype, act_branch varchar2(10));

create or replace type account_citytype as object(account ref accounttype, account ref accounttype, act_city varchar2(10), act_ pincodenumber(6), act_ state varchar2(15));

create table account of accounttype;

insert into account values(accounttype('19DCS001','Rajesh','35000','12-JUL-2001'));
insert into account values(accounttype('19DCS002','Shyam','30000','05-NOV-1993'));
insert into account values(accounttype('19DCS003','Bimal','55000','12-DEC-1997'));
insert into account values(accounttype('19DCS004','Neel','46000','31-JAN-2000'));
insert into account values(accounttype('19DCS005','Tushar','37900','27-FEB-2002'));

select * from account;

create table account_branch of account_branchtype;

insert into account_branch values(account_branchtype ('19DCS001','Rajesh','Manjalpur'));
insert into account_branch values(account_branchtype ('19DCS002','Shyam','MG Road'));
insert into account_branch values(account_branchtype ('19DCS003','Bimal','Mayapuri'));
insert into account_branch values(account_branchtype ('19DCS004','Neel','Borivali'));
insert into account_branch values(account_branchtype ('19DCS005','Tushar','Ghogha'));

select * from account_branch;

create table account_city of account_citytype;

insert into account_city values(account_citytype ('19DCS001','Rajesh','Vadodara','390011','Gujarat'));
insert into account_city values(account_citytype ('19DCS002','Shyam','Bangalore','400032','Karnataka'));
insert into account_city values(account_citytype ('19DCS003','Bimal','Delhi','110064','Delhi'));
insert into account_city values(account_citytype ('19DCS004','Neel','Mumbai','400092','Maharastra'));
insert into account_city values(account_citytype ('19DCS005','Tushar','Bhavnagar','364110','Gujarat'));

I tried many things but couldn't find a proper solution. I want to use act_no varchar2(10), act_name varchar2(10) from type accounttype in account_branch. Please help me.

CodePudding user response：

Don't try to repeat the columns, use third normal-form and ensure your data has a single source of truth and then when you want to display the account name you can get the value from the object reference.

Create your types as:

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_citytype AS OBJECT(
  account ref accounttype,
  city    varchar2(10),
  pincode number(6),
  state   varchar2(15)
);

Note: you can use the act_ prefix everywhere if you want but you know it is related to an account as it is in a table with a name starting with "account" so that just seems like lots of unnecessary typing.

and your tables as:

create table account of accounttype (
  no CONSTRAINT account__no__pk PRIMARY KEY
);

create table account_branch of account_branchtype (
  account SCOPE IS account
);

create table account_city of account_citytype (
  account SCOPE IS account
);

Then you can insert the data using:

insert into account (no, name, balance, dob)
  values('19DCS001','Rajesh','35000', DATE '2001-07-12');

insert into account_branch (account, branch)
  values( (SELECT REF(a) FROM account a WHERE no = '19DCS001'),'Manjalpur');

insert into account_city (account, city, pincode, state)
  values(
    (SELECT REF(a) FROM account a WHERE no = '19DCS001'),
    'Vadodara',
    '390011',
    'Gujarat'
  );

Then:

SELECT a.*,
       a.age() AS age
FROM   account a;

Outputs:

and:

SELECT b.account.no,
       b.account.name,
       b.branch
FROM   account_branch b;

Outputs:

and:

SELECT c.account.no,
       c.account.name,
       c.city,
       c.pincode,
       c.state
FROM   account_city c;

Outputs:

fiddle