Some time ago I needed a script to get a hierarchical list of all mappings and process flows that where tied together. I had some problems finding out the type of activities of a process flow. Because, if I encountered a Sub Process flow I wanted to recursively go into that subprocess and get all activities there. I could not find any property that contained the needed information but I found out that you can get a list of all subprocess activities or mappings in a processflow. Like this:
OMBRETRIEVE PROCESS_FLOW '$p_procesFlow' GET SUBPROCESS ACTIVITIES OMBRETRIEVE PROCESS_FLOW '$p_procesFlow' GET MAPPING ACTIVITIES
My immediate problem was solved. However there are many more types that can be used and it sure was not the most elegant way to do this. But I had no idea how to do it better until I found an undocumented property here. Below the procedure I created to get the type of an activity or operator.
proc get_typ {p_parentType p_parent p_childType p_child o_typ } {
upvar $o_typ l_typ
switch $p_childType {
"OPERATOR" { set pos 6 }
"ACTIVITY" { set pos 5 }
}
set l_typ [lindex \
[split [OMBRETRIEVE $p_parentType '$p_parent' \
$p_childType '$p_child' \
GET PROPERTIES (STRONG_TYPE_NAME) ] '.' ] $pos ]
switch [string toupper $l_typ] {
"AGGREGATION" { set l_typ AGGREGATOR }
"ANYDATACAST" { set l_typ ANYDATA_CAST }
"VARIABLES" { set l_typ CONSTANT }
"USERTYPES" { set l_typ CONSTRUCT_OBJECT }
"PSEUDOCOLUMN" { set l_typ DATA_GENERATOR }
"DISTINCT" { set l_typ DEDUPLICATOR }
"EXTERNALTABLE" { set l_typ EXTERNAL_TABLE }
"FLATFILE" { set l_typ FLAT_FILE }
"MAPPINGINPUTPARAMETERS" { set l_typ INPUT_PARAMETER }
"JOIN" { set l_typ JOINER }
"KEYLOOKUP" { set l_typ KEY_LOOKUP }
"MATERIALIZEDVIEW" { set l_typ MATERIALIZED_VIEW }
"NAMEADDRESS" { set l_typ NAME_AND_ADDRESS }
"MAPPINGOUTPUTPARAMETERS" { set l_typ OUTPUT_PARAMETER }
"SUBMAP" { set l_typ PLUGGABLE_MAPPING }
"POSTMAPTRIGGER" { set l_typ POSTMAPPING_PROCESS }
"PREMAPTRIGGER" { set l_typ PREMAPPING_PROCESS }
"SETOPERATION" { set l_typ SET_OPERATION }
"ORDERBY" { set l_typ SORTER }
"TABLEFUNCTION" { set l_typ TABLE_FUNCTION }
"TRANSFORMFUNCTION" { set l_typ TRANSFORMATION }
default { set l_typ [string toupper $l_typ ] }
}
}
I created a temporary process flow called TMP and a likewise named mapping. In both I included all possible activitiy or operator types. Not resulting in a valid object, but that was not the point of the excercise. As shown in the procedure above the names of the types retrieved with the STRONG_TYPE_NAME property does not exactly match the name used in the OMB language. So I had to add a translation switch in the procedure. Maybe a bit too straightforward, but it works.
Now I can find the type and use that to generate or alter all kinds of operators or activities.
Below a simple example showing the possible types.
OMBCC '/<project>/<oracle_module>'
set map TMP
set ops [OMBRETRIEVE MAPPING '$map' GET OPERATORS]
foreach op $ops {
get_typ MAPPING $map OPERATOR $op typ
puts "Operator $op is of type $typ"
puts "OMBRETRIEVE MAPPING '$map' GET $typ OPERATORS"
OMBRETRIEVE MAPPING '$map' GET $typ OPERATORS
}
OMBCC '/<project>/<processflow_module>/<processflow_package>/'
set pf TMP
set acts [OMBRETRIEVE PROCESS_FLOW '$pf' GET ACTIVITIES]
foreach act $acts {
get_typ PROCESS_FLOW $pf ACTIVITY $act typ
puts "Activity $act is of type $typ"
puts "OMBRETRIEVE PROCESS_FLOW '$pf' GET $typ ACTIVITIES"
if {$typ == "START" } { puts "START activity is special"
} else {
OMBRETRIEVE PROCESS_FLOW '$pf' GET $typ ACTIVITIES
}
}
And the output is below. I explicitly named the operators/activities after the type they were to enable an easy translation.
Operator AGGREGATOR is of type AGGREGATOR Operator ANYDATA_CAST is of type ANYDATA_CAST Operator CONSTANT is of type CONSTANT Operator CONSTRUCT_OBJECT is of type CONSTRUCT_OBJECT Operator DATA_GENERATOR is of type DATA_GENERATOR Operator DEDUPLICATOR is of type DEDUPLICATOR Operator EXPAND_OBJECT is of type CONSTRUCT_OBJECT Operator EXPRESSION is of type EXPRESSION Operator EXTERNAL_TABLE is of type EXTERNAL_TABLE Operator FILTER is of type FILTER Operator FLAT_FILE is of type FLAT_FILE Operator INPUT_PARAMETER is of type INPUT_PARAMETER Operator JOINER is of type JOINER Operator KEY_LOOKUP is of type KEY_LOOKUP Operator LCRSPLITTER is of type LCRSPLITTER Operator LCR_CAST is of type LCRCAST Operator MATCHMERGE is of type MATCHMERGE Operator MATERIALIZED_VIEW_1 is of type MATERIALIZED_VIEW Operator NAME_AND_ADDRESS is of type NAME_AND_ADDRESS Operator OUTPUT_PARAMETER is of type OUTPUT_PARAMETER Operator PIVOT is of type PIVOT Operator PLUGGABLE_MAPPING is of type PLUGGABLE_MAPPING Operator POST_MAPPING_PROCESS is of type POSTMAPPING_PROCESS Operator PRE_MAPPING_PROCESS is of type PREMAPPING_PROCESS Operator SEQEUNCE is of type SEQUENCE Operator SET_OPERATION is of type SET_OPERATION Operator SORTER is of type SORTER Operator SPLITTER is of type SPLITTER Operator TABLE_ is of type TABLE Operator TABLE_FUNCTION is of type TABLE_FUNCTION Operator TRANSFORMATION is of type TRANSFORMATION Operator UNPIVOT is of type PIVOT Operator VARRAY_ITERATOR is of type CONSTRUCT_OBJECT Operator VIEW_ is of type VIEW Activity WHILE_LOOP is of type WHILE_LOOP Activity EMAIL is of type EMAIL Activity NOTIFICATION is of type NOTIFICATION Activity END_SUCCESS is of type END_SUCCESS Activity MAPPING is of type MAPPING Activity ROUTE is of type ROUTE Activity OR1 is of type OR Activity TRANSFORMATION is of type TRANSFORMATION Activity AND1 is of type AND Activity END_WARNING is of type END_WARNING Activity END_LOOP is of type END_LOOP Activity WAIT is of type WAIT Activity MANUAL is of type MANUAL Activity USER_DEFINED is of type USER_DEFINED Activity START1 is of type START Activity ASSIGN is of type ASSIGN Activity END_LOOP_1 is of type END_LOOP Activity FORK is of type FORK Activity SET_STATUS is of type SET_STATUS Activity FILE_EXISTS is of type FILE_EXISTS Activity END_LOOP_2 is of type END_LOOP Activity FTP is of type FTP Activity SQLPLUS is of type SQLPLUS Activity END_ERROR is of type END_ERROR Activity FOR_LOOP is of type FOR_LOOP Activity DATA_AUDITOR_MONITOR is of type DATA_AUDITOR Activity SUBPROCESS is of type SUBPROCESS
Klein Oracle denkraam 
Hi, I’m a newby to OMB Plus and I’m just just wondering where you got the list of operator types from? ie how did you know to convert MAPPINGINPUTPARAMETERS to INPUT_PARAMETER for instance? I’ve been looking everywhere for this info but I can’t find anything to help, seems OMB isn’t very well documented…
Ignore my last post, for those interested this link has a list of operator types: http://oracle.su/docs/11g/owb.112/e10584/omb_appendix.htm#BABHHFJE
Harley,
Thanks for the link. When I was looking into it, I was on OWB 10.2 and there was nothing in the documentation. I just created a silly mapping with all the different objects and listed all operator types using OMBPlus which gave me the translation. Silly, cumbersome but effective. I’m glad it’s in the documentation now.
Regards,
Eric.
[…] By Buddel […]