This discussion is archived
4 Replies Latest reply: Feb 1, 2013 3:41 PM by Vite DBA RSS

User ID

Hilton Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points