|
Instantiating MDSYS.VERTEX_TYPE
Thursday June 17 2010 at 20:03
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.
        
|
SpatialDB Advisor
Home
Comment