EBS SQL Loader in Oracle E-Business Suite

SQL Loader can be registered as a concurrent program in EBS. When SQL Loader registered as concurrent program it does not provide flexibility and it can take advantages of features provided by concurrent manager/ concurrent program platform of EBS. For example, it can take one parameter for data file along with directory structure. An alternate approach will be to use a shell or host program to use as anchor program. This approach provides lot of flexibility to set parameters easily for SQL Loader. Any parameter, which can be set from command line can be set using this approach for a specific program without impacting environment.

Example OPTIONS (ERRORS=50) 

Advantages for using this approach

1. It is very easy to populate EBS environment variables to the table through this approach e.g. Request ID, ORG ID and any other variable. These variables can be updated to the table after successfully completion of SQL Loader utility. Once SQL Loader completed, execute update statement on the table from the host program.

• A biggest drawback of using SQL Loader as concurrent program is not to have access to bad or rejected data files. Developer or user depends on data base administrator to provide bad file or discarded data file form operating system. By using this approach host program can easily display SQL loader log file, bad file and rejected files in concurrent program log. Host program command like echo can be used to print log file and bad file in concurrent program logs. This will provide easy access to the rejected data through concurrent program log file.

• Parameters to SQL Loader can be specified for specific concurrent program, without impacting the environment (e.g. max error number can be specified for each program)

How to load data faster using SQL Loader

1. One easy way to upload data faster is to use direct load option. This option will directly write data to data blocks. It will skip conventional method of generating insert statement and executing them. Downside of this approach will be not maintaining indexes on table. In my experience if data volume is very high then using direct method is advantageous. During the load time we can skip index maintenance and after completing data load rebuild the indexes. Direct load plus rebuilding indexes still be much shorter than using conventional method of data load. In my experience it has significantly improved the data load speed.

Example OPTIONS (DIRECT=TRUE, ERRORS=50, SKIP_INDEX_MAINTENANCE=true)

• Another way to speed up the data load is to use unrecoverable, but need to be aware that redo logs are not generated during this data load. This needs to evaluated if it is suitable for data load operation. If data load is very high and back up can be taken after data load maybe it is a viable option to consider.

Example   UNRECOVERABLE
          LOAD DATA 
          INFILE 'sample.dat'

• Another effective method to speed up data load is to generate control file dynamically for the specific load and provide constant string in the SQL Loader control file as much as possible. This will eliminate the need of reading data file and post updates to table as suggested above. This can be done in host program or by any other means (e.g. UTL File if anchoring program is PL/SQL). Once Control file is created it can be used in host program.

Fixed field data files load much faster than character separated data files. This may or may not work, depends on the system which is generating data files.