Data Migration can be used to read a file and load data into a container table. The container table is created by the process. Three ways of loading files are described, OnClient, OnServer and OnServer - EXTTABLE Active. The most efficient is OnServer - EXTTABLE Active which also supports all character encodings supported by the Oracle database. OnServer has limited support for multibyte character encodings. OnClient can only load files in the clients character encoding, is slow when files are large and file size is limited by the amount of memory available to the windows client.
OnServer load files accessible from the database server while OnClient load files accessible form the windows client.
The container table named IC_<job ID>_TAB is created by the process. If a container table already exist it will be saved as IC_<job ID>_BKP (overwriting any previous copy).
A file can be read by functionality in the Windows client. The file can be read from any folder accessible by the Windows client. Files read by the Windows client must be encoded in the Windows client characters set. If the file is not encoded in the clients character set, characters not recognized will be lost when the file is saved to the database. A file is loaded into the Windows clients memory before it is saved to the database. The available memory will limit the size of files that can be processed by the Windows Client. Large files may also take a long time to process.
Create a new data migration job in Migration Job Maintenance and enter values for Job ID, Description, Procedure Name, View Name, Group ID (optional) and File Location. The columns in the file is either separated by a separator character or each column has a starting position and a length. If the file has columns separators enter Column Separator.
In File Information it is also possible to enter File Path, File Name and Character Set. Character Set is ONLY used when File Location = OnServer. When File Location = OnClient the file must be encoded in the clients character set.
In File Configuration it is also possible to enter Date Format and String Delimiter. Decimal Point, Thousands Separator and Minus Pos is not used.
Value in Procedure Name must be CREATE_TABLE_FROM_FILE.
At this point the File Mapping tab is empty.
The File Mapping tab can be entered manually with columns corresponding with the data in the file that shall be loaded into the container table. But to make the process easier the name of a suitable view can be entered in the View Name column. Enter a view name, save and query. The File Mapping tab will be populated with the views columns.
Update the File Mapping tab. Enter position for used columns, add new columns and remove columns not used.
For separated files the value in Pos is the columns relative position on the line. For files that has no separators the value in Pos is the starting position of the value and Length is the number of characters for the value.
The Rules tab is populated automatically with rules that apply to the type of job created. Other rules may be added.
If the file contains a header line add the rule SKIPLINES, set Rule Flag = Active and specify the number of lines (Rule Value) from the beginning of the file that shall be ignored.
When File Location = OnClient the file must be loaded into the Windows Client with the context menu function Load File... in the Detail tab in the Execute Job window. When loading the file there is a limitation for files with Unicode character set without a BOM (Byte Order Mark), the characters will not be read properly. Therefore the workaround will be to convert the file to a Unicode character set which has the BOM and do the loading.
The content of the file is loaded into the File String column. If FilePath and/or FileName has values the Select File to Load dialog is opened in the specified folder and with specified file name.
When the file has been loaded into the File String column press Save and then select Start Online in the context menu to start the job. The job will rename the old container table (if present), create a new container table and load the data into the container table.
The container table named IC_<job ID>_TAB is created by the process. If a container table already exist it will be saved as IC_<job ID>_BKP (overwriting any previous copy).
Read only GRANTS are given for the IAL OWNER.
A file can be read by functionality in the Oracle database. The Oracle UTL-FILE utility is used. The File Path column has a List-Of-Value with available folders. The List-Of-Value must be prepared in advance. Available folders are added to the List-Of-Value with the Oracle SQL command CREATE OR REPLACE DIRECTORY <directory> AS '<path>'.
I.e. CREATE OR REPLACE DIRECTORY FNDMIG AS 'E:\utl_file\...'
The path specified must be available on the database server and accessible to the user running the Oracle database. On a Windows computer this is usually the Local System Administrator.
The File Name column also have a List-Of-Value showing all files in the folder specified in the File Path column.
The file Character Set must be specified unless it is the same as the database character set. The Character Set column has a List-Of-Value with all character sets supported by the Oracle database.
When reading files with File Location = OnServer any single byte character set should be correctly encoded into the database character set. UTF-8 files should also be correctly encoded but other multi byte character set may not be correctly encoded.
When a file in a multi byte characters set is read it may be correctly inserted into the database but the report may show the wrong number of rows. The reason for reporting wrong number of rows is that the UTL-FILE utility is a line reading process. It recognizes the end-of-line by looking for the OS character set (or database character) set end-of-line character and if they don't match the end-of-line characters in the file wrong number of rows are be reported.
When File Location = OnServer the rule EXTTABLE = Active alters the way the Oracle database process reads the file. When the rule is Active an Oracle External Table is created and used to load the file. This is the most efficient way to load file data into the database.
With rule EXTTABLE = Active files of any character set supported by the Oracle database can be loaded.
When positional notation is used and the file is encoded in a multi byte character set Length in File Mapping tab must be expressed in number of bytes.
The order of the columns in the container table is the order specified in the Attr Seq column. If the Attr Seq column is empty the columns will be order alphabetically.
Oracle External Table statement generated for a separated file:
Oracle External Table statement generated for a positional file:
The six character long CUSTOMER_ID "100101" is stored in this UTF-16 encoded file as 12 bytes "0031 0030 0030 0031 0030 0031". The declaration in the Oracle External Table statement is "CUSTOMER_ID POSITION(1:12)". The declaration of the column "CUSTOMER_ID" in the Oracle External Table will be "VARCHAR2(12 CHAR)" which is not correct but has no influence on the loading process.
The container table is created with the name IC_<Job ID>_TAB. If a table with that name already exist it will be renamed to IC_<Job ID>_BKP.
If a backup table already exist it will be dropped.
When a container table is created a column IC_ROW_NO is always added. This column contains the sequence order in which the rows were loaded from the file. Thos columns can be used in later processing to order the rows in the container table if the order of rows is significant.
If a container table is used in a JOIN with other tables/views in later processing an index on the table can speed up processing.
When rule CREINDEX is Active an index will be created on the column(s) that have Flags = K or P in the File Mapping tab. With Rule Value = UNIQUE a unique index is created.
If more then one column is indexed a concatenated index is created. Use the Attr Seq column to specify the order of the columns in the index
Storage parameters used when container tables are created are maintained in System Parameters.
Errors may occur for several reasons. The processing report shows the number of rows selected from the file, the number of successfully processed rows (committed) and the number of failed rows.
In the Detail tab Status is shown for each line and Error Message for failed lines.
Above is shown an error report when File Location = OnClient - all rows are displayed, successful and failed; and below an error report when File Location = OnServer - only failed rows are displayed.
When File Location = OnServer and rule EXTTABLE = Active the error reporting is different. An error report may look like this:
Two lines in the file has been successfully processed but the other lines has failed for some reason.
The File String column contains the SQL*Loader log. Further down in the log the actual error is reported.
The loader process has created two files named <Job ID>.log and <Job ID>.bad. The log file contains a processing report and is always created, but removed when processing is successful if the rule REMLOG = Active (default). It is the content of the log file that is displayed in the File String column.
The BAD file, <Job ID>.bad, contains the rejected records.
The file is in the same format as the original data file and can be used to load the data after necessary adjustments.
The List-Of-Values on File Path and File Name is not valid when File Location = OnClient. If there are values in File Path (and File Name) the File Load... dialog will open in the File Path folder if the folder exists in the client environment.
The List-Of-Values on File Path contains a list of folders accessible to the Oracle server process on the database server. The List-Of-Values is populated with folders specified in the Oracle database initialization parameter UTL_FILE_DIR and folders specified as DIRECTORY OBJECTS.
Using the parameter UTL_FILE_DIR is an old method and is no longer recommended. If it is used the database has to be restarted when the parameter is changed.
The recommended method is to use directory objects. Directory objects can be created by an Oracle system account and granted to the application owner or by the application owner itself in which case no grant is needed.
