Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Michael.

Asked: June 13, 2017 - 5:11 pm UTC

Last updated: June 14, 2017 - 4:30 am UTC

Version: Oracle Database 11g

Viewed 1000+ times

You Asked

Hello! I have the following error:

    G := GRADO_TYP(21, 30, 50);
         *
ERROR at line 9:
ORA-06550: line 9, column 10:
PLS-00307: too many declarations of 'GRADO_TYP' match this call
ORA-06550: line 9, column 5:
PL/SQL: Statement ignored

It happends when I try to call the constructor with 3 integer as parameters, what's weird for me is that it works when a use the constructor with only one FLOAT parameter
And my code is the following: 

<
-- DD: DECIMAL DEGREES
CREATE OR REPLACE TYPE GRADO_TYP AS OBJECT (
    GRADOS      INTEGER,
    MINUTOS     INTEGER,
    SEGUNDOS    INTEGER,
    CONSTRUCTOR FUNCTION    GRADO_TYP (G INTEGER, M INTEGER, S INTEGER)     RETURN SELF AS RESULT,
    CONSTRUCTOR FUNCTION    GRADO_TYP (DD FLOAT)                            RETURN SELF AS RESULT,
    MEMBER      FUNCTION    TO_DD     RETURN FLOAT,
    MEMBER      FUNCTION    TO_STRING RETURN VARCHAR2
)
/

CREATE OR REPLACE TYPE BODY GRADO_TYP IS
    CONSTRUCTOR FUNCTION GRADO_TYP (G INTEGER, M INTEGER, S INTEGER)
    RETURN SELF AS RESULT IS
    BEGIN 
        SELF.GRADOS     := G;
        SELF.MINUTOS    := M;
        SELF.SEGUNDOS   := S;
        RETURN;
    END;

    CONSTRUCTOR FUNCTION GRADO_TYP (DD FLOAT) 
    RETURN SELF AS RESULT IS
    BEGIN 
        SELF.GRADOS := FLOOR(DD);
        SELF.MINUTOS := FLOOR( (DD - SELF.GRADOS) * 60 );
        SELF.SEGUNDOS := ( DD - SELF.GRADOS - SELF.MINUTOS / 60 ) * 3600;
        RETURN;
    END;

    MEMBER FUNCTION TO_DD 
    RETURN FLOAT IS 
        DD FLOAT;
    BEGIN 
        DD := SELF.GRADOS + (SELF.MINUTOS / 60) + (SELF.SEGUNDOS / 3600);
        RETURN DD;
    END;

    MEMBER FUNCTION TO_STRING
    RETURN VARCHAR2 IS
        STR VARCHAR2(100);
    BEGIN
        STR := SELF.GRADOS || '° ' || SELF.MINUTOS || ''' ' || SELF.SEGUNDOS || '"';
        RETURN STR;
    END;

END;
/

-- TESTING DEGREES
DECLARE 
    G GRADO_TYP;
BEGIN
    DBMS_OUTPUT.PUT_LINE('PART I');
    G := GRADO_TYP(21.76);
    DBMS_OUTPUT.PUT_LINE(G.TO_DD);
    DBMS_OUTPUT.PUT_LINE(G.TO_STRING);
    DBMS_OUTPUT.PUT_LINE('PART II');
    G := GRADO_TYP(21, 30, 50);
    DBMS_OUTPUT.PUT_LINE(G.TO_DD);
    DBMS_OUTPUT.PUT_LINE(G.TO_STRING);
    DBMS_OUTPUT.PUT_LINE('FINISHED MAN');
END;
/

/>


I appreciate it very much before hands

and Connor said...

Every type has an automatic default constructor which is public variables in the type. So for a type of:

CREATE OR REPLACE TYPE GRADO_TYP AS OBJECT (
    GRADOS      INTEGER,
    MINUTOS     INTEGER,
    SEGUNDOS    INTEGER,
    CONSTRUCTOR FUNCTION    GRADO_TYP (G INTEGER, M INTEGER, S INTEGER)     RETURN SELF AS RESULT,
    CONSTRUCTOR FUNCTION    GRADO_TYP (DD FLOAT)                            RETURN SELF AS RESULT,
    MEMBER      FUNCTION    TO_DD     RETURN FLOAT,
    MEMBER      FUNCTION    TO_STRING RETURN VARCHAR2
)


there is already a inbuilt constructor of:

CONSTRUCTOR FUNCTION GRADO_TYP (
    GRADOS      INTEGER,
    MINUTOS     INTEGER,
    SEGUNDOS    INTEGER)


So when you do

GRADO_TYP(1,2,3)

there are *two* possible constructors - the automatic one and the one you built with parameters g,m and s.

Since yours is just doing the standard assignments anyway, just remove it and you should be good to go.


SQL> CREATE OR REPLACE TYPE GRADO_TYP AS OBJECT (
  2      GRADOS      INTEGER,
  3      MINUTOS     INTEGER,
  4      SEGUNDOS    INTEGER,
  5      CONSTRUCTOR FUNCTION    GRADO_TYP (DD FLOAT)                            RETURN SELF AS RESULT,
  6      MEMBER      FUNCTION    TO_DD     RETURN FLOAT,
  7      MEMBER      FUNCTION    TO_STRING RETURN VARCHAR2
  8  )
  9  /

Type created.

SQL>
SQL> CREATE OR REPLACE TYPE BODY GRADO_TYP IS
  2      CONSTRUCTOR FUNCTION GRADO_TYP (DD FLOAT)
  3      RETURN SELF AS RESULT IS
  4      BEGIN
  5          SELF.GRADOS := FLOOR(DD);
  6          SELF.MINUTOS := FLOOR( (DD - SELF.GRADOS) * 60 );
  7          SELF.SEGUNDOS := ( DD - SELF.GRADOS - SELF.MINUTOS / 60 ) * 3600;
  8          RETURN;
  9      END;
 10
 11      MEMBER FUNCTION TO_DD
 12      RETURN FLOAT IS
 13          DD FLOAT;
 14      BEGIN
 15          DD := SELF.GRADOS + (SELF.MINUTOS / 60) + (SELF.SEGUNDOS / 3600);
 16          RETURN DD;
 17      END;
 18
 19      MEMBER FUNCTION TO_STRING
 20      RETURN VARCHAR2 IS
 21          STR VARCHAR2(100);
 22      BEGIN
 23          STR := SELF.GRADOS || '° ' || SELF.MINUTOS || ''' ' || SELF.SEGUNDOS || '"';
 24          RETURN STR;
 25      END;
 26
 27  END;
 28  /

Type body created.

SQL> sho err
No errors.
SQL>
SQL> set serverout on
SQL> DECLARE
  2      G GRADO_TYP;
  3  BEGIN
  4      DBMS_OUTPUT.PUT_LINE('PART I');
  5      G := GRADO_TYP(21.76);
  6      DBMS_OUTPUT.PUT_LINE(G.TO_DD);
  7      DBMS_OUTPUT.PUT_LINE(G.TO_STRING);
  8      DBMS_OUTPUT.PUT_LINE('PART II');
  9      G := GRADO_TYP(21, 30, 50);
 10      DBMS_OUTPUT.PUT_LINE(G.TO_DD);
 11      DBMS_OUTPUT.PUT_LINE(G.TO_STRING);
 12      DBMS_OUTPUT.PUT_LINE('FINISHED MAN');
 13  END;
 14  /
PART I
21.76
21° 45' 36"
PART II
21.51388888888888888888888888888888888889
21° 30' 50"
FINISHED MAN

PL/SQL procedure successfully completed.

SQL>
SQL>



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library