I have a dotnet application writing data (including spatial data) to my Oracle Spatial database via ODP.NET. I have successfully implemented half-a-dozen project-based datasets where the application works fine. In one isolated case, I have identified an INSERT transaction where all simple attribute data is successfully written to the database, but the Oracle SDO_GEOMETRY (spatial object) fails to get inserted. (It appears as a NULL when I interrogate the SDO_GEOMETRY in a test trigger.)
The dotnet application log shows the insert statement as follows:
CommandText : INSERT INTO DSV_DFP_DISTURBANCES (DISTURBANCE_ZONE_TYPE, DISTURBANCE_DETAILS_1, DISTURBANCE_DETAILS_2, DISTURBANCE_DETAILS_OTHER, INFRASTRUCTURE_TYPE_1, INFRASTRUCTURE_TYPE_2, INFRASTRUCTURE_TYPE_3, INFRASTRUCTURE_TYPE_4, INFRASTRUCTURE_TYPE_5, PROPOSED_DATE, ACTUAL_DATE, APPROVAL_STATUS, APPROVAL_COMMENTS, COMMENTS, SOURCE, PARENT_UFID, UFID, GBM_DEVICE_ID, OFFICER, PLATFORM, GBM_EDIT_TIME, GBM_EDIT_FLAG, DISCRIMINANT, MI_STYLE, GEOLOC) VALUES (:p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14, :p15, :p16, :p17, :p18, :p19, :p20, :p21, :p22, :p23, SDO_GEOMETRY(:p24, 8307))
Parameters : :p0 = Non-limited Activities; :p1 = -; :p2 = -; :p3 = ; :p4 = Road - Existing (No New Disturbance); :p5 = -; :p6 = -; :p7 = -; :p8 = -; :p9 = 7/11/2012 12:00:00 AM; :p10 = ; :p11 = Proposed; :p12 = ; :p13 = ; :p14 = ; :p15 = ; :p16 = 969F9B41ECBB678; :p17 = SAN42805; :p18 = molja; :p19 = P; :p20 = 41220.5927125463; :p21 = 2; :p22 = Proposed_Disturbance; :p23 = Pen (2,2,7397376) Brush (17,7397376); :p24 = Oracle.DataAccess.Types.OracleBlob
I have convinced myself that the problem lies somewhere in the interface between the database and the dotnet application - possibly in the area of data dictionary metadata. (See rationale below.) However I am unable to proceed with any further black-box diagnosis on my own.
* I don't have ready access to the dotnet application developers - but if I can come up with a specific question to ask them, then I may be able to start a dialog. (I can't, for example, approach them with the open-ended questions I have at present.)
* I don't know anything about dotnet or ODP myself, and don't have access to any suitably qualified people to help on-site with my black box testing.
Under the circumstances, I call upon the accumulated wisdom of the forums for help!
h2. Scenario Details
* I have a set of two spatial tables over which I have a view, with some complex select logic (only one spatial column in the view)
* I have an INSTEAD OF trigger to intercept all INSERT, UPDATE and DELETE actions on that view, to essentially unravel the complex logic and make the relevant edits in the associated base tables
* I can execute an insert statement using SQL*Plus to insert data - including a spatial geometry - into the view. The data all flows through to the base tables.
* When I attempt the same insert action using a dotnet application (black box :( ) I am getting simple attribute data written to the view, but there is no geometry object being written to the view. A reasonably low-level log file from the application indicates that the insert transaction completed successfully - i.e. no errors. However in the database (from further testing) the SDO_GEOMETRY object is being inserted as NULL.
* When I replace the (complex) view with a table of the same name (create table as select * from renamed view) and then try the dotnet application the geometry object does get written to the table, along with all attribute data.
* When I rename this table and recreate my view as a simple "select * from table" and then reapply my complex INSTEAD OF trigger to this new, simple view, the dotnet application successfully writes to the view (and the trigger writes all data - including the spatial object - into the underlying tables.)
* When I replace all the test infrastructure with my original complex view and trigger, the spatial geometry again fails to be written to the view.
Now here's the catch...
* I have a set of six such datasets, all modelled exactly the same way, with multi-table views and INSTEAD OF triggers, all doing the same things (but with different attribute fields) and only one of these datasets fails to insert the spatial object from the dotnet application. All other complex view-and-trigger datasets have geometries written to them by the dotnet application without a problem!
* Database is Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - 64bit Production, running on Exadata box
* Dotnet application is running as a web service under IIS on a separate Windows box. I should be able to dig out OS, .NET and ODP versions if that is likely to help...
* The fact that I can capture the insert statement from the dotnet application logs, "mock up" an equivalent insert statement in SQL*Plus and it works leads me to conclude that there is nothing wrong with my database views and triggers, per se.
* The fact that I can replace the complex view with a simple table of exactly the same structure and have the dotnet application work correctly leads me to conclude that there must be something different about the way that the dotnet application "sees" the complex view, compared with the simple table.
* The fact that I have five other datasets all modelled exactly the same way, with same multi-table complex spatial view logic and INSTEAD OF triggers, and these all work fine, leads me to conclude that there must be something different about the way that the dotnet application "sees" this particular view compared with the others.
I hate voodoo! Can anybody please exorcise these demons for me?
h4. 1. Has anybody encountered any ODP.NET misbehaviour when dealing with SDO_GEOMETRY data which might give me somewhere to look?
h4. 2. Can anybody tell me how ODP.NET data access machinery "interrogates" the Oracle data dictionary to determine what / how it is supposed to transact with a particular database object?
I am happy to try to provide details about tables, views, etc. However I suspect that this scenario is going to be exceptionally difficult to reproduce elsewhere for testing. Amongst other things, I won't be able to provide the dotnet application code, for example.
All suggestions, insights and advice very gratefully received!
OK - I have something further to add to the mix...
It seems that ... after doing nothing at all for a while+ (e.g. several hours) ... all of a sudden, my dotnet application INSERT now works! SDO_GEOMETRY objects are making their way into the database from the dotnet application.
Somewhere I'm thinking: "Well done, Dave. Have the rest of the day off!"
But somewhere else I'm thinking: "So ... what are you going to tell them when this happens in production?"
Basically - this is still voodoo as far as I'm concerned, and I don't believe in voodoo.
Is there such thing as a dotnet "cache" or "pool" which might be periodically refreshed? Something which might become out-of-sync with the database if I make DDL changes to tables and views, and which might thereby upset transactions?
This is still an open question as far as I'm concerned. Albeit slightly less urgent than it was 10 hours ago...
Enlightenment and advice will still be gratefully received.
ODP.net does have a client result cache, yes. I guess it's possible something happened there.
One of the issues with using SDO in ODP.net (at least the last time I did it, which was some time ago) is that SDO isn't directly implemented. It's done using user defined types. That, combined with the black box nature of this and the question over just what your triggers are doing makes it pretty hard to offer much advice without being able to see the code.
Luckily it's behaving now. :)
Yes, some years back I had a similiar issue.
In my scenario I would do a select on the table fetching attributes and the SDO_GEOMETRY. If that select did NOT produce a record that had an existing SDO_GEOMETRY(happened to be null for that record) and if I then did an UPDATE to that record, or an INSERT of a new, the SDO_GEOMETRY was not there. No error, just not there. If I got a "good" hit all went well.
The fix was to turn "Pooling=false" on the connection. All my troubles went away.
Sorry but I cannot recall the version of ODP.NET I was using but it was some years ago. To be frank I have NOT gone back to that application and changed it(It ain't broke now!!!)
Thanks Tridus and djonio. Always nice to receive a quick reply!
Dennis - I'm going to include your "Pooling=false" suggestion on a post-it note affixed to the whiteboard at the dev desk (standard documentation procedure, isn't it? :) ) so that we'll have something different to test should this ugly scenario ever arise again. I'll probably include it in discussions with the dotnet application developers at some stage, too...
I'll leave this question open for a bit longer - any other suggestions or war stories welcome.