CODEWITHSUNDEEP
oracle-databaseplsqluser-defined-types
Siqueira
Siqueira

Reputation: 443

PLS-00539 and PLS-00538 errors when creating UDT functions

I am trying to create a type hierarchy with types using Oracle, but I am getting the errors PLS-00539 and PLS-00538 when creating its functions.

The code I am using to create the types is:

CREATE OR REPLACE TYPE VC_MNumber AS Object (   
  idno NUMBER,
  MEMBER FUNCTION  dimension   RETURN NUMBER ,
  MEMBER FUNCTION typeName RETURN VARCHAR2) NOT INSTANTIABLE NOT FINAL;
/

DROP TYPE VC_MINTEGER;

CREATE OR REPLACE TYPE VC_MInteger UNDER VC_MNumber (   
  OVERRIDING MEMBER FUNCTION  dimension   RETURN NUMBER ,
  OVERRIDING MEMBER FUNCTION typeName RETURN VARCHAR2);
/

CREATE OR REPLACE TYPE VC_MReal UNDER VC_MNumber(   
  YDCCoeff NUMBER,
  CbDCCoeff NUMBER,
  CrDCCoeff NUMBER,
  OVERRIDING MEMBER FUNCTION  typeDimension   RETURN NUMBER, 
  OVERRIDING MEMBER FUNCTION typeName RETURN VARCHAR2) FINAL;
/

Everything okay up to this point, but when I try to create the functions:

CREATE OR REPLACE TYPE BODY VC_MReal AS
  OVERRIDING MEMBER FUNCTION typeDimension RETURN NUMBER IS
    var_dimension       number :=  10;
    BEGIN
        RETURN var_dimension;
    END;
   MEMBER FUNCTION typeName RETURN VARCHAR2 IS
    var_typeName        VARCHAR2(400) :=  'VC_MREAL';
    BEGIN
        RETURN var_typeName;
    END;
END;
/

I get the errors PLS-00539 and PLS-00538. I don't know what I am doing wrong. I'd appreciate a hand on this. Where is the mistake?

Upvotes: 0

Views: 1996

Answers (1)

XING
XING

Reputation: 9886

PLS-00538: subprogram or cursor 'TYPENAME' is declared in an object type specification and must be defined in the object type body PLS-00539: subprogram 'TYPENAME' is declared in an object type body and must be defined in the object type specification

There are few mistakes you made while declaration see explaination inline:

CREATE OR REPLACE TYPE VC_MNumber AS OBJECT
(
   idno NUMBER,
   MEMBER FUNCTION dimension
      RETURN NUMBER,
   MEMBER FUNCTION typeName
      RETURN VARCHAR2
)
   NOT INSTANTIABLE NOT FINAL;
/

--DROP TYPE VC_MINTEGER;

CREATE OR REPLACE  TYPE VC_MInteger
   UNDER VC_MNumber
   (
      OVERRIDING MEMBER FUNCTION dimension
         RETURN NUMBER,
      OVERRIDING MEMBER FUNCTION typeName
         RETURN VARCHAR2
   );
/

CREATE OR REPLACE  TYPE VC_MReal
   UNDER VC_MNumber
   (
      YDCCoeff NUMBER,
      CbDCCoeff NUMBER,
      CrDCCoeff NUMBER,
      OVERRIDING MEMBER FUNCTION Dimension --<--Overriding function should have same name
         RETURN NUMBER,
      OVERRIDING MEMBER FUNCTION typeName
         RETURN VARCHAR2
   )
    FINAL;
/

CREATE OR REPLACE TYPE BODY VC_MReal
AS
   OVERRIDING MEMBER FUNCTION Dimension
      RETURN NUMBER
   IS
      var_dimension   NUMBER := 10;
   BEGIN
      RETURN var_dimension;
   END;

   OVERRIDING MEMBER FUNCTION typeName  --<-- You declared in Object type as `overriding` function and using it as `Member function` alone.
      RETURN VARCHAR2
   IS
      var_typeName   VARCHAR2 (400) := 'VC_MREAL';
   BEGIN
      RETURN var_typeName;
   END;
END;
/

Upvotes: 1

Related Questions