Migration Job/Formatting

[About] [Tips&Tricks]

Usage

Use this window to maintain formatting information for file-migrations, or specify source information (table, view JOIN) for source-migrations.
You can also specify WHERE-clause and ORDER BY clause for selections. You will have access to different fields according to the value of Direction, which specifies if the source is a file (In-, Out) or if the source is an Oracle Source.

Notes

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.