Go to content Go to navigation and search

Home

Current Oracle Spatial Blogs

Search

RSS / Atom

Email me

textpattern

Instantiating MDSYS.VERTEX_TYPE

· Jun 17, 08:03 pm by Simon Greener

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.

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Newsvinepost this at Ma.gnoliapost this at Furlpost this at Blinklistpost this at Spurlpost this at Wistspost this at Simpypost this at Redditpost this at Farkpost this at Blogmarkspost this at Yahoo! my webpost this at Mr. Wongpost this at Windows Livepost this at Google Bookmarkspost this to Twitter
  Textile Help