Oracle Workflow Monitor Configuration

This configuration is based if Oracle Database and HTTP Server (Apache or Oracle Application Server) are on the same machine.

Standalone Apache Server or Oracle Application Server can be used as your HTTP Server.

The configuration belwo is using Oracle Application Server 10gAS as HTTP Server

Oracle Workflow Version 2.6.4
Oracle Database 11.1.0.7
Oracle Application Server(OAS) 10gAS 10.1.2.0

The Oracle Application Server(OAS) should have a different home directory from the database home directory.

Database Home - $ORACLE_HOME
OAS Home - $OHS_HOME

Perform the following steps -

1) If you want help files with your workflow monitor go to the Workflow Directory
$ORACLE_HOME/owb/wf

Run the following command -
unzip wfdoc.zip file

This should create a "doc" directory under $ORACLE_HOME/owb/wf

Make sure you just unzip it in the same directory and do not create your own directory and unzip into it.

2) Create a DAD (Data Acess Descriptor) entry for your oracle workflow home page
Edit the dads.conf file.
$OHS_HOME/ohs/modplsql/conf/dads.conf
Add the following entry into it. Make sure you subsititue the right hostname ,port and SID.

### entry for oracle workflow monitor #####
<Location /pls/test_owf>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None Plsql
DatabaseUsername OWF_MGR
PlsqlDatabaseConnectString hostname:Port:SID SIDFormat
PlsqlAuthenticationMode Basic
PlsqlDefaultPage wfa_html.home
PlsqlDocumentTablename scott.wwdoc_document
PlsqlDocumentPath docs
PlsqlDocumentProcedure scott.wwdoc_process.process_download
</Location>

3) Handling Workflow Images, Help Documents and Java Libraries
Edit the httpd.conf file under $OHS_HOME/ohs/conf

ORACLE_HOME Directory - /local/oracle/product/11.1.0/db_1 (subsitute your own)

Look for word Alias or Aliases in the httpd.conf file and add the following lines below the other Alias entries

Alias /OA_MEDIA/ "/local/oracle/product/11.1.0/db_1/owb/wf/java/oracle/apps/fnd/wf/icons/"
Alias /OA_JAVA/ "/local/oracle/product/11.1.0/db_1/jlib/"
Alias /OA_DOC/ "/local/oracle/product/11.1.0/db_1/owb/wf/doc/"



4) Restarting the HTTP Server

Its necessary to restrat the HTTP Server for the new changes to take effect.

Run the following commands from the $OHS_HOME/opmn/bin

To stop the HTTP Server
opmnctl stopproc ias-component=HTTP_Server

To start the HTTP Server
opmnctl startproc ias-component=HTTP_Server

5) Checking the browser

Log on to any browser and type the following-

http://hostname:7777/pls/owf_test/wfa_html.home

It should ask you a username and password , enter OWF_MGR as username and your password.

Insert Operator - Expert

Inserting an Operator in a mapping between different connected operators is a time consuming task considereing if you have mappings with complex logic and hundreds of columns.

Often a small change in requirement (like adding new columns or change in business logic) can result in deleting hundreds of connection from one operator to another for inserting a new operator in a mapping. Recreating connection again increases the development and testing effort, since all the connections have been created manually again.

Created an expert which can insert some selected operators (aggregator, set_operator, deduplicator, expression, joiner, splitter, sorter, and filter) anywhere in a mapping retaining all connections, if logically possible. Eventually less development,maintanence and testing effort.

Expert can be downloaded here. Expert does not come with any WARRANTY !!!!!

Expert is designed to insert an operator in a mapping, retaining all the existing connections.
This Expert is governed by the OWB laws of designing a mapping.

A quick demo how to use it.

Download the zip file and Import the expert and you should see it under Global Explorer/Public Expert/MAP_ACC/INSERT_OPERATOR


Add expert to a mapping. Right click on a mapping (in my case mapping name is "TEST" ) and select "Add/Remove Experts Here" option and select the "INSERT_OPERATOR" expert


***Time to test the expert ****

Mapping “TEST” populates a table with 120+ columns and has more than 20 key lookups and multiple joins, expressions, etc.Let’s insert a filter operator after Joiner "JNR_ALL" in the mapping "TEST" as shown below
Right click on the mapping and Select "Insert Operator"




A window will pop up.
Select "FILTER" as your Insert Operator.
You can select where you want to insert the operator "BEFORE" or "AFTER" the map operator.

Select "AFTER" as your position and Select "JNR_ALL" as map operator.
Check Box "Default outgoing connections from the new operator" will allow you to remap connections from the new operator to the existing default values.

Press "OK" to continue


After the expert has run, Open the mapping and a filter is inserted and all the connections are retained.


Now lets insert a sorter in the same mapping before the target table (PS_D_CRSE_CLASSMTG_STG).

Open the mapping after the expert has run and a new sorter is inserted and all the connections retained.

Note: Expert does not adds any Conditional logic (like filter or joiner condition) to any inserted operators.

Operator Type at runtime - OMB command

It's possible to get all the operator names within a mapping.
It's possible to list operator names as per operator type like Joiner, Sorter, Filter,etc within a mapping.

While coding an OMB script, i needed an OMB command to find the operator type from operator name at runtime.

How can we get Operator Type ( whether its a joiner,cube,dimension,etc) from the operator name within a mapping ?

Undocumented Operator property "STRONG_TYPE_NAME" can be used to get it.

Command:-
OMBRETRIEVE MAPPING 'MAPPING_NAME' OPERATOR 'OPERATOR_NAME' GET PROPERTIES (STRONG_TYPE_NAME)

Result:-
oracle.wh.service.impl.mapping.component.Join (If the operator is Joiner )

oracle.wh.service.impl.mapping.component.Cube (If the operator is Cube )

and so on...


A TCL logic needs to written to parse the result string ( get the last word after the last period "." ) .

Ispredefined Constant ?

Whenever you drag a Constant operator into your OWB mapping and define a constant , there is a check box with "Ispredefined Constant" under the attribute properties. What is "Ispredefined Constant"?

This topic is covered on lot of forums and blogs but i would like to go over it again.

I find two good uses of it.
1) On generation of OWB code for any mapping , constants are generated by OWB like
get_model_name (name of the mapping)
get_selected (number of rows selected)
get_inserted (number of rows inserted)
get_updated (number of rows updated)
etc..




















These constants can be used within your mappings to get value at runtime.


For eg: If you are trying to get mapping name you can use the "get_model_name" in the expression value of the constant with Ispredefined checkbox checked. Make sure you do NOT put single quotes around the expression value.
























2) Whenever you declare a constant in OWB mappings make sure you always check the Ispredefined constant checkbox, this helps whenever you are testing your mappings by generating intermediate code.

If you do not have Ispredefined Constant checked, OWB generates some internally generated name instead of expression value.

With Ispredefined constant set the expression value is subsititued automatically



















Note: OWB 10.2.0.4 generates the right intermediate code without needing to check the Ispredefined Constant checkbox.

SCD Type2 Mapping Accelelator Expert for Table Operator

OWB provides SCD Type 2 / Type 3 option(great feature) for a Dimension Operator. For a table operator, mapping needs to be manually created to handle SCD's. Working for one of my client , was assigned a task to generate mappings which would handle SCD TYPE 2 for table operator being used as Dimension.

Well, there were few dimensions with more than 120+ columns and 100 + SCD columns/Trigger History columns.


Yes, Dimensions with 120+ attributes and triggering history on more than 100+ columns.


Never thought would have to go through this but then generating mappings manaully and testing each and every column for SCD is not the best way to go ahead.


Creating an OWB Expert was the only way around it.


It took few weeks to create and test it but now its delievering the goods for the project. Its Smoking !!!!

Expert saved hours of effort on creating and testing all these mappings. :-)


You can download the expert from here. Import expert into your Design repository. Warning - No warranty with the expert, It works for me hope it does it for you.


Here is a quick demo of it ...

The expert executes in the context of a project. Start the expert

--Select the oracle target Module from the drop down list.



- Select target table (dimension) from the drop down list.




-- Select target table (dimension) from the drop down list.



-- Set the target details

1) Provide a mapping name
2) Select Surrogate Key
3) Select Effective date column
4) Select Expiration date column
5) Select Business keys (Natural keys)
6) Select SCD Attributes (Trigger History)
7) Select the sequence name
8) Insert Only Attributes ( Columns for which gets inserted but never updated)
Property “Load Column when updating row” is set to “No”


--Select Source type from the drop down list ( Source can be TABLES/VIEWS/EXTERNAL_TABLES/MATERIALIZED_VIEWS)
If there is no source you can select NO_SOURCE.

-- Select Source Oracle Module

-- Select Source Object

-- Expert will generate a new mapping. Open the new mapping , press the auto layout button. You can validate the mapping before deploying.