Data Migration Job - File Configuration Tab
File Configurations tab uses by file migration type jobs and gives the details of either the source or destination of the file and its characteristics.
File Information & File Configuration
|File Locations||Source or the destination of the file.
|File Path||Valid only when file location is On 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.|
|File Name||The File Name can contain any of the keywords &DATF, &DATE, &TIME, &USER, &SEQN and/or &GUID. &DATF will be replaced with a date in the format specified in Formatting tab - File Configuration - Date Format, &DATE will be replaced with a date in the format YYYYMMDD, &TIME will be replaced with time in the format HH24MISS, &USER with the platform user id, &SEQN with a sequence number and &GUID with a hexadecimal string|
|Character Set||If a Character Set is specified the file will be created in that character set, if a character set is not specified the file will be created in the database character set. The Character Set column has a List-Of-Value with valid character sets. 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. 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.|
|Date Format||Date Format used in file or to be used for file|
|Column Separator||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 shall have columns separators enter Column Separator.|
|Decimal Points||Character used to separator decimal points.|
|String Delimiter||Character used to replace column separator.|
|Thousand Separator||Character used to separator Thousands.|
|Minus Points||Valid values for Minus Pos is 0 - minus sign is placed after the value, 1 - minus sign is placed before the value (when the value is left padded) and 99999 - minus sign is placed before the value (when the value is right padded).|
Directory access on the database servers file system: When the attribute File Location is set to OnServer the UTL_FILE utility is used to access the files. The UTL_FILE utility can only access directories on the server that it has been granted access to. This can be achieved in two ways, using the UTL_FILE_DIR parameter in the database initialization file or using DIRECTORY objects. The preferred way is using directory objects.
Granting directory access using DIRECTORY objects: An Oracle directory object is an alias for a directory in the database servers file system. In order to create directory objects the IFS Application owner, <appowner>, must be granted the CREATE ANY DIRECTORY privilege. Start SQL*Plus (or any other tool) and login as user SYSTEM (or SYS) and execute the command - GRANT CREATE ANY DIRECTORY TO <appowner> - then logon as <appowner> and create the necessary directory objects with the command - CREATE OR REPLACE DIRECTORY <directoryName> AS '<path>', e.g. CREATE OR REPLACE DIRECTORY fndmig AS 'E:\database\utl_file_dir'. A directory is immediately accessible after the creation of the directory object. The value entered in File Path above must be the name (in upper case) of a directory object not the actual file path. Directory objects are accessible from Oracle 8.1.7.
The query SELECT * FROM all_directories shows all directory objects in the database. A directory object owned by another user may not be available. The command for granting access to a directory object is GRANT <object privileges> ON DIRECTORY <directoryName> TO <userName>. where object privileges are READ[, WRITE].
Granting directory access using the UTL_FILE_DIR parameter: This is an Oracle initialization parameter located in the database INIT.ORA file. If the value of parameter is changed the database has to be restarted. The parameter value is a list of directories. E.g. UTL_FILE_DIR = c:\tmp[, E:\database\utl_file_dir[, ...]].The value entered in File Path above must be the same as one of the values in the UTL_FILE_DIR parameter.
The query SELECT value FROM v$parameter where UPPER(name) = 'UTL_FILE_DIR' shows the UTL_FILE_DIR parameter value.