Top 5 Recent Articles
- Biography (1)
- Blog (41)
- Changes (1)
- Customers (1)
- Data Models (1)
- Education (2)
- General Software (21)
- Georaptor Blog (5)
- Image Catalog (2)
- Licensing (1)
- ManifoldGIS (3)
- MySQL Blog (4)
- MySQL Spatial (3)
- Networking and Routing (including Optimization) (3)
- Oracle Spatial (171)
- Philosophy (1)
- PostGIS (30)
- Press Releases (1)
- Source code (24)
- Space Curves (1)
- Spatial DB comparison (1)
- SQL (1)
- SQL Server Blog (58)
- SQL Server Spatial (General) (15)
- SQL Server Spatial (LRS) (37)
- Stored Procedure (2)
- Training (1)
- XML (5)
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!
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.