Query to find the list of all roles associated with a given application instance.

Query to find the list of all roles associated with a given application instance



SELECT UGP_DISPLAY_NAME
FROM POG , UGP  , POL , pop  , app_instance
WHERE POG.UGP_KEY=UGP.UGP_KEY
AND POL.POL_KEY= POG.POL_KEY
and pop.POL_KEY= POL.POL_KEY
and pop.obj_key= app_instance.object_key
and pop.pop_denial =0
and app_instance_display_name = :AppInstDispName

Dynamic date range in BI Publisher

Dynamic date range in BI Publisher


While you are scheduling any BI report, you might need to give the date range.
If you wanna achieve dynamism to your report, below is the syntax.

Below snippet would fetch the results for the last seven days.


Date Range From: {$SYSDATE()-7$}
Date Range To : {$SYSDATE()$}

To know the current status of any entry[You need to know the orc key of the entry you are interested in ] in UD table

To know the current status of any entry[You need to know the orc key of the entry you are interested in ] in UD table


select OST_STATUS,ost_key from OST where ost_key in (select ost_key from oiu where orc_key =1022507)

Time taken to run a java program

****Time taken to run a program*****

Can be used in scheduled tasks in OIM for performance considerations.

long startTimer = System.nanoTime();
long endTimer = System.nanoTime();
System.out.print("Time to run this  "+(endTimer - startTimer)+"ns");

Tips for viewing oim log file

Tips for viewing oim log file

Generally the oim log files are very huge. Most of the engineers try to download the huge files using WIN SCP and use conventional editors like notepad++ or Textpad to search for the error(s).

And some senior folks who are well versed with VI try to use VI commands.

Here is an alternative to both the approaches.

Use the below command to find out the line numbers which will contain the error string you are searching for.

grep -n "Record is inserted into table!" oim_server1.out

Now use below command to display log snippet below two specified line numbers.

sed -n 114993700,114993750p oim_server1.out  

To get list of all AI forms that are in use at the moment

To get list of all AI forms that are in use at the moment


select app_instance_dataset from app_instance where app_instance_dataset is not null;

Query to find out the series of orchestration events for a given request.

Query to find out the series of orchestration events for a given request

select * from orchevents where processid = (select orchestration_process_id from request where request_key = 19957) order by orchorder

How to fetch the lookup values using db query

How to fetch the lookup values using db query

select LKV_ENCODEd, LKV_DECODED from LKV, LKU WHERE lku.lku_type_string_key='Lookup.ABC.SecondaryGroups' and lkv.lku_key = lku.lku_key