Tips & Tricks, Server File Environment

Contents

• Use External Table
• EXAMPLE
• Error handling

Use Oracle's DIRECTORY to set up File Paths

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>'  (executed as SYSDBA)
Further : GRANT READ (,WRITE) ON <directory name> TO <user> (i.e.APPOWNER)

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.

Example :

As SYSDBA:

CREATE OR REPLACE DIRECTORY DKA0 AS '/u01/app/oracle/admin/test/utlfile/A0';
GRANT READ,WRITE ON DKA0 TO IFSAPP;

Used in Data Migration :

The Directory DKA0 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.

Use EXTERNAL TABLE to load a file into a IC-table

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 now (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 :

•  First the IC-table will be created. Creation and backup of the table is exactly the same as before.
• Secondly a External Table will be defined, using data from the job header + file-mapping details. All columns will be of type VARCHAR2
• If error occurs, the LOG-file defined in the SQL*Loader part of the statement will be available from the Execute Job windows Detail folder.
• Finally the IC-table will be populated by a INSERT INTO statement selecting data from the External Table.

Benefits: Populating a IC-table with data can be performed 10 times faster than before.
 

Example:

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, GET-functions are available and returns complete statements that are used during execution.

Preview Intface_Server_File_API.Get_Create_Ext_Table_Stmt here >>.
Preview Intface_Server_File_API.Get_Insert_From_Ext_To_Ic_Stmt here >>.

Create External Table example :

<na>

Insert from External to IC-table example :

Error handling with EXTERNAL TABLES

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.
 

EXAMPLE 1 :

1. Always check the feedback-message in Last Info.
   There may be a error-message after the Create Table statement.
2. The LOG-file from the SQL*Loader job will be loaded into the Detail folders File String.
   Search for lines starting with 'KUP' and you will see the Loaders error message.

In this case all lines are rejected because CUST_NO is defined too short compared to file input.

Action : Increase length of column CUST_NO in Migration Job Maintenance/File Mapping.

EXAMPLE 2 :

1. We can see that some records were rejected.....
2. ...but there are 12725 OK records inserted in the IC-table....
3. ... and there is only 1 rejected record

Now, we would like to complete the IC-table with the rejected record. See below how it is done.

4. From LOV on File Name we see that a BAD-file has been created for this job.
   The BAD-file contains the rejected file records.
   We select this file and load it into the Detail folder with RMB 'Load File...'

5. There is 1 record on the file. CUST_NO contains leading blanks + the letter 'x' in pos 21-23, i.e. it exceeds defined length 20 in File Mapping.

6. We remove the trailing 'x' by activating Rule TRUNCVAL. (Values exceeding pos 20 will be truncated)
7. We also activate rule CRETABCONF with value KEEPALL.
   Data Migration will now keep the previous 12725 rows in the IC-table and add this one.
8. When executing the job now, the one missing row is added to the 12725 previous records

Note: Remember to deactivate rule CRETABCONF after this execution