Process Flow - Execute a map for all files in a directory - Part 2

This note is a description of a useful expert (download at end) for generating process flow components for loading all files from a directory based on the design in the earlier post described here. The expert prompts for minimal information and builds a flow containing the selected map (a flat file/SQL*Loader map), functions and while loop from the earlier design. The expert also generates the 2 PLSQL functions that are java stored procedures. So all you need to do is load the dependent class into the schema where the functions are deployed.

You can add the expert as a short cut of a map, this will let you run the expert from a flat file map;

Flow Accel 1:

You are then prompted for the process flow module, package, process name and the directory where the data files reside;

Flow Accel 2:

That's it basically, you should have a process flow generated, plus the supporting functions in the same module as the map!

Flow Accel 3:

Here we see the 2 PLSQL functions generated, these are the java stored procedures that depend on the Java class in this post;

Flow Accel 4:

Note in OWB you can also define PLSQL functions/procedures that are Java stored procedures (you can load the java directly in the database no need to compile outside), here is the implementation I have generated for one of the functions;

Flow Accel 6:

And finally (but definitely not least) here is the generated process flow that takes care of the nitty gritty details of the activity setup, the loop conditions etc.;

Flow Accel 5:

So in addition to deploying the maps;
  1. perform a loadjava on the UtilFileSystem.java file from this post
  2. grant permissions (using dbms_java.grant_permission) on the directory you need - as is done in this post
  3. deploy the GETFILECOUNT and GETNTHFILE functions generated with this expert
  4. set the location for the process flow module and deploy
  5. it is now ready to run.
The MDL file for this expert can be found here on the blog - after import you will find it in the Global Explorer panel, under FLOW_ACCELERATORS / LOAD_FILES_FROM_DIRECTORY. You should add it as a shortcut ('add/remove experts here' upon right click of a map) to a map in the main tree.

Here are some snippets of the OMB used within the expert;
...
# 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')

#
# 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')

There are quite a few useful examples above that are not totally obvious from the scripting documentation.

In summary this is a useful little accelerator which you can also use to see how
basic constructs like LOOPs are constructed in process flows.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today