too many declaration of a construct match this call-CodePudding

I have declared an object with a constructor that takes 2 arguments. I can't use it because oracle returns:

[Error] Execution (2: 10): ORA-06553: PLS-307: too many declarations of 'BOUNDARY' match this call

I haven't declared this constructor more than one time.
My code is working on db fiddle but not on my database. The only difference between the code on dbfiddle and my code is that this object has an owner.
Therefore the name of the object is defined this way: owner_name.object_name.
I have checked if the object is defined in another owner. And when I call the constructor I use owner_name.constructor.
I've created a dummy constructor that take 0 arguments and it works.

CREATE OR REPLACE TYPE my_user.boundary AS OBJECT
(
    v_start INTEGER,
    v_end INTEGER,
    CONSTRUCTOR FUNCTION boundary (i_start INTEGER, i_end INTEGER)
        RETURN SELF AS RESULT,
    MEMBER FUNCTION isInside (i INTEGER)
        RETURN INTEGER
);

CREATE OR REPLACE TYPE BODY my_user.boundary
AS
    CONSTRUCTOR FUNCTION boundary 
        RETURN SELF AS RESULT
    IS
    BEGIN
        v_start := 1;
        v_end := 2;
        RETURN;
    END;
    
    CONSTRUCTOR FUNCTION boundary (i_start INTEGER, i_end INTEGER)
        RETURN SELF AS RESULT
    IS
    BEGIN
        v_start := i_start;
        v_end := i_end;
        RETURN;
    END;

    MEMBER FUNCTION isInside (i INTEGER)
        RETURN INTEGER
    IS
    BEGIN
        IF v_start <= i AND i <= v_end
        THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;
END;

SELECT ( my_user.boundary(1,2)) FROM DUAL; --doesn't work

[Error] Execution (2: 10): ORA-06553: PLS-307: too many declarations of 'BOUNDARY' match this call

SELECT ( my_user.boundary()).isInside(1) FROM DUAL; --is working

1

CodePudding user response：

Your code works in db<>fiddle when run under Oracle 21c, but not under 18c or 11g.

You said you haven't declared the constructor more than once, but you sort of have; there is a default constructor with the same parameters - from the 19c doc:

The database implicitly defines a constructor method for each user-defined type that you create. A constructor is a system-supplied procedure that is used in SQL statements or in PL/SQL code to construct an instance of the type value. The name of the constructor method is the name of the user-defined type. You can also create a user-defined constructor using the constructor_spec syntax.

and:

By default, the system implicitly defines a constructor function for all object types that have attributes.

A system-defined constructor is sometimes known as the attribute value constructor.

You are creating a user-defined constructor, but it has the same definition as the implicit one. Well, almost.

19c also says:

a user-defined constructor does hide, and thus supersede, the attribute-value constructor for its type if the signature of the user-defined constructor exactly matches the signature of the attribute-value constructor.

For the signatures to match, the names and types of the parameters (after the implicit SELF parameter) of the user-defined constructor must be the same as the names and types of the attributes of the type.

In 18c it works if you change the parameter names to match the attribute names:

CREATE OR REPLACE TYPE boundary AS OBJECT
(
    v_start INTEGER,
    v_end INTEGER,
    CONSTRUCTOR FUNCTION boundary (v_start INTEGER, v_end INTEGER)
        RETURN SELF AS RESULT,
    MEMBER FUNCTION isInside (i INTEGER)
        RETURN INTEGER
);
/

CREATE OR REPLACE TYPE BODY boundary
AS
    CONSTRUCTOR FUNCTION boundary (v_start INTEGER, v_end INTEGER)
        RETURN SELF AS RESULT
    IS
    BEGIN
        SELF.v_start := v_start;
        SELF.v_end := v_end;
        RETURN;
    END;
...

21c seems to be more forgiving/flexible, though the documentation hasn't changed. (Or maybe 19c, or some version of it, also allows this - I'm just going on the available versions in db<>fiddle..)

However, as all you are doing here is a simple assignment, you don't need your overriding constructor in any version - it works without it.

CodePudding user response：

After I added the undeclared constructor to the spec it compiled and worked fine:

CREATE OR REPLACE TYPE BV_OWN.boundary AS OBJECT
(
    v_start INTEGER,
    v_end INTEGER,
    CONSTRUCTOR FUNCTION boundary   -- added
        RETURN SELF AS RESULT,
    CONSTRUCTOR FUNCTION boundary (i_start INTEGER, i_end INTEGER)
        RETURN SELF AS RESULT,
    MEMBER FUNCTION isInside (i INTEGER)
        RETURN INTEGER
);

Had to drop the schema specs since I didn't have a BV_OWN user to test with. Used Oracle 21c. db<>fiddle here