OMBPlus and child types in a mapping or process flow

7 10 2010

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
About these ads

Actions

Information

3 responses

28 06 2012
Harley

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…

28 06 2012
Harley

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

28 06 2012
Buddel

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

%d bloggers like this: