Creating a custom report for OIM 10g


Written by:

The information contained on this blog is for information purposes only, and may at times not apply to your situation. Please read the disclaimer before continuing

I came across THIS document on how to create custom reports in OIM 10g.

All things considered, I believe this Oracle document is unnecessarily complex where in fact creating reports in Oracle Identity Manager (OIM) 10g is fairly simple.

With this in mind, and for my own benefit, I decided to construct my own 'custom report' blog, which illustrates a step-by-step guide on how to create custom reports within OIM 10g

Keep in mind that you should only be constructing custom reports this way if they can be considered simplistic and only return a small amount of data. This isn't a solution for large scale deployments so as an alternative would sugest using BI publisher.

The Goal

The goal here is to create a custom report containing a list of all the identities and the resource information. In this case, the resource is called MY_USERS_GTC.

Within this report, I want to present the following values:

  • username
  • first name
  • lastname
  • Resource Name
  • E-mail
  • resource status

Note: Normally I would have not consider pulling the 'email address' from the resource as it's not from a non trusted resource, unlike HR. I could not be bothered in changing it so for the purpose of this blog, lets pretend is something like 'access level' or 'access rights'.

The report will be called 'UsersWithMYUSERSGTCResource'

In order to create a new report within OIM 10g, you will be required to provide the following:
  • a .SQL file containing a modified store procedure
  • XML file containing the reporting details
  • Modification of the file

Step 1: Connect to the database


In this example, I have decided to use the Oracle freeware product, SQL Developer. You need to create a new connection. Enter in your Oracle property values and test the connection by clicking connect.

Step 2: Locating the Xelsysadm user


As you may already know, the assignment of the tables, views, stored procedures etc are assigned to individual identities. In my case, I have assigned the OIM schema to the 'xelsysadm' user. Unless you have changed the user, the default is xelsysadm.

You need to expand out the user xelsysadm, then expand out the procedures.

Step 3: Locate the 'XL_SP_USERRESOURCEACCESS' file


To use as a template for creating new reports, locate the XL_SP_USERRESOURCEACCESS store procedure. Double click on this and a new window will display on the right. This is almost identical to the report that you will be creating, though we will need to make some alterations.

As you have the store procedure open, click on file and click save as.... Now find a suitable location to save this new file and call it 'Default_Store_Procedure_for_Reports.sql'. Once you've done that, click OK.


Step 4: Modify the SQL statement


In order to create a report, you must first construct a SQL DDL Query, which will contain all the information from the database.

This is the SQL query that I will be using:

select distinct usr.usr_login AS "User_Login",
usr.usr_first_name AS "First_Name",
usr.usr_last_name AS "Last_Name",
obj.obj_name AS "Resource_Name",
FROM xelsysadm.usr,,
obi.obj_key = obj.obj_key
AND obi.obi_key = oiu.obi_key
AND oiu.usr_key = usr.usr_key
AND ost.ost_key = oiu.ost_key
AND oiu.orc_key = orc.orc_key
AND orc.orc_key = xelsysadm.UD_MY_USERS.orc_key
AND obj.obj_name= 'MY_USERS_GTC'

You will probably need to change the object name and the values you are pulling

Step 5: Altering the Store procedure

Now you have the default file that you saved and the SQL query, you will need to start manipulating the store procedure file by doing the following:

  • 1. Change the procedure name (Step 6)
  • 2. Removing/Adding parameters (Step 7 + 8)
  • 3. Removing/Adding the IF statements (Step 9)
  • 4. Include SQL query in SP (Step 10)
  • 5. Alter the (Step 11)


There are a few changes that you need to do there, you start off by changing the stored procedure name into something else.

On line 2, change the store procedure name...

PROCEDURE XL_SP_UserResourceAccess (



On line 327, change the store procedure name...

END XL_SP_UserResourceAccess;



Step 7: Remove any unnecessary parameters

The first 12 parameters within the store procedure (upto strudfcolumnvaluelist_in) are mandatory parameters.
For the meanwhile, remove all the values that you will not be needing:

struserlogin_in IN VARCHAR2,
strfirstname_in IN VARCHAR2,
strmiddlename_in IN VARCHAR2,
strlastname_in IN VARCHAR2,
struseremail_in IN VARCHAR2,
strorgname_in IN VARCHAR2,
strusergroup_in IN VARCHAR2,
strmgrfirstname_in IN VARCHAR2,
strmgrlastname_in IN VARCHAR2,
struserstatus_in IN VARCHAR2,
struseremptype_in IN VARCHAR2

Step 8: Include the necessary parameters


As I mentioned before, I will be wanting to display the following values within the search query:

  • username
  • first name
  • lastname
  • resource status
  • E-mail
When creating the report, which is a later step, I will be using the imported values into the report. I am also allowing the user to enter in any of the values as part of the query

I will be using the standard report naming convention. for reports, it's str_in. Call the field name whatever you want, as long as it's similar to the original import field.

username within my report will be struserlogin_in
lastname within my report will be strlastname_in

So I need to include all the fields that can use for the search query

struserlogin_in IN VARCHAR2,
strfirstname_in IN VARCHAR2,
strlastname_in IN VARCHAR2,
strresourcestatus_in IN VARCHAR2,
strservicenewacclevel_in IN VARCHAR2

Step 9: Include/remove any IF statements


Starting around the line 95 mark within the same file, you will need to remove any of the IF statements from step 5 and include any IF statements from step 6 Example, I am including an IF statement for 'strdatabase_in'
Review the form that is assigned to the resource object of interest. In this case, I am extracting information from the Oracle RO Resource. Form associated to that is called 'UD_DB_DR'
The field label value associated to this form that I wish to search for is called 'E-mail'. The name of this is called UD_DB_MY_USERS_Email.

Therefore, I will be using the structure of 'Form Designer.Column Property' to locate the value within the IF statement.


IF strdatabase_in IS NOT NULL THEN
whereclause := whereclause
|| UPPER (''''||stremail_in||'''')
|| ' ';

Make sure there are IF statements for all the additional field that you've added within step 6


There are other steps required, which can be located within this blog,

'Creating custom reports part 2'.