question

I have designed an Oracle database ( https://drive.google.com/file/d/0BzoFsxqhjRNDeXN2a3RLbzRCTVU/view?usp=sharing) as below. My question is, how do I define a function in the BodyModel_TYPE to display no. of single deck buses in each body model and while that function has to accept a specific body model as input/parameter. The method I have coded is like: ALTER TYPE BodyModel_TYPE ADD MEMBER FUNCTION cal_single_deck_bus RETURN int CASCADE; CREATE OR REPLACE TYPE BODY BodyModel_TYPE AS MEMBER FUNCTION cal_single_deck_bus RETURN int IS N int; BEGIN SELECT COUNT(t.BUSID) INTO N FROM SINGLEDECKBUS_TABLE T WHERE DEREF(t.BODYMODEL) = self; RETURN N; END cal_single_deck_bus; END; / It does not have an input and the returned result is not as expected though. The full sql code is as following: -- Engine Model create or replace type EngineModel_TYPE as object (EngineModelID int, EngineDescription varchar2(45), EngineType varchar2(25), EngineCapacity int); / create table EngineModel_TABLE of EngineModel_TYPE; alter table EngineModel_TABLE add constraint EngineModelID_PK primary key (EngineModelID); -- Body Model create or replace type BodyModel_TYPE as object (ModelID int, ModelName varchar2(45), FloorType varchar2(5), Manufacturer varchar2(45), Length number(8,2), Width number(8,2), NoOfAxles int); / create table BodyModel_TABLE of BodyModel_TYPE; alter table BodyModel_TABLE add constraint BodyModelID_PK primary key (ModelID); -- Bus create or replace type Bus_TYPE as object (BusID int, RegistrationNo VARCHAR2(10), RegistrationExpireDate date, EngineModel REF EngineModel_TYPE, BodyModel REF BodyModel_TYPE) NOT FINAL; / -- Single Decker Bus CREATE OR REPLACE TYPE SingleDeckBus_type UNDER Bus_type (NoOfDoors INT, SeatingCapacity int, StandingCapacity int); / CREATE TABLE SingleDeckBus_table OF SingleDeckBus_type; ALTER TABLE SingleDeckBus_table ADD CONSTRAINT SingleDeckBus_BusId_pk PRIMARY KEY (BusId); -- Double Decker bus CREATE OR REPLACE TYPE DoubleDeckBus_type UNDER Bus_type (LowerDeckSeatingCapacity int, LowerDeckStandingCapacity int, LuggageCapacity number(8,2), UpperDeckSeatingCapacity int); / CREATE TABLE DoubleDeckBus_table OF DoubleDeckBus_type; ALTER TABLE DoubleDeckBus_table ADD CONSTRAINT DoubleDeckBus_BusId_pk PRIMARY KEY (BusId); -- Accessory CREATE OR REPLACE TYPE Accessory_type AS OBJECT (AccessoryId INT, AccessoryName VARCHAR2(45), AccessoryDescription VARCHAR2(45), InstallDate DATE, SingleDeckBus REF SingleDeckBus_type, DoubleDeckBus REF DoubleDeckBus_type); / CREATE TABLE Accessory_table OF Accessory_type; ALTER TABLE Accessory_table ADD CONSTRAINT AccessoryId_pk PRIMARY KEY(AccessoryId); -- 2. Insert record statements (3 records for each table that support testing of your queries) insert into EngineModel_TABLE values (1, 'Description 1', 'Type 1', 10); insert into EngineModel_TABLE values (2, 'Description 2', 'Type 2', 20); insert into EngineModel_TABLE values (3, 'Description 3', 'Type 3', 30); insert into BodyModel_TABLE values (1, 'name 1', 'Type1', 'manufac1', 10.1, 5.1, 10); insert into BodyModel_TABLE values (2, 'name 2', 'Type2', 'manufac2', 10.2, 5.2, 20); insert into BodyModel_TABLE values (3, 'name 3', 'Type3', 'manufac3', 10.3, 5.3, 30); insert into SingleDeckBus_table SELECT 1, 'SA01', '1-Jan-2020', REF(E), REF(B), 1, 20, 10 from ENGINEMODEL_TABLE E, BODYMODEL_TABLE B where E.ENGINEMODELID = 1 AND B.MODELID = 1; insert into SingleDeckBus_table SELECT 2, 'SA02', '2-Jan-2020', REF(E), REF(B), 2, 25, 10 from ENGINEMODEL_TABLE E, BODYMODEL_TABLE B where E.ENGINEMODELID = 2 AND B.MODELID = 2; insert into SingleDeckBus_table SELECT 3, 'SA03', '3-Jan-2020', REF(E), REF(B), 3, 30, 10 from ENGINEMODEL_TABLE E, BODYMODEL_TABLE B where E.ENGINEMODELID = 3 AND B.MODELID = 3; insert into SingleDeckBus_table SELECT 55, 'SA03', '3-Jan-2020', REF(E), REF(B), 3, 30, 10 from ENGINEMODEL_TABLE E, BODYMODEL_TABLE B where E.ENGINEMODELID = 3 AND B.MODELID = 3; insert into DOUBLEDECKBUS_TABLE SELECT 4, 'SA04', '4-Jan-2020', REF(E), REF(B), 20, 10, 30.15, 20 from ENGINEMODEL_TABLE E, BODYMODEL_TABLE B where E.ENGINEMODELID = 1 AND B.MODELID = 1; insert into DOUBLEDECKBUS_TABLE SELECT 5, 'SA05', '5-Jan-2020', REF(E), REF(B), 25, 10, 35.15, 25 from ENGINEMODEL_TABLE E, BODYMODEL_TABLE B where E.ENGINEMODELID = 2 AND B.MODELID = 2; insert into DOUBLEDECKBUS_TABLE SELECT 6, 'SA06', '6-Jan-2020', REF(E), REF(B), 30, 10, 40.15, 30 from ENGINEMODEL_TABLE E, BODYMODEL_TABLE B where E.ENGINEMODELID = 3 AND B.MODELID = 3; insert into ACCESSORY_TABLE SELECT 1, 'Acc1', 'AccDesc1', '1-Mar-2017', REF(S), REF(D) from SINGLEDECKBUS_TABLE S, DOUBLEDECKBUS_TABLE D where s.BUSID = 1 AND d.BUSID = 4; insert into ACCESSORY_TABLE SELECT 2, 'Acc2', 'AccDesc2', '2-Mar-2017', REF(S), REF(D) from SINGLEDECKBUS_TABLE S, DOUBLEDECKBUS_TABLE D where s.BUSID = 2 AND d.BUSID = 5; insert into ACCESSORY_TABLE SELECT 3, 'Acc3', 'AccDesc3', '3-Mar-2017', REF(S), REF(D) from SINGLEDECKBUS_TABLE S, DOUBLEDECKBUS_TABLE D where s.BUSID = 3 AND d.BUSID = 6; -- 3. Create a method in the appropriate class to display how many single deck buses in each body model. ALTER TYPE BodyModel_TYPE ADD MEMBER FUNCTION cal_single_deck_bus RETURN int CASCADE; CREATE OR REPLACE TYPE BODY BodyModel_TYPE AS MEMBER FUNCTION cal_single_deck_bus RETURN int IS N int; BEGIN SELECT COUNT(t.BUSID) INTO N FROM SINGLEDECKBUS_TABLE T WHERE DEREF(t.BODYMODEL) = self; RETURN N; END cal_single_deck_bus; END; / commit;