Fun with Oracle Spatial's SDO_TOPO_GEOGRAPHY type
-
Some of you may know that the app I work on has some tables that store Oracle spatial data.
While I don't generally deal with the Oracle Spatial app (two apps, one DB), I do need to for one of my application's search pages.
Unfortunately, this search is currently broken.
So, here are a few Oracle WTFs,
WTF #1: Using Oracle.
Err... sorry, force of habit.
Real WTF #1
While I was doing debugging, I split the really long compound SQL statement into shorter SQL statements and started executing them.
select feature_road_physref from physref where physref_number = 12345;
returned
FEATURE_ROAD_PHYSREF ----------------------------------------- MDSYS.SDO_TOPO_GEOMETRY(2,345678.90,12,3)
...which told me the row I wanted contained a
SDO_TOPO_GEOMETRY
Next step is to change the call to call
SDO_TOPO_GEOMETRY
'sget_geography
, to get anSDO_GEOMETRY
object back:select feature_road_physref.get_geometry() from physref where physref_number = 12345;
which helpfully tosses back:
ORA-00904: "FEATURE_ROAD_PHYSREF"."GET_GEOMETRY": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 9 Column: 8
Wait, what, invalid identifier?
Well, yes. Because apparently in Oracle, if you want to call a method on an oracle type that's stored in the database, you need a table alias instead of a table.
Edit: Before someone points this out: Specifying the table name alone also doesn't work. It has to be an alias.
Real WTF #2
OK, so we change the query to use a table alias:
select p.feature_road_physref.get_geometry() from physref p where p.physref_number = 5613;
which gives you this helpful error:
ORA-00907: missing right parenthesis ORA-06512: at "MDSYS.SDO_TOPO_GEOMETRY", line 1163 00907. 00000 - "missing right parenthesis" *Cause: *Action:
Oh hey, turns out there's a bug in this Oracle built-in type that was introduced in one of the Oracle 11 updates. Thanks, Oracle!
-
can we get a combined Side Bar WTF / I Hate Oracle Club category in the category dropdown?
Why would we need a I Hate Oracle Club subcategory when it already is one on its own?
-
Why would we need a I Hate Oracle Club subcategory when it already is one on its own?
Right now, I can't set the category to both Side Bar WTF and I Hate Oracle Club... only one or the other.
-
-
Well, it wasn't until I really looked at the list right now that I realized the cateogories grouped together like this have a parent/child relationship. Whoops.
-
Well, it wasn't until I really looked at the list right now that I realized the cateogories grouped together like this have a parent/child relationship.
Only two levels. And
I Hate Oracle
is at the parent level.IHO
is unlikely to be moved, and certainly not intoSideBar
- definately the parent of the latter, and possibly the children, are the source of content on the front page:
-
Filed Under: TAG WITH ALL OF THE THINGS TOO
-
Side note: One of our contractors still has an Oracle 11.0.x instance, while we have all Oracle 11.2.x installs.
WTF #2 isn't present in Oracle 11.0.x.
-
In case anyone was curious, we filed a support request with Oracle with steps to reproduce the issue.
Oracle tested it and found out it was indeed a bug in Oracle 11.2.0.3.
So, Oracle has filed their own bug about it. However, unless we escalate the bug, it's unknown how long it will take to fix them.
This affects any part of our apps that has to turn Topology Geometry data into generic Geometry data. Which is really anything that needs to run commands from Oracle's spatial packages.
-
Been there; done that. I think that issue is fixed by patch:18907724 which has a fix for bug:13945688 which sounds like your issue.
-
Possibly, but I don't have an Oracle support account to check it.
I do know that they opened a new bug 21035042 for this issue.
-
Ouch, that does look like a different issue. The bug I referred to was get_geometry returning invalid geometries. I wonder how we never hit that issue.
-
Well, it sounds like it may only affect Oracle 11.2.0.3, so if you're using a newer (maybe) or older version, you won't be hit by it.
-
We were on 11.2.0.3, but we upgraded to 11.2.0.4 because of some other spatial bugs, but that introduced new spatial bugs, so now our maps database is on 12c.
-
It's like pulling teeth getting software upgraded around here. Hell, that's probably why we're still on Oracle Application Server.
Then again, we also have the "which J2EE server should we move to" merry-go-round still moving. Oh look, WebLogic just went by... Websphere is just up ahead... oh hey, there's JBoss in the distance.
-
Hopefully, they get you a patch soon then.
-
oh hey, there's
JBossWildfly in the distance.I think they must have hired the GIMP guys to come up with their new name.
-
The paid version is still called JBoss, and being a large organization, we'd probably go for the paid version.
-
Yes, but that's the last (major) version called that. We use the paid version, BTW.
-
You mean you aren't using the [Cool Open Source Hipster Version]?
-
It's fun, because we use EAP6, which uses AS7 (so it seems like you're using "JBoss 6" but when you look at places like SO you have to look for "JBoss 7" because JB6 is really totally different). And a bunch of other random ass numbers for random ass components that who knows what the fuck they actually do.
Oh, and I think we're currently on 11.0.2.3, though the DBA has been talking about upgrading to 12.