OWB SQL*Loader Mapping

In response to a quesion raised on OTN Forum, http://forums.oracle.com/forums/thread.jspa?threadID=514132&tstart=0

The OWB Mapping for SQL*Loader is -

• FILE MODULE CREATION

Create a File Module, if you have already created goto 2.
To create the File Module navigate to - Project -> Files.
Right Click on the FilesNode, and create a new Module.
During this step, you need specify the "local directory" from the source files need to imported and sampled. Usually this will be on your desktop.

• IMPORT THE SOURCE FILE

Now Import the file into the File Module just created.
To do this, right click on FileModuleName and Select "Import". While importing you can sample the source file to define the field names, data types and record structure.

here, you can specify if you want to skip any records from the beginning of the file





















Here, you have to specify whether the file is a delimited / fixed record format. If delimited, choose the appropriate delimitor. The field "Fixed Delimitor" is editable ie if you have a delimitor which is not listed in the drop down box, you can just type the delimitor.







Here, you can specify the data types and any SQL*Loader conversion functions. Also, you have a check box to say if the first record in the file specifies the field names.

Click Next and Finish.

This completes the "Import and Sampling " of source files.

• BUILD OWB MAPPING

Expand the Oracle Module, where the mapping need to be created. Right click onf "Mappings" node and select "Create Mapping". Build the mapping as per your requirements.

• CONFIGURING THE OWB MAP

The things that are important for SQL*Loader mapping are

• specifying the source data file.

To specify a logical name for the soure file, Select "Source Data File" tab and click on the [...] button, which opens up a pop-up window where you can give a name , and click on "Add" and then on "Ok" button.













Now, expand the "Source Data File" tab, and you should see an entry with the name as mentioned in previous step.
Expand the Source File and you should see entries to specify the

• Data File Location - This is the Location Name pointing to the directory path where the source file is residing on server
• Data File Name - Name of the source file on server, the name should exactly match as on the server.
• Bad File Location & Name - specify the location pointing to the directory where you want the bad file to be created on server.
• Discard File Location & Name - specify the location pointing to the directory where you want the discard file to be created on server.
• Log file location and file name - specify the location pointing to the directory where you want the Log file to be created.
  
• Errors Allowed - Specify the number of errors that can be allowed before the OWB mapping to fail.
  
• Control File Location and Name - specify the location pointing to the directory where you want the Sql*Loader control file to be created.
• Expand the tab "Source and Targets", expand your target table, expand "Sql*Loader Parameters" and change "Trailing Nullcols" to "True".
  
• with all the above configuration settings, the window should be as
  

• Close the above window, commit the changes , Register the locations ( by specifying the access parameters, the directory path on the server where the source file is residing )Deploy the mapping and Run the mapping.
  

If everything is right, you should load data from you source file into target table.