Dashboards, Applications & Business Intelligence

How to know which Discoverer sessions runs specific workbook, what application user ID and responsibility, what they are running?

Step 1:
======

/* Create funciton in Database base using APPS Schema */
Create or replace function disco_client_info
(workbook_name varchar2) return number is
BEGIN
--Set the client inforamtions
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(fnd_global.user_id||'~'||fnd_global.resp_id||'~'||workbook_name);
return 1;
--
END disco_client_info;

Grant execute on disco_client_info to Public;


/* Grant execute privilege to Public */

Grant execute on disco_client_info to Public;

Step 2
=======
Register this function via the Discoverer Administrator

Step 3
=======
Call this function in Discoverer Desktop in all workbooks (which you want to monitor) as condition like below:

disco_client_info(’WORKBOOK_NAME’)=1

Since the function is always going to return 1 the condition will return true always.

Step 4
=======
If you run the below query you will get the information desired:

select module,client_info from v$session where module like 'Discoverer4';


Note: Module information ‘Discoverer4′ is set by Discoverer for Discoverer 4 version
using DBMS_APPLICATION_INFO.SET_MODULE. Change ‘Discoverer4′ to ‘Discoverer902′ for Discoverer 9.0.2 ,
‘Disco9′ for discoverer 9.0.4 and ‘Disco10′ for discoverer 10.1.X.

The draw back is, you are required to hardcode the workbook name in the condition since you can get workbook information dynamically only in title.

-submitted by Rajan