Instantiating MDSYS.VERTEX_TYPE

Recently I was contacted about the fact that my free PL/SQL packages could not compile on 11gR2 due to changes in the MDSYS.VERTEX_TYPE structure.

This structure has given me lots of grief over the years. At 9i the structure only had four elements: X, Y, Z and W. But at 10g, the ID element was added. Now, at 11g the structure looks like this:

 Create type vertex_type as object
 (X  number,
  Y  number,
  Z  number,
  W  number,
  v5 number,
  v6 number,
  v7 number,
  v8 number,
  v9 number,
  v10 number,
  v11 number,
  ID number );

The problem I have had over the years is how to instantiating a MDSYS.VERTEX_TYPE structure within my own PL/SQL so that I could interchange structures with Oracle functions.

The approach I initially took was to instantiate (in 10g) this way:

 v_vertex mdsys.vertex_type := mdsys.vertex_type(NULL,NULL,NULL,NULL,NULL);

Problem is that it doesn’t work in 9i (one less field) or in 11g!

My initial response was to create my own, more stable, structure, but I what I don’t want to do is compete with Oracle!

The change I made in response to my customer’s problem was an idea from left-field that I wish I had had a few years ago! That is, instantiate this way:

 v_vertex mdsys.vertex_type := mdsys.sdo_util.getVertices(mdsys.sdo_geometry(2001,NULL,MDSYS.SDO_POINT_TYPE(NULL,NULL,NULL),NULL,NULL);

It doesn’t matter that I have given the Oracle utility a point, it wil still return a fully specified structure!

Cool.

Now, I can let the Oracle programmers handle the problems between versions via their programming of getVertices than me having to worry about it.

Happy PL/SQL programming.