Skip to content

File Migration - Handling Server Files

File Migration- On Server

Data Migration can be used to read a file and load data into a container table. 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.

OnServer load files accessible from the database server while OnClient load files accessible form the windows client.

In here, we discuss how to set File Location On Server, and uses of it.

File Path - Oracle Directory

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 specified as DIRECTORY OBJECTS.

Oracle 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. The Folder should have read/write access.

Syntax: CREATE OR REPLACE DIRECTORY <directory name> AS '<file path>'
Further: GRANT READ (,WRITE) ON DIRECTORY <directory name> TO <user> (i.e.APPOWNER)

oracle directory

The Directory FNDMIG will point to one path in the Test-database and another in the Production database.

Benefits:

  1. We can do this 'on the fly' without having to restart the database.
  2. Use of Directories instead of physical file-paths in the Job Header eliminates the need for editing basic data when we export a job to another database.

Note: 1. Directory names are stored in UPPERCASE and must be referred in UPPERCASE
         2. We sometimes create databases by copying database-files. Remember to update defined DIRECTORIES in the new database with correct server paths.

File Path for OnServer

A file can be read or write by functionality in the Oracle database. Once you selecte File Location as OnServer, directories define as mentioned above will be listed in File Path Field using a LOV. File in the selected file path directory will be listed in File Name field using a LOV.

So you can select the File Path and File name for File Location OnServer for both File In and File Out type File Migration Jobs.

As an Example:

  • If you create a migration Job to Create a Table From File, with File Location OnServer, your data file will fetch from the given directory.
  • If you create a migration job to Write data to File, with File Location OnServer, the job will write data to the given file in your directory.

First Select the File Path from the LOV loaded with all Directories with Read/Write access for File Location OnServer.

on server file path

The File Name column also have a List-Of-Value showing all files in the folder specified in the File Path column.

on server file name

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.

on server char set

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.

on server file config

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.

Finally the log will be look a like,

log

References...

EXTERNAL TABLE to load a file into a IC-table