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!

nothing happens after execution of the procedure!

Discussion in 'SQL PL/SQL' started by many_yammy, Sep 20, 2014.

  1. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Dear All,

    I created two procedures and the aim is to delete a set of information in couple of tables.
    I have a table named "CAFM_FACADE_ELEMENT" which referenced two other tables "CITYOBJECT" and "IMPLICIT_GEOMETRY". I need to delete a set of rows in all of them but first should delete from "CAFM_FACADE_ELEMENT" and then "CITYOBJECT" and "IMPLICIT_GEOMETRY".
    I created a Global temporary table and in the next procedure I inserted the information into this temporary table and started deleting. Here is my codes

    Code (SQL):
    CREATE global TEMPORARY TABLE facade_element(elem_id NUMBER, imp_id  NUMBER, param_id NUMBER, cityobj_id NUMBER);
    Code (SQL):
    CREATE OR REPLACE PROCEDURE del_fac_elem_1 (P_buil IN VARCHAR2)
    IS


    BEGIN

        INSERT INTO facade_element
        SELECT FE.ID, NVL(FE.FK_IMPLICIT_REPRESENTATION,0), NVL(FE.FK_PARAMETRIC_REPRESENTATION,0), FE.FK_CITYOBJECT_ID
        FROM CAFM_FACADE_ELEMENT FE
        LEFT OUTER JOIN CAFM_FACADE F ON
        FE.FK_CAFM_FACADE=F.ID
        LEFT OUTER JOIN CAFM_BUILDING B ON
        F.FK_CAFM_BUILDING=B.ID
        WHERE B.BUILDING_ID=P_buil
        GROUP BY FE.ID, FE.FK_IMPLICIT_REPRESENTATION, FE.FK_PARAMETRIC_REPRESENTATION, FE.FK_CITYOBJECT_ID;
     
        DELETE FROM (
        SELECT FE.*
        FROM CAFM_FACADE_ELEMENT FE, FACADE_ELEMENT f
        WHERE FE.ID=f.elem_id);


        DELETE FROM CITYOBJECT  
        WHERE (ID) IN (
              SELECT cityobj_id
              FROM facade_element);
             
        DELETE FROM IMPLICIT_GEOMETRY
        WHERE (ID) IN (
              SELECT imp_id
              FROM facade_element);
       
     
        COMMIT;
       
       
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'The building does not have the corresponding information');
        ROLLBACK;
    END;
    The procedures can be compiled but at the end when I execute it nothing happens. Could you please help me about the error/s I have in my code?

    Thank you in advance.

    Many
     
  2. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    I think I've found the problem and this is the delete statement:

    Code (SQL):
       DELETE FROM (
        SELECT FE.*
        FROM CAFM_FACADE_ELEMENT FE, FACADE_ELEMENT f
        WHERE FE.ID=f.elem_id);
    Best regards,
    Many