The problem
Problem is that in classical client-server apps, you probably deal with real Oracle users, who logged in to database with their accounts. In Apex, the situation is rather different and in that situation only one Oracle user (attached Oracle user which is assigned to Apex workspace where application is developed) is contacting the database.Here is one live example of topical problem.
In a case that you have some simple monitoring part implemented in a classic way through 4 fields in tables, declared as:
CREATE_USER VARCHAR2(30 BYTE), CREATE_DATE DATE, EDIT_USER VARCHAR2(30 BYTE), EDIT_DATE DATEwhose values are filled through two triggers, BEFORE INSERT:
CREATE OR REPLACE TRIGGER "SOME_TABLE_TGBI" BEFORE INSERT ON SOME_TABLE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW begin :new.create_date:= sysdate; :new.create_user:= user; end; /and BEFORE UPDATE:
CREATE OR REPLACE TRIGGER "SOME_TABLE_TGBU" BEFORE INSERT ON SOME_TABLE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW begin :new.edit_date:= sysdate; :new.edit_user:= user; end; /then you'll get correct values from all client-server applications and wrong values for all Apex based applications. In those cases you'll always get just one user explained previously. Let me show exactly what I'm pointing to. Here is an example of one table edited by real Oracle users (4 letters in name) and many Apex application users represented by APEX_USER.
ID CREATE_DATE CREATE_USER EDIT_DATE EDIT_USER ----- ------------------- ------------- ------------------- ---------------- 26384 11.07.2010 10:22:15 NAMR 19.04.2011 09:01:16 UACA 26386 19.04.2009 11:17:15 UACA 19.04.2011 19:10:15 APEX_USER 26385 12.01.2011 08:11:15 RAPO 19.04.2011 03:33:16 APEX_USER 26387 19.02.2011 12:24:15 RAPO 19.04.2011 19:04:13 APEX_USER 26388 22.02.2011 17:23:15 APEX_USER 19.04.2011 22:22:05 RAPO 26390 30.03.2011 13:19:15 IGAP 19.04.2011 21:47:36 APEX_USER 26391 06.01.2011 13:18:15 APEX_USER 19.04.2011 19:56:27 APEX_USER 26389 02.01.2011 11:08:15 APEX_USER 19.04.2011 20:23:33 APEX_USER 26392 02.04.2011 11:01:15 APEX_USER 19.04.2011 10:09:56 IGAP 26394 18.04.2011 09:10:15 NAMR 19.04.2011 16:11:01 APEX_USERFor some application t is vital to follow which user has done some actions, so it is more then obvious to implement a solution for that situations.
The solution
But regardless that only one real Oracle user is connected to database through Apex application, Apex leave plenty of information about "application" based user ... authenticated user in Apex application. This value can always be retrieved Apex "Built-in Substitution String" v('APP_USER') value in any PL/SQL code. More on Apex "Built-in Substitution Strings" can be found here.So, when this is true, let me show you how to write trigger which will server both environments in the same time.
For that create a procedure in one separate schema (in mine case this is mine famous TOOLS schema). I'll call that procedure TGBIU_APEX:
CREATE OR REPLACE PROCEDURE "TGBIU_APEX"
/******************************************************************************
NAME : TGBIU_APEX.prc
PURPOSE : Comon part for insert/update trigger in Apex and NON Apex applications
Date : 15.02.2008.
Author : Damir Vadas
Remarks : Create procedure in separate schema and grant execute to public.
Recommendation is to not create PUBLIC SYNONYM.
Changes (DD.MM.YYYY, Name, CR/TR#):
******************************************************************************/
(o_date out date,
o_user out VARCHAR2
)
AUTHID CURRENT_USER
AS
BEGIN
o_date:=sysdate;
o_user:=nvl(v('APP_USER'),USER);
END TGBIU_APEX;
/
As you see, procedure has two "out" parameters where interesting one is "o_user" value which would firstly be assigned from Apex application and if this value is null then real Oracle user value would be assigned. So in that case problem is solved.Do not forget to place AUTHID CURRENT_USER which will ensure proper usage all around the database. Grant execute to PUBLIC but do not create PUBLIC SYNONYM.
Usage for previously two triggers would be like:
CREATE OR REPLACE TRIGGER "SOME_TABLE_TGBI" BEFORE INSERT ON SOME_TABLE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW begin TOOLS.TGBIU_APEX (:new.create_date,:new.create_user); end; /and
CREATE OR REPLACE TRIGGER "SOME_TABLE_TGBU" BEFORE INSERT ON SOME_TABLE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW begin TOOLS.TGBIU_APEX (:new.edit_date,:new.edit_user); end; /
The End
Another usage of approach like this might be in some other actions where you need to monitor other actions based on authenticated users.Beside you achieve the main purpose, clearness and centralizing code is another benefit, which would certainly help in future maintains/upgrades to some other environment.
Cheers!
