I was playing with the XML as file target in OWB and I noticed some things that are worthwhile mentioning (I thought)... some of these also apply to a generic flat file though...
First things first, if you want to write to file (using the default operator as described here) make sure you understand that we are using UTL_FILE as a mechanism. This means certain things as to how to set up access to directories. More about is in the Oracle documentation on utilities (I think).
Now if you use a file as target, you do NOT have to:
- Create a file definition first (provided you are not interested in fancy attribute names etc.)
- Synchronize (or create and bind initially) unless you really want to. The reason for doing this is that we will create a file definition for you and it makes lineage more complete (but the code will work without doing this)
What you do need to do is go into mapping configuration and set the data file properties:
All of this information will be used to generate the actual mapping. Not checking the XML box obviously will give you an ASCII output file.
Before we go to the XML case, do be aware of the fact that the file is defined as having as many fields (and seperators) as the file definition in the mapping. So for a regular flat file with this mapping:
You get the following result (Note the last ',' in the file!):
1000,999,1,10/1/05,6,
1001,175955,1,10/1/05,6,
1002,175955,1,10/1/05,6,
1003,205852,1,10/20/05,6,
1004,184497,1,11/1/05,8,
1005,180226,1,10/5/05,17,
1006,175955,1,11/1/05,7,
1007,175955,2,9/30/05,4,
1008,406995,1,11/29/05,118,
1009,180226,2,12/12/05,60,
1010,193039,1,12/1/05,118,
1011,197310,1,11/28/05,104,
1012,193039,1,11/2/05,69,
Now if we look at the same mapping, but now creating an XML file (and only for the XML variant) and not mapping an attribute or element in the XML file, you will run into an error upon deployment (PLS-00201: identifier 'NULL$1' must be declared)...
To avoid this and to explicitly model your expected data result (e.g. that last attribute - or any middle attribute in the file) you should create a mapping like this specifically adding a NULL value into the XML file.
That way you will get the following (just a single 'row' in this case):
Note the 'empty' status part for this XML file... If you work with the operator this way you will get compiling code and an explicit model of what data you are expecting. If you want to exclude attributes from the file, make sure that you remove the attribute from the definition. Since this is only captured (see first part of the post) in the mapping, this can be done quite quickly and efficiently.
