OWB - OMB snippet for Process Flow

> Data Integration Tool (ETL/ELT) > Oracle Warehouse Builder

1 - About

Snippet of the OMB language to alter and create a process flow.

Advertising

3 - Snippets

OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET AND ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET ASSIGN ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET DATA_AUDITOR ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET EMAIL ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET END_ERROR ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET END_LOOP ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET END_SUCCESS ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET END_WARNING ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET FILE_EXISTS ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET FOR_LOOP ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET FORK ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET FTP ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET MANUAL ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET MAPPING ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET NOTIFICATION ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET OR ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET ROUTE ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET SET_STATUS ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET SQLPLUS ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET SUBPROCESS ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET TRANSFORMATION ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET USER_DEFINED ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET WAIT ACTIVITIES
OMBRETRIEVE PROCESS_FLOW ‘EERSTE’ GET WHILE_LOOP ACTIVITIES

Create the functions

#

catch {
  OMBCREATE FUNCTION 'GETFILECOUNT' SET PROPERTIES (RETURN_TYPE,IMPLEMENTATION) VALUES ('NUMBER', 'LANGUAGE JAVA NAME ''UtilFileSystem.getFileCount(java.lang.String) return int'';') ADD PARAMETER 'DIR_PATH' SET PROPERTIES (DATATYPE) VALUES ('VARCHAR2')
}
catch {
  OMBCREATE FUNCTION 'GETNTHFILE' SET PROPERTIES (RETURN_TYPE,IMPLEMENTATION) VALUES ('VARCHAR2', 'LANGUAGE JAVA NAME ''UtilFileSystem.getNthFile(java.lang.String, int) return java.lang.String'';') ADD PARAMETER 'DIR_PATH' SET PROPERTIES (DATATYPE) VALUES ('VARCHAR2') ADD PARAMETER 'N' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')
}
...

#

Create the flow

#

OMBCREATE PROCESS_FLOW '$flow_name'

Add the IN parameter to the flow

#

OMBALTER PROCESS_FLOW '$flow_name' ADD PARAMETER 'DIR_PATH' SET PROPERTIES (DIRECTION,DATATYPE,VALUE) VALUES ('IN','STRING','$dirp')

#

Define the local variables used in the flow

#

OMBALTER PROCESS_FLOW '$flow_name' ADD VARIABLE 'FILE_INDEX' SET PROPERTIES (DATATYPE,VALUE) VALUES ('INTEGER','0')
OMBALTER PROCESS_FLOW '$flow_name' ADD VARIABLE 'FILE_NAME' SET PROPERTIES (DATATYPE,VALUE) VALUES ('STRING','')
OMBALTER PROCESS_FLOW '$flow_name' ADD VARIABLE 'NO_OF_FILES' SET PROPERTIES (DATATYPE,VALUE) VALUES ('INTEGER','0')

#

Advertising

Add all the activities

#

OMBALTER PROCESS_FLOW '$flow_name' ADD TRANSFORMATION ACTIVITY 'GETFILECOUNT' SET REFERENCE TRANSFORMATION '$map_mod_name/GETFILECOUNT'
OMBALTER PROCESS_FLOW '$flow_name' ADD TRANSFORMATION ACTIVITY 'GETNTHFILE' SET REFERENCE TRANSFORMATION '$map_mod_name/GETNTHFILE'
OMBALTER PROCESS_FLOW '$flow_name' ADD WHILE_LOOP ACTIVITY 'WHILE_LOOP'
OMBALTER PROCESS_FLOW '$flow_name' ADD END_LOOP ACTIVITY 'END_LOOP'
OMBALTER PROCESS_FLOW '$flow_name' ADD END_ERROR ACTIVITY 'END_ERROR'
OMBALTER PROCESS_FLOW '$flow_name' ADD MAPPING ACTIVITY 'FILE_LOAD' SET REFERENCE MAPPING '$map_mod_name/$map_name'
OMBALTER PROCESS_FLOW '$flow_name' ADD ASSIGN ACTIVITY 'ASSIGN'

#

Define the transitions between the activities

#

OMBALTER PROCESS_FLOW '$flow_name' ADD TRANSITION 'ST_2_GC' FROM  ACTIVITY 'START1' TO 'GETFILECOUNT'
OMBALTER PROCESS_FLOW '$flow_name' ADD TRANSITION 'GC_2_WH' FROM  ACTIVITY 'GETFILECOUNT' TO 'WHILE_LOOP' SET PROPERTIES (TRANSITION_CONDITION) VALUES ('SUCCESS')
OMBALTER PROCESS_FLOW '$flow_name' ADD TRANSITION 'GC_2_EN' FROM  ACTIVITY 'GETFILECOUNT' TO 'END_ERROR' SET PROPERTIES (TRANSITION_CONDITION) VALUES ('ERROR')
OMBALTER PROCESS_FLOW '$flow_name' ADD TRANSITION 'WH_2_GF' FROM  ACTIVITY 'WHILE_LOOP' TO 'GETNTHFILE' SET PROPERTIES (TRANSITION_CONDITION) VALUES ('LOOP')
OMBALTER PROCESS_FLOW '$flow_name' ADD TRANSITION 'GF_2_MP' FROM  ACTIVITY 'GETNTHFILE' TO 'FILE_LOAD' SET PROPERTIES (TRANSITION_CONDITION) VALUES ('SUCCESS')
OMBALTER PROCESS_FLOW '$flow_name' ADD TRANSITION 'GF_2_ER' FROM  ACTIVITY 'GETNTHFILE' TO 'END_ERROR' SET PROPERTIES (TRANSITION_CONDITION) VALUES ('ERROR')
OMBALTER PROCESS_FLOW '$flow_name' ADD TRANSITION 'MP_2_AS' FROM  ACTIVITY 'FILE_LOAD' TO 'ASSIGN'
OMBALTER PROCESS_FLOW '$flow_name' ADD TRANSITION 'AS_2_EL' FROM  ACTIVITY 'ASSIGN' TO 'END_LOOP'
OMBALTER PROCESS_FLOW '$flow_name' ADD TRANSITION 'EL_2_WL' FROM  ACTIVITY 'END_LOOP' TO 'WHILE_LOOP'
OMBALTER PROCESS_FLOW '$flow_name' ADD TRANSITION 'WH_2_EN' FROM  ACTIVITY 'WHILE_LOOP' TO 'END_SUCCESS' SET PROPERTIES (TRANSITION_CONDITION) VALUES ('EXIT')

#

Define the parameters for the activities

#

OMBALTER PROCESS_FLOW '$flow_name' MODIFY ACTIVITY 'GETFILECOUNT' MODIFY PARAMETER 'DIR_PATH' SET PROPERTIES (BINDING) VALUES ('$flow_name.DIR_PATH')
OMBALTER PROCESS_FLOW '$flow_name' MODIFY ACTIVITY 'GETFILECOUNT' MODIFY PARAMETER 'GETFILECOUNT' SET PROPERTIES (BINDING) VALUES ('NO_OF_FILES')
OMBALTER PROCESS_FLOW '$flow_name' MODIFY ACTIVITY 'WHILE_LOOP' MODIFY PARAMETER 'CONDITION' SET PROPERTIES (VALUE) VALUES ('FILE_INDEX < NO_OF_FILES')
OMBALTER PROCESS_FLOW '$flow_name' MODIFY ACTIVITY 'GETNTHFILE' MODIFY PARAMETER 'GETNTHFILE' SET PROPERTIES (BINDING) VALUES ('FILE_NAME')
OMBALTER PROCESS_FLOW '$flow_name' MODIFY ACTIVITY 'GETNTHFILE' MODIFY PARAMETER 'DIR_PATH' SET PROPERTIES (BINDING) VALUES ('$flow_name.DIR_PATH')
OMBALTER PROCESS_FLOW '$flow_name' MODIFY ACTIVITY 'GETNTHFILE' MODIFY PARAMETER 'N' SET PROPERTIES (BINDING) VALUES ('FILE_INDEX')
OMBALTER PROCESS_FLOW '$flow_name' MODIFY ACTIVITY 'FILE_LOAD' MODIFY PARAMETER 'DATA_FILE_NAME' SET PROPERTIES (VALUE,ISLITERALVALUE) VALUES ('$flow_name.DIR_PATH || ''/'' || FILE_NAME', 'false')
OMBALTER PROCESS_FLOW '$flow_name' MODIFY ACTIVITY 'ASSIGN' MODIFY PARAMETER 'VALUE' SET PROPERTIES (VALUE,ISLITERALVALUE) VALUES ('FILE_INDEX + 1', 'false')
OMBALTER PROCESS_FLOW '$flow_name' MODIFY ACTIVITY 'ASSIGN' MODIFY PARAMETER 'VARIABLE' SET PROPERTIES (BINDING) VALUES ('FILE_INDEX')