4 Replies Latest reply: Feb 1, 2013 5:41 PM by Vite DBA RSS

    User ID

    Hilton
      Hi there,
      Running into a bit of a problem with getting a user_id with calling a procedure in a package.

      Business Need
      To be able to track who created and updated records in a table

      Solution
      Using the whois concept of oracle EBS
      created_by, creation_date, last_updated_by, last_update_date

      Problem
      The API is using a function throughout the application that has been built in PL/SQL.
      This was a standard I used on the forms side and was using my own user_table.
      Now with using APEX there is already a user table so I want to replace this.
      I thought it would be as easy as replacing the select.
      Somehow though the value is not being returned.
      I keep getting the following error from the application when running it through the browser interface:
      ORA-01400: cannot insert NULL into ("XCM"."KDM_REQ_DEPARTMENT"."CREATED_BY")
      The weird thing is that testing this locally I did not get any issues.

      Using the following function to get the user_id:
      function user_id
      return number
      is
      action_name constant varchar2 (30) := 'USER_ID';
      l_workspace_id number;
      l_user_id number;
      begin
      --dbms_output.put_line (nvl (v ('APP_USER'), user)) ;
      --l_user_id := htmldb_util.get_user_id (nvl (v ('APP_USER'), user)) ;
      select workspace_id into l_workspace_id from
      apex_workspace_apex_users
      where user_name = nvl (v ('APP_USER'), user)
      ;
      wwv_flow_api.set_security_group_id(l_workspace_id);
      select
      user_id
      into
      l_user_id
      from
      wwv_flow_users
      where
      user_name = nvl (v ('APP_USER'), user) ;
      if (l_user_id is null) then
      return - 1;
      else
      return l_user_id;
      end if;
      exception
      when others then
      return - 1;
      end user_id;

      --------
      Apex 4.2
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE 11.2.0.1.0 Production
      TNS for Linux: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      Linux version 2.6.32-279.19.1.el6.i686
      ---------

      Edited by: Hilton on Feb 1, 2013 1:09 PM
        • 1. Re: User ID
          Hilton
          BTW:
          I initially used this to get the user_id and it is also no longer working:

          l_user_id := htmldb_util.get_user_id (nvl (v ('APP_USER'), user)) ;
          • 2. Re: User ID
            Vite DBA
            Hi Hilton,

            just use database triggers, set and forget.
            CREATE OR REPLACE TRIGGER TABLENAME_AUD
             BEFORE INSERT OR UPDATE
             ON TABLENAME
             FOR EACH ROW
            BEGIN
              IF inserting THEN
                :new.created_by := nvl(apex_custom_auth.get_user,user);
                :new.created_date := sysdate;
              END IF;
              IF updating THEN
                :new.last_updated_by := nvl(apex_custom_auth.get_user,user);
                :new.last_updated_date := sysdate;
              END IF;
            END;
            Regards
            Andre
            • 3. Re: User ID
              Hilton
              Thanks Andre. You're solution is pretty sweet but would mean I have to make changes to each table.
              I think it is working though as I had not cancelled the previous button function that the form created.
              Once I removed it the procedure seems to be running although not doing anything
              Trying now to figure out the debugging side of things.
              • 4. Re: User ID
                Vite DBA
                Hi Hilton,

                Its true that you have to put it on every table that you want to audit, but once its there, its there for what ever API accesses the table. The code is very basic and if you keep your auditing column names consistent, then the only code changes required when you create the triggers for different tables is the trigger name, usually tablename_aud, and the actual table name. On top of that, its the method any experienced DBA will tell you is the way to go.

                Doing it at the application level means that you have to call the function for every page that accesses a table and every other API that accesses the data, such as sqlplus, sql developer, toad, sql loader etc.

                Also, note that a function to get the current user, either the Apex user if in an Apex session or the database user if outside an Apex session is simply.

                nvl(apex_custom_auth.get_user,user);

                Regards
                Andre