I am quite new to oracle and plsql and I usually write in python. I need to create member functions under subtypes and do not understand what the error PLS-00538 and PLS-00539 are referring to. Here is my code:
create or replace type album_type as object
(albumTitle varchar(50),
albumPlaytime number(3), -- minutes
albumReleaseDate date,
albumGenre varchar(15),
albumPrice number(9,2),
albumTracks number(2),
member function discountPrice return number)
return integer)
not instantiable not final
/
create or replace type disk_type under album_type
( mediaType varchar(10),
diskNum number(2), -- number of disks
diskUsedPrice number(9,2),
diskDeliveryCost number(9,2),
overriding member function discountPrice return number)
/
create or replace type mp3_type under album_type
(downloadSize number, -- size in MB
overriding member function discountPrice return number)
/
Currently I am just trying to compile body types to try and debug my errors. The below code is working but not positive it is correct:
create or replace type body album_type as
member function discountPrice return number is
begin
return albumprice;
end discountPrice;
end;
/
However, when applying the same on the subtype it gives the errors: 1) PLS-00539: subprogram 'DISCOUNTPRICE' is declared in an object type body and must be defined in the object type specification. 2) PLS-00538: subprogram or cursor 'DISCOUNTPRICE' is declared in an object type specification and must be defined in the object type body
create or replace type body disk_type as
member function discountPrice return number is
discount number;
begin
discount:= round(albumprice - albumprice*0.20,0);
return discount;
end discountPrice;
end;
/
Ultimately, I want the function to give a 20 % discount. Logically it would be:
if mediaType == 'Audio_CD':
price = albumprice - albumprice*0.20
The function declaration in the type body must exactly match the declaration in the type specification. In this case, the keyword OVERRIDING
is missing.
create or replace type body disk_type as
overriding member function discountPrice return number is
discount number;
begin
discount:= round(albumprice - albumprice*0.20,0);
return discount;
end discountPrice;
end;
/
Also, the code has a mistake around return integer)
, but I'm guessing that's just a typo.