Occasionally, while writing components or processes in PeopleSoft, I have wished I could obtain the OPRID directly from the database without using META-SQL, META-Variables, or any other PeopleSoft magic. While those mechanisms work very well within the PeopleSoft framework, they are not accessible from PL/SQL procedures, triggers, or views. I was discussing this with my DBA a few months ago and he kindly pointed me to the CLIENT_INFO field of the v$session view. As Chris Heller explained in his post Associating database connections with PeopleSoft users dated Sunday, August 13, 2006, EnableDBMonitoring needs to be turned on before the CLIENT_INFO field will contain the OPRID. Chris Heller's post also explains where to find CLIENT_INFO on non-Oracle database platforms.
Following the good advice of my DBA, I wrote the following SQL fragment to parse the OPRID from the CLIENT_INFO field.
SUBSTR(sys_context('USERENV', 'CLIENT_INFO'), 1, INSTR(sys_context('USERENV', 'CLIENT_INFO'), ',', 1, 1) - 1)Notice that I use the sys_context function rather than querying the v$session view directly. The v$session view is a great view for system administrators, but it lists all sessions, not just the logged in user's session.
Because this fragment is rather verbose, I wrote a PL/SQL function to encapsulate the logic of the fragment. However, when used in a WHERE clause, this fragment executes a lot faster than the function.
Here is an example SQL statement that will return the PeopleSoft logged in user. I would like to tell you that you can run this statement from any SQL tool, but it will only work when run from PeopleSoft. This is because each database client is responsible for setting the CLIENT_INFO and each client tool sets this value differently (if the client tool even sets CLIENT_INFO).
SELECT SUBSTR(sys_context('USERENV', 'CLIENT_INFO'), 1, INSTR(sys_context('USERENV', 'CLIENT_INFO'), ',', 1, 1) - 1)
FROM DUALHere is a PL/SQL block that sets the variable
lv_oprid to the PeopleSoft OPRID.DECLARE
lv_oprid VARCHAR2(30);
-- ... more variables declared here
BEGIN
lv_oprid := SUBSTR(sys_context('USERENV', 'CLIENT_INFO'), 1, INSTR(sys_context('USERENV', 'CLIENT_INFO'), ',', 1, 1) - 1);
-- ... do something with OPRID
END;
In a future post I will show how to use this technique to log information about users for debugging and auditing. I also hope to show how to use this technique to improve the user experience with custom pagelets.
If you are trying to restrict the visible rows in a search record, then I suggest you take a look at Larry Grey's post Little known Row Level Security hook dated Thursday, May 18, 2006.
2 comments:
I am building an iScript for manager-self service and I wanted to present all the positions that reported to the manager in a collapsible tree. However, the problem I was having was building the recursive sql to start on the user. I wrote the sql using a CTE, but it would have never worked without this post!
This is my cte, the ZZ_IDM table is a denormalized table that contains job info by userid:
WITH Reports (Position_Nbr, Descr, Reports_To, Depth) AS (
SELECT A.Position_Nbr
, A.Descr
, A.Reports_To
, 1
FROM Ps_Position_Data A
, PS_ZZ_IDM I
WHERE I.POSITION_NBR = A.POSITION_NBR
AND A.Effdt = (
SELECT MAX(E.Effdt)
FROM Ps_Position_Data E
WHERE A.Position_Nbr = E.Position_Nbr
AND E.Effdt <= To_Date(Sysdate))
AND I.OPRID = (
SELECT SUBSTR(sys_context('USERENV'
, 'CLIENT_INFO')
, 1
, INSTR(sys_context('USERENV'
, 'CLIENT_INFO')
, ','
, 1
, 1) - 1)
FROM DUAL)
UNION ALL
SELECT Z.Position_Nbr
, Z.Descr
, Z.Reports_To
, reports.depth + 1
FROM Ps_Position_Data Z
, REPORTS
WHERE Z.Effdt = (
SELECT MAX(Ez.Effdt)
FROM Ps_Position_Data Ez
WHERE Z.Position_Nbr = Ez.Position_Nbr
AND Ez.Effdt <= To_Date(Sysdate))
AND Z.Reports_To = Reports.Position_Nbr
AND Z.Position_Nbr <> Reports.Position_Nbr
AND Z.Eff_Status = 'A'
AND Z.Posn_Status = 'A')
SELECT *
FROM REPORTS
Can you think of any other way to accomplish this in an iScript?
Thanks!
@Kevin, CTE or "Connect By" are the two ways that I would accomplish this. You could use recursive PeopleCode, but the SQL is much more efficient. Well done!
Post a Comment