In the OWB10G_DEMO project, right click the Files node and select Create Flat File Module.

Click Next on the New Module Wizard: Welcome page. On the New Module Wizard: Name page type FF_MODULE as the module name and click Next.

On the New Module Wizard: Connection Information page, you can either type a valid directory path or use Browse to select the directory.

Click Browse.

In the Flat File Directory Chooser dialog select C:\OWB-OBE\sourcefiles or select the path where you saved your setup files as instructed in Lesson 1 and click OK.

Click Next in the New Module Wizard: Connection Information page.

The New Module Wizard: Location page appears. You will create a new location.

To create a new location, click New. In the New Location Dialog type FF_MODULE_LOC as the name for the location. Accept the default type File System and click OK.

The newly created location will automatically be selected click Next.

The New Module Wizard: Finish page appears, review the module summary information. Check the box - Proceed to the Import Metadata Wizard and click Finish.

Click Next on the the Import Metadata Wizard: Welcome page. The Import Metadata Wizard: Filter Information page appears. Accept the Default selection All Data Files and click Next.

The Import Metadata Wizard: Object Selection page appears. Expand C:\OWB-OBE\sourcefiles or the path that you selected in step number 4 will be displayed. Select CUSTOMERS.TXT from the Available Objects list and move it to the Selected Objects list on the right-hand side using the > (single greater than) and click Next.

The Import Metadata Wizard: Summary and Import page appears. Select the customers_txt record, if it is not already selected and click Sample.

On this page you see file definition (customers_txt) prefixed with a red ball indicator. The red ball indicator indicates that there is no metadata available for these files. Before importing, Warehouse Builder needs to know the structure of these files. Through the sampling process, information about the structure is gathered.

You will learn how to sample the customers.txt file.

The Flat File Sample Wizard: Name page appears. On this page you specify the file's name and the number of rows to sample. Warehouse Builder offers a nearly endless set of character sets you could use to read the data.

Rename customer_txt the default sampled file's name to CUSTOMERS. This is the name Warehouse Builder will refer to the file after the import. Accept the default number of rows to sample as 200 and click Next.

The Flat File Sample Wizard: Record Organization page appears. You use Record Organization page to indicate how records are organized in the file that you are sampling.

Accept the default option of Records delimited by carriage return (<CR>) and click Next.

In the Flat file Sample Wizard: File Layout page, accept the default values of 0 for Skip rows. This file is an example of a single record type file, select Single for Record Type and click Next.

On this page you can specify the number of rows to skip. Warehouse Builder also supports multiple file record types.

The Flat File Sample Wizard: File Format page appears. On this page you specify the formatting for the file structure. Fixed length files as well as character-separated files are supported.

Accept the default selection, Delimited for File Format, comma (,) is the delimiter character enclosed within double quotes (" ").

The Flat File Sample Wizard: Field Properties page appears.

You can choose to use the values in the first record for the field names, when the flat files first record is a header. Check the box Use the first record as the field names.

Notice that some of the data types now have changed. Without the column names, some values are actually numeric. Click Next.

Review the summary specifications after sampling the file customer.txt. Click Finish on the Flat File Sample Wizard: Summary page.

The Import Metadata Wizard: Summary and Import page reappears. The wizard has now completed the sampling process (identifying the structure) for the CUSTOMERS file, which is indicated by the green ball indicator.

Click Finish in the Import Metadata Wizard: Summary and Import to complete the import.

The Importing Progress Dialog indicates the progress of the import. You will see that metadata from the flat files has been imported to the FF_MODULE flat file module.

Collapse the Files node, expand the Databases node, expand the Oracle node and expand the STG node. You will create your external table in the STG module.

Right click the External Tables node, and select Create External Table.

Click Next on the New External Table Wizard: Welcome page. On the New External Table Wizard: Name page type CUSTOMERS_EXT as the name for the external table and click Next.

In the New External Table Wizard: File Selection page select CUSTOMERS flat file definition from the FF_MODULE. Click Next.

On this page you will specify the deployment location of the flat file datafile that you want to use at runtime. In the New External Table Wizard: Locations page select FF_MODULE_LOC. Click Next.

The New External Table Wizard: Finish page shows the result of the external table wizard. Click Finish. You will see the CUSTOMERS_EXT external table in the OWB client.

To validate the external table definition.

Expand the STG target module and expand the External Tables. Right click the CUSTOMERS_EXT external table and select Validate.

The CUSTOMERS_EXT external table is generated and the Validation Progress window displays the progress of the validation and the Validation Results window displays the validation results. Click Close to close the Validation Results window.

Note: Make sure the validation is successful without any errors.

At runtime in the database an external table definition has to have a directory definition in order to get access to the flat file. This Directory object is a materialization of a connector that is defined in the context of a location. OWB will conveniently populate the connector once you validate the External table definition.

You can configure properties of the external table.

Right click the CUSTOMERS_EXT external table and select Configure. The Configuration Properties pops up.

In the Configuration Properties, the Access Specification property allows you to specify a bad file name, the discard file and log file name. This is used for debugging purposes.

In the Configuration Properties, Data Files property, expand DATAFILE1 you will view the name of the data file, you can change the name too. For this example you will use the default. Close the Configuration Properties window.

By default OWB will add a data file to the External table referring to the flat file (customers.txt) name you sampled.

In the OWB client select Project > Deployment Manager

Select OBE_RUNTIME_CONNECTION Runtime Repository Connection from the connection drop-down list and click OK or use the runtime repository connection that you created in Lesson 7.

The Connection Information window opens. Provide the connection Information. You will have to enter only the password, type connect_user or use the same password that you provided in Lesson 7.

The OWB Deployment Manager opens. Expand Target_LOC, expand Connectors and select TARGET_LOC_FF_MODULE_LOC.

Expand target module STG, expand the External Tables node. Hold CTRL and select external table definition CUSTOMERS_EXT. Click Default Action.

Clicking the Default Action button changes the Deploy Action from None to Create.

Click the Generate/Deploy button on the toolbar.

The Location Registration for location FF_MODULE_LOC appears. In this case Username and Password are optional. For hostname enter localhost or <name of your computer>. In the Root Path type C:\OWB-OBE\sourcefiles\ or type the path where you saved you setup files (include the backslash at the end) and click OK.

The Confirm Commit dialog pops up. Click Commit to commit your changes before deploying.

After generating the deployment specification the Pre Deployment generation results window opens.

On the Pre Deployment Generation Results window select CUSTOMERS_EXT external table definition. Select Script tab at the bottom, select the CUSTOMERS_EXT record in the bottom and click View Code. The script shows a fully defined external table.

In the Pre Deployment generation results window click Deploy.

The Deploy results window displays and the deployment is successful. Click OK to close the Deployment results window.

After successful deployment the OWB Deployment Manager Deploy Status column displays Success. Close the OWB Deployment Manager.

To check whether the external table definition works. You will log into SQL Plus.

Click Start > Programs > Oracle [Oracle_Home] > Application Development > SQL Plus. In the Log on window provide connection information. For username and password type TARGET_SCHEMA, or <your target schema name and password that you created in Lesson 7>. For Host String type obeowb or <your database service name>.

Describe the structure of the table from the SQL prompt using DESC CUSTOMERS_EXT.

Run a select statement at the SQL prompt SELECT statement SELECT * from CUSTOMERS_EXT where rownum < 5; (use the semi colon to terminate the SQL statement)

To close SQL Plus type exit at the SQL prompt.