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 onwards 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.
The Rule EXTTABLE that makes it possible to use Oracle's External Table concept for jobs using procedure CREATE_TABLE_FROM_FILE.
Create the file job with the EXTTABLE Rule enabled. When the job is started, it will execute in 3 steps:
- First the IC-table will be created.
- 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.
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.
Create External Table example:
Using the procedure Intface_Server_File_API.Show_Create_Ext_Table_Stmt,
Insert from External to IC-table example:
Using the procedure Intface_Server_File_API.Show_Ins_From_Ext_To_Ic_Stmt,
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 Form Detail tab
- Always check the feedback-message in Last Info.
There may be a error-message after the Create Table statement.
- 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.
If there are rejected records, you can complete the IC-table with the rejected record.
- From the Execute Job form select 'Migration Job'. So that it will direct to the Migration Job detail view. From LOV on File Name under 'File Configuration' tab, 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 save the migration job.
- Now select the Execute Job from Migration Job form. This will direct back to the Execute Job form. First clean up the existing job using 'Clean Up' button and load the file into the Detail folder with 'Load File'. The error records will be there.
- Fix the errors.
- We also activate rule CRETABCONF with value KEEPALL.
Data Migration will now keep the previous 7 rows in the IC-table and add this one.
- When executing the job now, the one missing row is added to the previous records
Note: Remember to deactivate rule CRETABCONF after this execution