Another Simple Way to Load CSV Data to Oracle 11i and R12

Introduction
Without heavy development or customization, a standard process of loading external data in Oracle 11i and R12 can be broken down into these steps:

1. DBA create a temp. table to hold the data
2. Business users provide an Excel spreadsheet holding the data
3. An operator converts the Excel file to CSV (comma-delimited) file or any text-based flat file.
4. IT staff installs a FTP/SFTP program for this operator and create a connection to the server which run the Concurrent Manager.
5. This operator transfer this file by FTP/SFTP to the server
6. Developer provides two concurrent programs and bundle them to a Request Set:
• A SQL*Loader to load this flat file
• A stored procedure to process the data
7. An operator runs Step (5) and then runs the Request Set periodically or as per request.

This is a very straight-forward process -- if all the people involved in this process are co-operative and the data definition is stable. However, in real-world situation the data definition always keep changing and the change is required to put to production in a very short amount of time.

In here I present a way to simplify the entire business flow by simplifying the steps (1), (4), (5), and (6). This process does not require a lot of customization and it is a pure PL/SQL solution (no Java, VB, Dephi, or other programming languages) so Oracle developers should be able to handle this.

Steps
The high-level operations of this new process are:

• (A) Put the data flat file as a LOB in the database by using Form attachment
• (B) Extract this LOB as a file located in a directory in the database server
• (C) Create an External Table which uses the file created in (B)
• (D) process the data in this External Table

STEP (A)

1. Choose any Form which has the attachment functionality (paper-clip icon is active in the Menu bar). This Form should be available to the operator who is responsible for uploading the data to the database. For example, if you want to upload price list information, you should use the Price List Form (My another blog describes how to turn on the attachment functionality for Price List Form).
2. Convert the Excel file data to CSV flat file (e.g. price_data.csv).
3. Create a bogus business object in the Form you decided in (1). For example, if you choose Price List Form, you could create a bogus Price List called "XX Price Upload -- DO NOT USE". Make this price list inactive so that it will not be selected in Order Management Forms.
4. Click on the paper-clip icon and add a file-based attachment. A new browser will be opened and the file created in (2) is uploaded. Then click "Yes" to confirm the upload.

STEP (B)

Create a concurrent program (details can be found in the Appendix) which can extract the LOB to a specific directory. In Oracle Applications 11i and R12 it should have a couple of Directory Objects which can be used by database user APPS. You can check it by

SELECT * FROM DBA_DIRECTORIES

The following is the PL/SQL segment which can be used to extract a BLOB stored in FND_LOB to a directory:


STEP (C)

1. Write a script to create External Table to read the flat file. Check this script in the development environment and make sure all the data column can be read.
2. Change the Directory Object Name to &DIRECTORY_NAME and data file name to &DATAFILE_NAME. Then store this script in a FND message (e.g. XX_TABLE_DEFINITION). These two tokens will be substituted during processing (by using the FND_MESSAGE API). Here is an example:

DROP TABLE XX_PRICE_LIST_EXT

/
CREATE TABLE XX_PRICE_LIST_EXT (
PRODUCT_CONTEXT        VARCHAR2(100),
PRODUCT_ATTRIBUTE      VARCHAR2(100),
PRODUCT_VALUE          VARCHAR2(100),
PRODUCT_DESCRIPTION    VARCHAR2(100),
UOM                    VARCHAR2(20),
PRIMARY_UOM            VARCHAR2(20),
LINE_TYPE              VARCHAR2(100),
PRICE_BREAK_TYPE       VARCHAR2(100),
APPLICATION_METHOD     VARCHAR2(100),
VALUE                  NUMBER,
DYNAMIC_FORMULA        VARCHAR2(100),
STATIC_FORMULA         VARCHAR2(100),
START_DATE             DATE,
END_DATE               DATE,
PRECEDENCE             NUMBER
) ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT directory &DIRECTORY_NAME
ACCESS parameters (
RECORDS DELIMITED BY NEWLINE
BADFILE 'XX_PRICE_LIST_EXT.bad'
LOGFILE 'XX_PRICE_LIST_EXT.log'
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
)
LOCATION ('&DATAFILE_NAME')
)REJECT LIMIT UNLIMITED
/

STEP (D)

Operator runs this concurrent program to extract the LOB and create the External Table. Further processing can be done by another concurrent program which read the data from this External Table.

APPENDIX


Executable

Concurrent Program
This concurrent program uses the executable XX_FILE_DATA_LOADING
Here is the parameters used for this concurrent program (Description is the valueset name):

Concurrent Program Parameters Screen

If "Table Definition" is blank, no external table script is re-created. It is for the situation that only new batch of data is needed to be processed and everything (including the data file name) is the same.

Valuesets

XX_ATTACHMENT_FORM_NAME

SELECT
    DATA_OBJECT_CODE,  -- VALUE column
    USER_FORM_NAME     -- ID column
    XX_ATTACHMENT_FORM_LINKAGE_V

XX_ATTACHMENT_FILE

SELECT
    tab3.FILE_NAME,   -- VALUE column
    tab3.FILE_ID         -- ID column
    FROM
    FND_ATTACHED_DOCUMENTS tab1, 
    FND_DOCUMENTS_TL tab2,
    FND_LOBS tab3
    WHERE
    tab1.ENTITY_NAME  = :$FLEX$.XX_ATTACHMENT_FORM_NAME
    AND tab1.DOCUMENT_ID  = tab2.DOCUMENT_ID
    AND tab2.MEDIA_ID = tab3.FILE_ID

XX_FND_MESSAGES_NAME

SELECT
    tab1.message_name   -- VALUE column
    FROM
    fnd_new_messages tab1,
    fnd_application tab2
    WHERE
    where tab1.language_code='US' AND
    tab1.application_id=tab2.application_id
    order by tab1.message_name

XX_DIRECTORY

SELECT
    DIRECTORY_NAME  -- VALUE column
    DIRECTORY_PATH  -- ID column
    FROM
    DBA_DIRECTORIES
    ORDER BY DIRECTORY_NAME

Database View

XX_ATTACHMENT_FORM_LINKAGE_V


Database Package

This package contains the following procedures:

• MAIN - The entry point of the concurrent program.
• SPLIT - A function which can convert the message to an array of string broken down by line
• EXTRACT_LOB_TO_FILE - This procedure extracts the LOB from FND_LOB to a directory.
• PARSE_SQL_FROM_FILE (not used in here) - This procedure read the file in chunk that it will execute the text fragment if it encounters a "/".
• PARSE_SQL_FROM_MESSAGE - Read the FND message; parse and execute this message as PL/SQL code.