Results 1 to 9 of 9

Thread: Oracle Spatial

  1. #1
    Join Date
    Apr 2011
    Posts
    20

    Unanswered: Oracle Spatial

    Hy!

    I'm new to ORACLE SPATIAL. This is the query that check if a geometry is contaned by another, if so , some attribute =1; But my trigger crasher.
    This is the query:

    Code:
    SELECT sdo_geom.relate(site.geom, 'CONTAINS', isp.GEOM, 0.005)
    INTO   param
    FROM   BLT_AC2000_TEST.wa_site_polygon AS site, BLT_AC2000_TEST.intrsect_site_pipe AS isp
    WHERE  site.fid = isp.FID_PRIM
           AND isp.FID_PRIM = 1126
           AND isp.FID_SEC = 1243;
    
     if (param='CONTAINS') then 
          :NEW.ATTRIBUTE_1:=1;
    end if;
    Thanks!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Specifying an error you got might help other people help you.

  3. #3
    Join Date
    Apr 2011
    Posts
    20
    I dont have an error , simply my Autocad Map3D crashes.

  4. #4
    Join Date
    Apr 2011
    Posts
    20
    Hy. Is me again. I changed my trigger because I realised I need an AFTER trigger:
    Code:
    create or replace
    TRIGGER BLT_AC2000_TEST.TRIGGER1 
    AFTER INSERT OR UPDATE ON BLT_AC2000_TEST.INTRSECT_SITE_PIPE 
    declare
    numar_aparitii number(10);
    nr number(10,0);
    BEGIN
    SELECT count(sdo_geom.relate(site.geom,'CONTAINS', isp.GEOM,0.005)) INTO nr 
      from BLT_AC2000_TEST.wa_site_polygon site, 
      BLT_AC2000_TEST.intrsect_site_pipe isp 
      WHERE site.fid = isp.FID_PRIM  and isp.FID_PRIM=946 and isp.FID_SEC=971; 
       if (nr=1) then 
         -- raise_application_error(-20236, 'Eroare'||to_char(nr) );
         UPDATE BLT_AC2000_TEST.intrsect_site_pipe isp SET isp.test=nr where isp.FID_PRIM=946 AND isp.FID_SEC=971;
         else 
         UPDATE BLT_AC2000_TEST.intrsect_site_pipe isp SET isp.test=0 where isp.FID_PRIM=946 AND isp.FID_SEC=971;
      end if;
      NULL;
    END;
    This is want i'm trying to do. check is a line is contained by a polygon if so, set an atribute to a certain value. This a simplified trigger, the final one will be more complicated.
    I get these errors:
    0RA 06512
    0RA 04088 - Error during execution of trigger. Can you help me?
    Tranks.

  5. #5
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Hi,

    there also must be a ORA-04091 error in the stack.

    What you are doing, is:

    1.) defining a trigger that should be fired, when a row in the table is updated.
    2.) within the trigger, you update the table on which the trigger is defined

    This obviousely would lead to a infinite recursion, so Oracle prevents it

    The Error description is:

    ORA-04091: table <string>.<string> is mutating, trigger/function may not see it

    Cause :
    A trigger (or a user defined plsql function that is referenced in
    this statement) attempted to look at (or modify) a table that was
    in the middle of being modified by the statement which fired it.

    Action:
    Rewrite the trigger (or function) so it does not read that table.
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  6. #6
    Join Date
    Apr 2011
    Posts
    20
    I understood what you said. This is what I want to do:
    Check if a pipe (geometry line) is contained by a manhole (geometry polygon) and I have a table INTERSECT_SITE_PIPE where a have the intersection geometry. If line are contained a atribut is set to 1 , if not is set to 2 in order to label the pipes that are not contained.
    How do you sugest to do this? With triggers I mean.
    Thanks very much.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Show us the original before trigger, which is the way to do it, and we will try to fix it
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Apr 2011
    Posts
    20
    Thanks but I solved my problem. Actually i remade my trigger. Would you happen to have some materials about oracle spatial?

  9. #9
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    I found this on very useful (though pretty expensive):

    Pro Oracle Spatial for Oracle Database 11g (Expert's Voice in Oracle)
    by Ravi Kothuri, Albert Godfrind and Euro Beinat
    Apress, Oct 2007
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •