AJAX and PeopleSoft

Ever since I read Rich Manalang's post Adding Live Search to PeopleSoft Enterprise, I have been trying to figure out how to apply AJAX to PeopleSoft components. I have been able to benefit from AJAX in PeopleSoft by sending parameters to IScripts and displaying the results on a page. However, I have not been able to figure out how to update a component's data buffer using AJAX.

I think most of us would agree that the full page refresh required for a FieldChange event is too expensive. Yes, we do have deferred processing available. However, deferred processing can be just as user un-friendly as the page "flicker" of a post-back. AJAX frameworks provide the infrastructure required to post data to the server without requiring a full page refresh. Furthermore, AJAX frameworks can update a portion of a page with the results of a server operation. But how can we leverage the power of AJAX to update the component data model in PeopleSoft?

It seems the answer to this question is in the post. To update the component's data model, you would need to post the form values. Since the web server will return the full page content, you will next need to parse the returned page and update your page accordingly. Is it worth it? Anyone have any better ideas?

PeopleSoft on Oracle WHOAMI

Determine the PeopleSoft OPRID from Oracle

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 DUAL

Here 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.