Your problem is that you have defined two constructors of the same type, but you didn't realize you did probably.
When you create an object type, Oracle creates a default constructor, with parameters that match the parameters of the type. Therefore, when you also defined a constructor with only lattitude and longitude as inputs, Oracle cannot work out which constructor to call, the default created one, or your one, so it errors with:
SQL Error: ORA-06553: PLS-307: too many declarations of 'POSITION' match this call
To fix this, you can simplify your code:
drop type position;
CREATE OR REPLACE TYPE Position AS OBJECT (longitude NUMBER(11,7), lattitude NUMBER(11,7))
/
DROP TABLE District_Info;
CREATE TABLE District_Info( Dname VARCHAR2(20), DPos Position, Boundary_dist VARCHAR2(20), Launch_ghat CHAR(1) );
INSERT INTO District_Info (Dname,DPos,Boundary_dist,Launch_ghat) VALUES ('d',Position(1.1),'gr','y');
Ie, you don't need the constructor declaration or the body. If you like, you can have a DIFFERENT constructor, eg:
CREATE OR REPLACE TYPE Position AS OBJECT (longitude NUMBER(11,7), lattitude NUMBER(11,7),
-- Define a constructor that has only 2 parameters.
CONSTRUCTOR FUNCTION position(i_longitude NUMBER)
RETURN SELF AS RESULT
)
/
CREATE OR REPLACE TYPE BODY Position AS
CONSTRUCTOR FUNCTION position(i_longitude NUMBER)
RETURN SELF AS RESULT IS
BEGIN
SELF.longitude := i_longitude;
SELF.lattitude := i_longitude;
RETURN; -- self;
END;
END;
/
show errors;
DROP TABLE District_Info;
CREATE TABLE District_Info( Dname VARCHAR2(20), DPos Position, Boundary_dist VARCHAR2(20), Launch_ghat CHAR(1) );
DESC District_Info;
INSERT INTO District_Info (Dname,DPos,Boundary_dist,Launch_ghat) VALUES ('d',Position(1.1),'gr','y');
