|
From: Mark N. <mar...@ho...> - 2006-02-22 20:54:07
|
Hi All, I've been looking at the Oracle edit issues that various people have been helping out with. The common thread among rows that I can't update at this point is a non-null SDO_GEOMETRY field (we use Oracle Spatial heavily.) I've found that for rows that I can't edit, I can make them work by a) removing the geometry column b) setting the geometry column to null or c) removing the geometry from the where clause using the Squirrel GUI, as someone suggested trying earlier while we were still puzzling out the date issues. Obviously, only option c) is realistic in a real world scenario, but I'm just exploring. I had originally suspected the geometry fields as a possible issue, as someone earlier in this thread had mentioned CLOBs and BLOBs as potential problem areas for updating. I had been able to successfully update rows with non-null geometries, however. Today, I have been able to verify that the current snapshot fixes the date issue, but now trips on (only some) rows containing non-null geometries. I turned log4j up to debug, which dumps the sql being used to perform the updates to the log. Looking at these queries, there seems to be a wide variation in the number of fields used in the where clause of these updates, and the success of the updates has to do with (and likely did even before the date fix) whether the geometry field is included. Note that I am not specifically excluding any fields from the where clause with the GUI at this point. As a couple of examples, I update a row of table AAA in my own schema. It updates successfully, even with a non-null geometry, because the where clause only contains one of the 24 fields. When I update a row in another copy of the schema, it also succeeds; this time, however, the where clause contains 23 of the 24 fields, neatly excluding the geometry field that would have caused trouble. Again, I am excluding no fields through the GUI. Table BBB: I update in my own schema and succeed. There are three geometries, two of them non-null, and the where clause contains only three of 40+ fields. When I try to update the same table in another schema, it fails. Most, but not all, of the fields are included in the where clause in this case; one of the geometries is included, and trips me up. If I exclude that field through the GUI, I can update successfully. At this point, editing works great for me. The date fix was the big stumbling block; I can get around the current issue by excluding the problem fields in the GUI. I am curious, though, as to how the where clause is constructed; it was mentioned in this thread that all of the columns in the table are used to form a huge where clause, and looking at the debug output there seems to be quite a bit of variation in the fields selected instead of all columns all the time. I don't imagine that Oracle Spatial support is a high priority at this point; if anybody is working on it and wants some help with testing, you can drop me an email. Regards, Mark |
