How to call a map for every file in a directory? Flat file maps have the data file name as a parameter. The map can be called from a flow and the data file name set, the map will be wrapped in a while loop that iterates over every file in a directory. Here is an illustration, it uses the functions getfilecount and getnthfile from the post here.
The flow looks like you'd expect for a basic programming loop;
- an initialization block to get the number of files in a directory, I have the directory as a parameter to the process flow too. The initialization is the GETFILECOUNT activity which uses the function from the blog post linked above.
- the while statement with the condition (iterate for every file in directory using the index)
- get the nth file in the directory using the function GETNTHFILE
- execute the map (LOAD_F2) passing the fully qualified name of the file
- increment the index
- end loop
The process flow uses the following variables and parameters;
In the above image DIR_PATH is a process flow parameter, so the flow can be executed and directory specified upon execution. There are a few variables; one for holding an index for the loop (FILE_INDEX), one for holding the file name returned from getnthfile (FILE_NAME) and another variable for the number of files (NO_OF_FILES).
The while condition for the loop is defined as loop while index is less than the number of files;
The increment index (ASSIGN) activity inside the while loop looks like
This lets us easily execute a single map many times with a group of files. Its also possible to separate the control file/log file location so they can be written to one directory and source data files processed from another (so getnthfile will not process control files etc..).
Comments (4)
I have an OMB script that builds the pattern described above, I'll tidy this up and share. This will just let you click on a map and build the flow components based upon the selected map and some input (module/process flow/directory).
Posted by David Allan | July 5, 2007 7:43 PM
Posted on July 5, 2007 19:43
See post
http://blogs.oracle.com/warehousebuilder/newsItems/viewFullItem$160
for details on the expert.
Posted by David Allan | July 6, 2007 1:59 PM
Posted on July 6, 2007 13:59
Following the same logic, what's needed to get parameters from column's cursor/view/table ???
declare
-- Look for parameters for mapping LOAD_SALES
cursor log is select period, store from log where status = 'OK';
begin
for rec in cursor loop
-- Start mapping for each record
load_sales(rec.period, rec.store);
end loop;
end;
Posted by Antonio Akiyama | September 18, 2008 8:51 AM
Posted on September 18, 2008 08:51
Dave,
I just tried the link for the OMB+ example you mentioned and was unable to get to this.
Can you help me out?
Posted by Paul | September 24, 2008 5:56 AM
Posted on September 24, 2008 05:56