1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Instead of trigger returns infinite_loop

Discussion in 'SQL PL/SQL' started by many_yammy, May 17, 2014.

  1. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi all,

    I have a problem in PL/SQL.. I wrote an instead of trigger but the problem is in a loop I have more than the expected recrusive processes.

    for example I have the enteries of 9 rows in a view table("CAFM_FACADE_ELEMENT_VIEW") which will be aggregated based on some indicators and merged into 4 groups. So I expected to have four rows in destination table "SURFACE_AREA" but I have more than 50 rows. How can I manage the problem.

    I appreciate your help in advance.

    Best regards,

    Many

    here is the code:

    "

    CREATE OR REPLACE TRIGGER AGGREGATE_TO_SURFACE_AREA_TEST

    INSTEAD OF INSERT ON CAFM_FACADE_ELEMENT_VIEW

    FOR EACH ROW

    DECLARE

    BUILDING_ID NUMBER;

    FACADE_ID NUMBER;

    ELEMENT_TYPE NUMBER;

    MATERIAL NUMBER;

    HEIGHT_CLASS NUMBER;

    SURFACE_AREA NUMBER(10,3);



    CURSOR AGG_

    is

    SELECT AO, AA, AB, A, B, SUM(SUMA)

    INTO BUILDING_ID, FACADE_ID, ELEMENT_TYPE, MATERIAL, HEIGHT_CLASS, SURFACE_AREA

    FROM (

    SELECT f.FK_CAFM_BUILDING AO,

    f.ID AS AA,

    fe.FK_FACADE_ELEMENT_TYPE AS AB,

    fe.FK_OUTSIDE_MATERIAL AS A,

    fe.FK_OUTSIDE_HEIGHT_CLASS AS B,

    SUM(fe.OUTSIDE_SURFACE_AREA) AS SUMA

    FROM CAFM_FACADE_ELEMENT_VIEW fe, CAFM_FACADE f

    WHERE fe.FK_CAFM_FACADE = f.ID

    GROUP BY f.FK_CAFM_BUILDING,

    f.ID,

    fe.FK_FACADE_ELEMENT_TYPE,

    fe.FK_OUTSIDE_MATERIAL,

    fe.FK_OUTSIDE_HEIGHT_CLASS

    UNION ALL



    SELECT f.FK_CAFM_BUILDING AS AO,

    f.ID AS AA,

    fe.FK_FACADE_ELEMENT_TYPE AS AB,

    fe.FK_INSIDE_MATERIAL AS A,

    fe.FK_INSIDE_HEIGHT_CLASS AS B,

    SUM(fe.INSIDE_SURFACE_AREA) AS SUMA

    FROM CAFM_FACADE_ELEMENT_VIEW fe,

    CAFM_FACADE f

    WHERE fe.FK_CAFM_FACADE = f.ID

    AND fe.FK_INSIDE_HEIGHT_CLASS != 0

    GROUP BY f.FK_CAFM_BUILDING,

    f.ID,

    fe.FK_FACADE_ELEMENT_TYPE,

    fe.FK_INSIDE_MATERIAL,

    fe.FK_INSIDE_HEIGHT_CLASS

    )S

    GROUP BY AO, AA, AB, A, B;



    BEGIN

    OPEN AGG_;

    LOOP

    FETCH AGG_ into BUILDING_ID, FACADE_ID, ELEMENT_TYPE, MATERIAL, HEIGHT_CLASS, SURFACE_AREA;

    EXIT WHEN AGG_%notfound;



    end loop;

    close AGG_;

    INSERT INTO SURFACE_AREA

    ( FK_CAFM_BUILDING, FK_CAFM_FACADE, FK_FACADE_ELEMENT_TYPE, FK_MATERIAL, FK_HEIGHTCLASS, SURFACE_AREA)

    VALUES (BUILDING_ID, FACADE_ID, ELEMENT_TYPE, MATERIAL, HEIGHT_CLASS, SURFACE_AREA);



    END;

    "
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Check your syntax,

    Some errors are there. Is it well compiled trigger?
     
  3. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi,

    thank you for your reply.
    I removed "for each row" and also the "insert into" line in cursor. But which syntax error you mean?

    could you please point them out?
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    I Think Here INTO is not required...

    Is your trigger compiled with out warning?
     
    Rajap likes this.
  5. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    You are right. I already removed it.

    Yes, It runs withought error.
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    can you show me the new code which you modified.

    In Fetch you need INTO statement to assign cursor values to variables.
     
  7. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Although I know the code is written in stupid way and for each insert in view I am deleting all entries in surface_area table and again insert the new value but it seems at least to give me the result but not optimized at all:


    here is the code:


    create or replace TRIGGER AGG_TO_SURFACE_AREA_INSERT
    INSTEAD OF INSERT OR DELETE or UPDATE ON CAFM_FACADE_ELEMENT_VIEW



    DECLARE

    BUILDING_ID NUMBER;
    FACADE_ID NUMBER;
    ELEMENT_TYPE NUMBER;
    MATERIAL NUMBER;
    HEIGHT_CLASS NUMBER;
    SURFACE_AREA NUMBER(10,3);

    CURSOR AGG_
    is
    SELECT AO, AA, AB, A, B, SUM(SUMA)
    FROM (
    SELECT f.FK_CAFM_BUILDING AO,
    f.ID AS AA,
    fe.FK_FACADE_ELEMENT_TYPE AS AB,
    fe.FK_OUTSIDE_MATERIAL AS A,
    fe.FK_OUTSIDE_HEIGHT_CLASS AS B,
    SUM(fe.OUTSIDE_SURFACE_AREA) AS SUMA
    FROM CAFM_FACADE_ELEMENT fe, CAFM_FACADE f
    WHERE fe.FK_CAFM_FACADE = f.ID
    GROUP BY f.FK_CAFM_BUILDING,
    f.ID,
    fe.FK_FACADE_ELEMENT_TYPE,
    fe.FK_OUTSIDE_MATERIAL,
    fe.FK_OUTSIDE_HEIGHT_CLASS

    UNION ALL

    SELECT f.FK_CAFM_BUILDING AS AO,
    f.ID AS AA,
    fe.FK_FACADE_ELEMENT_TYPE AS AB,
    fe.FK_INSIDE_MATERIAL AS A,
    fe.FK_INSIDE_HEIGHT_CLASS AS B,
    SUM(fe.INSIDE_SURFACE_AREA) AS SUMA
    FROM CAFM_FACADE_ELEMENT fe,
    CAFM_FACADE f
    WHERE fe.FK_CAFM_FACADE = f.ID
    AND fe.FK_INSIDE_HEIGHT_CLASS != 0
    GROUP BY f.FK_CAFM_BUILDING,
    f.ID,
    fe.FK_FACADE_ELEMENT_TYPE,
    fe.FK_INSIDE_MATERIAL,
    fe.FK_INSIDE_HEIGHT_CLASS
    )S
    GROUP BY AO, AA, AB, A, B;

    BEGIN
    delete surface_area;

    OPEN AGG_;
    LOOP
    FETCH AGG_ into BUILDING_ID, FACADE_ID, ELEMENT_TYPE, MATERIAL, HEIGHT_CLASS, SURFACE_AREA;
    exit when AGG_%notfound;



    INSERT INTO SURFACE_AREA
    (ID, FK_CAFM_BUILDING, FK_CAFM_FACADE, FK_FACADE_ELEMENT_TYPE, FK_MATERIAL, FK_HEIGHTCLASS, SURFACE_AREA)
    VALUES (SURFACE_AREA_SEQ.NEXTVAL , BUILDING_ID, FACADE_ID, ELEMENT_TYPE, MATERIAL, HEIGHT_CLASS, SURFACE_AREA);


    end loop;
    close AGG_;


    END;
     
  8. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Explicit Cursor, Lets Look at the example below

    Example 1:

    1> DECLARE

    2> emp_rec emp_tbl%rowtype;

    3> CURSOR emp_cur IS

    4> SELECT *

    5> FROM

    6> WHERE salary > 10;

    7> BEGIN

    8> OPEN emp_cur;

    9> FETCH emp_cur INTO emp_rec;

    10> dbms_output.put_line (emp_rec.first_name || ' ' || emp_rec.last_name);

    11> CLOSE emp_cur;

    12> END;
     
  9. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    I think the issue comes from the "UNION ALL" which does not remove duplicate rows while "UNION" does.

    There is only 1 condition in the 1st part of the Cursor ("WHERE fe.FK_CAFM_FACADE = f.ID") while in the 2nd part there's 2 (" WHERE fe.FK_CAFM_FACADE = f.ID
    AND fe.FK_INSIDE_HEIGHT_CLASS != 0") .

    => The 1st part will also bring the rows (SUM) for the " fe.FK_INSIDE_HEIGHT_CLASS != 0" condition.

    Try using "UNION" or maybe add the condition " fe.FK_INSIDE_HEIGHT_CLASS = 0" in the 1st part.

    Regards,
    Rajen.
    P.S: "UNION" is not as optimized as "UNION ALL" as it does a implicit "DISTINCT" to remove the duplicates.
     
  10. sfardin

    sfardin Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Hi,

    Looks like the issue is with query.Check output of the query by running it in Toad or sql developer. Maybe the join between two tables may not be correct or have missed one or more join condition.
     
  11. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    what i cannot understand is the anonymous block works perfectly but when i define it as a trigger i have problem.

    Although I think i need the loop otherwise all the entries sum up as one. It means when I expect 4 rows withough loop i have only one.
     
  12. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    the output is completely correct and what I expect.
    since I delete all entries ("delete surface_area") the results seem OK. But this is really slow and stupid.

    and the I have the problem for the ID column. Because I need to restart the sequence after deleting all previous data and inserting new ones.
     
  13. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi "many_yammy",

    Can you please send us:

    1) The structure of all the underlying tables and view (or the SQL to create it)
    2) The data which is present in the tables
    3) Data which is being "inserted" in the view

    Thanks & Regards,
    Rajen.