Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

I need to write a trigger before update or delete.

SN-RSep 8 2021

when ever I need to edit or delete a data from table A, I need to insert the whole data's in table B which contains in table A with the user details like who modified/deleted and when modified/deleted . How can I do this?
someone please help me
thanks in advance

Comments

Siva Ravada-Oracle
Your query looks fine to me.

siva
429668
I've seen similar errors while using Oracle 9.2.0.5 and ArcSDE8.3. It is usually down to polygons not being entirely closed. In SDE the first node and last node need to be exactly the same whereas Oracle, rightly so, applies a tolerance.

You above query will not highlight this unless you change the tolerance of the validate function to something like 0.000000001 metres.

I have a script which will fix these if you're intestered.
502625
David,

Thanks for your answer. After finding errors within a window, I ran validate_layer_with_context and found many instances of ora-13356 (redundant vertices) ora-13349-51 (self intersecting polygons). I believe these can be fixed by applying sdo_union & sdo_util.remove_duplicate_vertices, following this thread:

924033

I also need to check for unclosed polygons and possibly fix them. If it's still available, could you post or send me your correction script.

With thanks, Alex
91061
If performance was important, I'd try to avoid running the validate twice. This is an example, cut your query in as the inner select (how did you keep the formatting of your query?):

select a.primary_key, a.vgeom
from (your_select_statement_here) a
where a.vgeom <> 'TRUE';
429668
Alex - If you still want the script please let me know and i'll send it on.
502625
Yes please David. Either post it to this thread or send it to my email address: alex@moreati.org.uk

Thanks, Alex
571664
Could you send me the script as well David, if you still have it after a year! Thanks

p.baynham@verizon.net
Simon Greener
For 8 years I was GIS Manager at a company in Tasmania running ArcSDE on top of Oracle Spatial.

The result of managing this environment led me to create a ShapeCheck routine that I put in the Oracle Job queue that could track transactional edits as they were made each day to ensure data quality was maintained (ie NO errors from VALIDATE_GEOMETRY or VALIDATE_GEOMETRY_WITH_CONTEXT). This ShapeCheck routine was passed a WHERE clause that could be something like "CREATION_DATE LIKE SYSDATE" (ie check all edits for today). This was run each night after 6pm when all editors were no longer at work.

Now that I work for myself I have packaged up all my experience in managing this environment in a bunch of packages that can be downloaded from:

http://www.spatialdbadvisor.com/source_code (click on the "Oracle Spatial Packages and Types" link on the left side of the page).

In particular, have a look inside the TOOLS package for a procedure called GeometryCheck whose signature is:
   Procedure GeometryCheck( p_schema        IN VarChar2, 
                            p_tableName     IN VarChar2,
                            p_ColumnName    IN VarChar2,
                            p_whereClause   IN VarChar2);
Now the original ShapeCheck used to send emails to the person who made the edit with a CC to person who worked for me who was charged with quality assurance. The new version, GeometryCheck, writes its activity (including automated correction) to special tables that can be reported on in SQL Developer.

Wrt SQL Developer I am creating some reports for SQL Developer that will also be available from my website.

regards
S
1 - 8

Post Details

Added on Sep 8 2021
15 comments
4,886 views