Search code examples
sqloracle-databasefunctionplsqlmember

Creating member functions in oracle plsql


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

Solution

  • 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.