Traditionally, we have used the parameter UTL_FILE_DIR to define file paths for our server files. This is a database parameter, and the database has to be stopped and restarted when the parameter has changed.
Instead, we may use the CREATE DIRECTORY command to define a server path for our files :
Syntax : CREATE OR REPLACE DIRECTORY <directory name> AS '<file
path>'
Further : GRANT READ (,WRITE) ON DIRECTORY <directory name> TO <user> (i.e.APPOWNER)
CREATE OR REPLACE DIRECTORY DIRONSERVER AS '\\machine_name\DirOnServer'; GRANT READ,WRITE ON DIRECTORY DIRONSERVER TO IFSAPP;
Used in Data Migration :
The Directory DIRONSERVER will point to one path in the Test-database and another in the Production 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.
This option is useful if large data files should be loaded into a temporary tables
using procedure CREATE_TABLE_FROM_FILE.
As from Oracle9 it is possible to create a table that points to a server-file, a
so-called External Table.
The table is not populated by data, but the data on the file is accessed by using
SQL*Loader features.
The SQL*Loader reads data from server files faster than UTL_FILE.
Up to App7 CPS3, Data Migration has used Oracle's UTL_FILE utility to read
server files, but we have introduced a new Rule
EXTTABLE that makes
it possible to use Oracle's External Table concept for jobs using procedure
CREATE_TABLE_FROM_FILE.
The file job setup is done in exact same manner as before. But when the job is started,
it will execute in 3 steps :
Benefits: Populating a IC-table with data can be performed 10 times faster than
before.
Below is a setup of a simple file job. After setting up a job, you may preview
what the statements for creating the table + inserting data .
In the LU IntfaceServerFiles, procedures are available and returns complete statements
that are used during execution.
Using the procedure Intface_Server_File_API.Show_Create_Ext_Table_Stmt,
Using the procedure Intface_Server_File_API.Show_Ins_From_Ext_To_Ic_Stmt,
Sometimes we get errors when creating the External Table. As we define all columns as VARCHAR2, the main cause are field length errors.
Data Migration will detect if errors have occurred and will load the LOG-file
into the Detail folder in the Execute Job window.
Note :
EXAMPLE 1 :
Now, we would like to complete the IC-table with the rejected record. See below how it is done.
Note: Remember to deactivate rule CRETABCONF after this execution