‘Oraload’ is a bulk data load utility which is used to load multiple database tables with data files, with a high performance.This is a highly customizable piece of code, written in C++ using Oracle utilities, which can process 3 kinds of files mainly:
- Flat files with bulk data separated by a delimiter
- Files containing multiple DML statements to be acted on to various tables in the database.
- Files with specific format, which contain the application specific, user defined suspension messages and is used to load corresponding static tables in the database for further processing.
The high performing bulk data load with Oraload is been achieved by implementing Dynamic SQL. Since Pro*C/C++ does not support full-function prototyping of SQL programming calls,the sqlda.h & sqlcpr.h headers has been used.
Processing Flat file with ORALOAD
To process flat files with bulk data separated by a delimiter, the Library functions of Oracle, SQLLIB is used. Since the number of place holders and data types for input host variables are unknown, the Method 4 of Dynamic SQL is apt to reach our goal. In this method, a dynamic SQL statement is been built and processed using descriptors
The inputs to the executable are:
- Table name
- External file name containing the data to be loaded
- Operation to be performed in the table, like insert or replace.
In this case, the table description will be bound with the external file to be loaded into the database. The descriptors are declared for the
select items and host arrays, and the host arrays are populated by reading the data from the external file. When the operation performed is ‘Replace’, a delete command will be executed immediately, before proceeding with the host arrays.
Dealing with DML and user defined suspension messages
The files containing DML statements are processed by using Dynamic sql method 1. In method 1, a dynamic sql statement is built from the input file and executed immediately.
For the files containing user defined suspension messages, we use this utility to update the corresponding static tables in database with the updated error messages and codes in the file.
Dealing with constraint
Any constraint violation or error during the execution will throw a user-defined error message, which is helpful to identify the erroneous table and field, and to load it properly/rectify the error. Any oracle error will cause a roll-back, which will revert back the tables to its former state
Advantages of ORALOAD vs SQL*Loader
- When Oraload encounters a bad data it stops the execution, providing respective error code and user friendly error message; whereas in SQL*Loader, rows that violate unique key constraints are not rejected, it will leave the corresponding index in an unusable state. Then we need to rebuilt the index to return to a usable state.
- No updates in table description will affect Oraload, as it automatically bind table description with data from external file; whereas SQL*Loader demands a modification in the control file accordingly.
- Oraload allows to utilize SQL functions like TO_DATE(), TO_CHAR() etc. to perform possible data transformations; whereas SQL*Loader does not allow it to be used in control files.
- Oraload reports any referential integrity constraints in the moment it occurs; whereas, SQL*Loader disable these constraints during the load, and enables and check it only once the load is complete.
- Oraload does not employ any constraints over DML statements; whereas in case of SQL*Loader, an exclusive lock is placed upon the table while data is being loaded. This prevents any other DML activity from occurring.
Conclusion
Thus use of Oraload reduces considerable unit testing time as it is does not deal with control files. It also provides user friendly error messages when used for UNIT Testing we can detect errors in early stage itself
Related Posts
- Oracle PL/SQL: Oracle System Tables
- SQL Server – Maximum Capacity Specifications Comparison
- SQL Server Cluster Resource Failed When Bringing Online
- Oracle PL/SQL: Transactions
- Error: SQL0284N The table was not created
Tags: bulk data load, oraload, SQL *Loader
