6 Replies Latest reply: Feb 6, 2014 5:47 AM by Ahmed-Mamdouh RSS

    Database trigger can't see global package variable after assign in my form

    Ahmed-Mamdouh

      Hi

      I faced problem with trigger and global package variable using forms builder

       

       

      first all I get IP address of local machine using WEBUTIL in PRE-FORM trigger and initialize my global package variable by "Client_Info_Pkg.Set_IP_Address(WEBUTIL_CLIENTINFO.GET_IP_ADDRESS);", then I tried to make DML operation within my form and in the same time I have database trigger also use the global package variable by "Client_Info_Pkg.Get_IP_Address;" to read it from package then insert it into audit table


      The issue that database trigger can't feel the initialization "Client_Info_Pkg.Set_IP_Address(WEBUTIL_CLIENTINFO.GET_IP_ADDRESS)" that I made in PRE-FORM trigger and I used to wonder if oracle create new session to run that trigger so it can't see my initialization or what ???!!!.

       

       

      the package

      CREATE OR REPLACE PACKAGE Client_Info_Pkg IS

         Global_IP_Address VARCHAR2(100);

       

         FUNCTION Get_IP_Address RETURN VARCHAR2;

        

         PROCEDURE Set_IP_Address(p_Machine_Name IN VARCHAR2);

      END Client_Info_Pkg;

      /

       

      CREATE OR REPLACE PACKAGE BODY Client_Info_Pkg IS

          FUNCTION Get_IP_Address RETURN VARCHAR2 IS

          BEGIN

              RETURN Global_IP_Address;

          END;

         

          PROCEDURE Set_IP_Address(p_IP_Address IN VARCHAR2) IS

          BEGIN

              Global_IP_Address := p_IP_Address;

          END;

      END Client_Info_Pk

       

      the trigger

      --------------

      CREATE OR REPLACE TRIGGER RUNERP.FO_DOC_TYPES_L_T

      BEFORE INSERT OR UPDATE OR DELETE ON FO_DOC_TYPES

      REFERENCING NEW AS NEW OLD AS OLD

      FOR EACH ROW

      BEGIN

          IF INSERTING THEN

              EXECUTE IMMEDIATE 'INSERT INTO FO_DOC_TYPES_LOG

              VALUES(''I'', :DOC_TYPE_ID, :DOC_TYPE_VALUE, :DOC_TYPE_NAME, :Vip_Address' USING :NEW.DOC_TYPE_ID, :NEW.DOC_TYPE_VALUE, :NEW.DOC_TYPE_NAME, Client_Info_Pkg.Get_IP_Address;

          END IF;

         

          IF UPDATING THEN

              EXECUTE IMMEDIATE 'INSERT INTO FO_DOC_TYPES_LOG

              VALUES(''U'', :DOC_TYPE_ID, :DOC_TYPE_VALUE, :DOC_TYPE_NAME, :Vip_Address)' USING :NEW.DOC_TYPE_ID, :NEW.DOC_TYPE_VALUE, :NEW.DOC_TYPE_NAME, Client_Info_Pkg.Get_IP_Address;

          END IF;

         

          IF DELETING THEN

              EXECUTE IMMEDIATE 'INSERT INTO FO_DOC_TYPES_LOG

              VALUES(''D'', :DOC_TYPE_ID, :DOC_TYPE_VALUE, :DOC_TYPE_NAME, :Vip_Address)' USING :NEW.DOC_TYPE_ID, :NEW.DOC_TYPE_VALUE, :NEW.DOC_TYPE_NAME, Client_Info_Pkg.Get_IP_Address;

          END IF;

      END;

      /

       

      Thanks in advanced