CODEWITHSUNDEEP
sqloracle-databasefunctionplsqlmember
d789w
d789w

Reputation: 377

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

Upvotes: 0

Views: 1284

Answers (1)

Jon Heller
Jon Heller

Reputation: 36807

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.

Upvotes: 1

Related Questions