,

Interfaces in Oracle Application: An Introduction

What are Interfaces?

  • Interfaces are used in Oracle Applications to integrate external systems and Data Conversion.
  • The interfaces are mainly used to either transfer data from Oracle Applications to a flat file or data from legacy system to Oracle Applications.
  • Used extensively at the time of Data Conversion from legacy/ old systems to a fresh implementation of Oracle Applications.
  • Used also at regular intervals when data transfer is from other live systems if the systems are not defined in Oracle Applications implementation.
  • Oracle provides flexible and flexible tools in the form of Interface programs to import the master and transactional data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications.

Types of Interfaces

There are two major types of Interfaces:

  • Inbound Interface : These interfaces are used to transfer data from external systems to Oracle Applications.
  • Outbound Interface :  These interfaces are used to transfer data from Oracle Applications to external systems.

Two other distinctions of Interfaces:

  • Open Interface: If the interface logic is provided by Oracle Applications, it is called an Open Interface.
  • Custom Interface: If the interface logic needs to be developed by the implementation team, it is called a Custom Interface.

Interface Components

Open Interface Logic

  • First the data from the source application is loaded into a database table (called Interface table).
  • Then the provided validation program logic validates the records whether they are correct or not .
  • If the validation fails, the errors are transferred into another table (called Error Table).
  • If the validation succeeds, the correct records are transferred through a process into the destination application table.

Components of an Interface

a] Source Application:
You obtain data from a source application to pass on to a destination application for further processing and/or storage.
b] Source Data Issues:
Type of file, Size, Frequency of upload, Record Length (Variable or fixed), Delimiter, Datatype for each field, Any unwanted data, Naming convention and uniqueness of file, Location of the file, Access on the file.
c] Destination Application:
You send data to a destination application so that the application can perform further processing and/or storage.
d] Interface Table:
For inbound interfaces, the interface table is the intermediary table where the data from your source application temporarily resides until it is validated and processed into the destination application.
e] Identifier columns:
Uniquely identify rows in the interface table provide foreign key reference to both the source and destination applications.
f] Control Columns:

  • Control columns track the status of each row in the interface table, as it is inserted, validated, rejected, processed, and ultimately deleted.
  • WHO columns are also control columns.

g] Data Columns:

  • Stores the data that is being converted.
  • Required columns store the minimum information needed by the destination application to successfully process the interface row.

h] Derived Columns:
Derived columns are created by the destination application from information in the required columns.
i] Optional Columns:
Optional columns are not necessarily required by the destination application, but can be used by the destination application for additional value-added functionality beyond the basics.
j] Error Table:

  • For inbound interfaces, the errors table stores all errors found by the validation and processing functions.
  • In some cases, the errors table is a child of the interface table. This allows each row in the interface table to have many errors, so that you can easily manage multiple errors at once.
  • In other cases, the errors are stored in a column within the interface table, which requires you to fix each error independently.

Developing an Interface

1] Identification:
Find out if there exists an Open Interface to carry out the functionality.
2] Creation of Pre-Interface table ( staging Table):
A table in the format of the data file which can be pruned to load as clean a data into the Interface table.
3] Load data into Pre-Interface table:
SQL*LOADER can be used to load the flat file into the pre-interface table.
4] Validate data in the Pre-Interface table:
Basic validation of the data loaded into the Pre-Interface table can be carried out like:

  • For checking NULL values in required columns
  • Checking for Foreign Key and Quick Code values.
  • Duplication Validation
  • Business Rule validation

5] Mapping the values:
Generated fields in Oracle Applications can be mapped in this step to either default values or sequences.
6] Load data into Interface table:

  • Once the data is as clean as you can get it, the data can be inserted into the Interface table.
  • At such a time, certain columns, which are necessary in Applications but not found in legacy system, need to be populated accordingly like WHO columns.

7] Run the interface program
8] Check for Errors
9] Report on the Interface

/by
, , ,

Invoking SQL*Loader

Invoking SQL*Loader

On Unix systems, the command used to invoke SQL*Loader is sqlldr. On Windows systems running Oracle8i, release 8.1 or higher, the command is also sqlldr.
The command to execute SQL*Loader is as follows:
sqlldr USERID = <username>/<password> CONTROL = <control file name> LOG = <log file name>….
Issuing the sqlldr command by itself results in a list of valid command-line parameters being displayed. Command-line parameters are usually keyword/value pairs, and may be any combination of the following:
USERID = {username[/password] [@net_service_name]|/} Specifies the username and password to use when connecting to the database.
CONTROL = control_ file_name  Specifies the name, which may include the path, of the control file. The default extension is .ctl.
LOG = path_ file_name Specifies the name of the log file to generate for a load session. You may include a path as well. By default, the log file takes on the name of the control file, but with a .log extension, and is written to the same directory as the control file.
BAD = path_ file_name Specifies the name of the bad file. You may include a path as part of the name. By default, the bad file takes the name of the control file, but with a .bad extension, and is written to the same directory as the control file.

DATA = path_ file_name Specifies the name of the file containing the data to load. You may include a path as part of the name. By default, the name of the control file is used, but with the .dat extension.
DISCARD = path_ file_name Specifies the name of the discard file. You may include a path as part of the name. By default, the discard file takes the name of the control file, but it has a .dis extension.
Other Parameters are:
DISCARDMAX=logical_record_count
SKIP=logical_record_count
SKIP_INDEX_MAINTENANCE={TRUE | FALSE}
SKIP_UNUSABLE_INDEXES={TRUE | FALSE}
LOAD=logical_record_count
ERRORS=insert_error_count
ROWS=rows_in_bind_array
BINDSIZE=bytes_in_bind_array
SILENT=[(]keyword[,keyword...][)]
DIRECT={TRUE | FALSE}
PARFILE=path_file_name
PARALLEL={TRUE | FALSE}
READSIZE=bytes_in_read_buffer
FILE=database_datafile_name
/by
, , ,

SQL*Loader Environment

SQL*Loader Environment

SQL*Loader takes as input a control file, which controls the behavior of SQL*Loader, and one or more datafiles. Output of the SQL*Loader is an Oracle database (where the data is loaded), a log file, a bad file, and potentially a discard file.

Execution Steps:

Step1    Executes from the command prompt or parameter file.
Step2    Reads the control file.
Step3    Reads the data from the control file or from one or more datafiles.
Step4    Loads the data in the database and logs the information in the log file.
Step5    Places the rejected records in the bad file.
Step6    Places the discarded records in the discard file.

1] The parameter file:

The command line information can be saved in a parameter file. This parameter file is executed from the command prompt. A parameter file has a .par extension. Following is a sample parameter file.
Steps to create a parameter file:
  • Open a text editor.
  • Type in each parameter with its corresponding value as shown in the parameter file.
  • Save the file with the .par extension.
How do you execute the parameter file?

At the command prompt type sqlldr PARFILE = <parfile name>.

2] The Control File:

  • The control file is a text file written in a language that SQL*Loader understands.
  • The control file describes the task that the SQL*Loader is to carry out. The control file tells SQL*Loader where to find the data, how to parse and interpret the data, where to insert the data, and more.
  • It also contains the names and locations of the bad file and the discard file.
  • Some of above information (such as name and location of the input file) can also be passed to SQL*Loader as command-line parameters.
  • It’s also possible for the control file to contain the actual data to be loaded. This is sometimes done when small amounts of data need to be distributed to many sites, because it reduces (to just one file) the number of files that need to be passed around.
 A sample control file is given below:
In general, the control file has three main sections, in the following order:
A] Session-wide information:
The session-wide information contains the names of the input/output files for the data load session. Apart from this, other SQL*Loader parameters can also be listed in this section.
  • The LOAD DATA statement is required at the beginning of the control file.
  • INFILE * specifies that the data is found in the control file and not in an external data file.
  • BADFILE ‘example1.bad’ indicates that all erroneous records must be stored in the file example1.bad.
  • DISCARDFILE ‘example1.dsc’ indicates that all discarded records must be stored in the file example1.dsc.
B] Table and Field_List Information:
The INTO TABLE statement specifies the table into which data should be loaded. In this case it is the dept table. By default, SQL*Loader requires the table to be empty before it inserts any records.
FIELDS TERMINATED BY specifies that the data is terminated by commas, but can also be enclosed by quotation marks. Data types for all fields default to CHAR.
The names of columns to load are enclosed in parentheses. Because no data type or length is specified, the default is type CHAR with a maximum length of 255.
C] Input Data:
BEGINDATA specifies the beginning of the data. The data to be loaded is present below the BEGINDATA command.

3] Input Datafiles:

  • The data to be loaded is contained in one or more datafiles if it is not contained in the control file.
  • The data in the datafile can be in the fixed length format, variable length format, or in the stream record format.
A] Fixed Length Format:
A file is in the fixed record format when all the records in the datafile have the same byte length. This format is not flexible but offers very good performance.then the syntax for the INFILE command is – INFILE student.dat “fix 15″
The syntax for letting SQL*Loader know that the data is in the fixed length format is:
INFILE datafile_name “fix n”
Here INFILE datafile_name refers to the file that contains the data to be loaded. “fix n” implies that each record in the datafile has a fixed byte length of n.
For example if the name of the following datafile is student.dat and the byte length of a record is 15 bytes
0001, —–Rina, 0002, —-Harry, 0003,—–Sudha
B] Variable Length Format:
A file is in the variable record format when the length of each record varies. The length of each record is included at the beginning of the record in the datafile. This format provides some added flexibility over the fixed record format and a performance advantage over the stream record format.
For example, you can specify a datafile that is to be interpreted as being in variable record format as follows:
INFILE “datafile_name” “var n”
Here n specifies the number of bytes in the record length field. If n is not specified, SQL*Loader assumes a length of 5 bytes. If n is specified larger than 40 it results in an error. The following datafile is random.dat and the value for n is 3.
009hello,cd,010world,im,
012my,name is,
SQL*Loader reads the first 3 bytes to gather the length of the record. Here the first record is 9 bytes long. After SQL*Loader has read 9 bytes, it reads the next 3 bytes to find the size of this record which is 10 bytes long. It reads the next 10 bytes of the record and then finds the third record is 12 bytes long and so on.
C] Stream Record Format:
A file is in the stream record format when the records are not specified by size; instead SQL*Loader forms records by scanning for the record terminator. Stream record format is the most flexible format, but there can be a negative effect on performance. The syntax for specifying the stream record format is as follows:
INFILE datafile_name [“str terminator_string”]
The terminator_string can be a ‘char_string’  which is a string of characters enclosed in single or double quotation marks or a ‘hex_string’ which is a byte string in hexadecimal format.

4] The Log File:

The log file is a record of SQL*Loader’s activities during a load session. It contains information such as the following:
  • The names of the control file, log file, bad file, discard file, and data file
  • The values of several command-line parameters
  • A detailed breakdown of the fields and datatypes in the data file that was loaded
  • Error messages for records that cause errors
  • Messages indicating when records have been discarded
  • A summary of the load that includes the number of logical records read from the data file, the number of rows rejected because of errors, the number of rows discarded because of selection criteria, and the elapsed time of the load
Always review the log file after a load to be sure that no errors occurred, or at least that no unexpected errors occurred. This type of information is written to the log file, but is not displayed on the terminal screen.

5] The Bad File:

Whenever you insert data into a database, you run the risk of that insert failing because of some types of error. Integrity constraint violations undoubtedly represent the most common type of error. However, other problems, such as the lack of free space in a tablespace, can also cause insert operations to fail. Whenever SQL*Loader encounters a database error while trying to load a record, it writes that record to a file known as the bad file.
  • If one or more records are rejected, the bad file is created and the rejected records are logged.
  • If no records are rejected, then the bad file is not created.

6] The Discard File:

While SQL*Loader is being executed it creates a discard file for records that do not meet any of the loading criteria. The records contained in this file are called discarded records. Discarded records do not satisfy any of the WHEN clauses specified in the control file. These records differ from rejected records. Discarded records do not necessarily have any bad data. A discarded record is never inserted into the Oracle table.
A discard file is created according to the following rules:
  • You have specified a discard filename and one or more records fail to satisfy all of the WHEN clauses specified in the control file. (If the discard file is created, it overwrites any existing file with the same name, so be sure that you do not overwrite any files that you want to retain.)
  • If no records are discarded, then a discard file is not created.
/by
, ,

Registering SQL*Loader as a Concurrent Program

Registering SQL*Loader as a Concurrent Program

The following steps will describe the process to register a SQL*Loader program as a Concurrent Program in Oracle Apps.
Step 1]
Create the SQL*Loader Control and Data file and place them in Server(ex: $CUSTOM_TOP/bin). Create or check the interface table structures in the backend.

Control file: test.ctl

Data file: test.dat
CREATE TABLE testdept(
deptno NUMBER(2) NOT NULL,
dname VARCHAR2(14),
loc VARCHAR2(13));


Step 2]
Go to Application Developer > Concurrent > Executables. Define a Concurrent Program Executable. Choose the Execution Method as SQL*Loader and give the Execution File Name as the name of the SQL*Loader control file. Save your work.
Step 3]
Go to Application Developer > Concurrent > Program. Define the Concurrent Program. Attach the executable defined above.
Step 4]
Go to parameters of the concurrent program. Create a parameter to take the server path of the data file. You can also place the default value.
Step 5]
Attach the Concurrent program to a Responsibility through a Request Group.
Step 6]
Go to that Responsibility and Run the Concurrent Program. If successful check the output file that have all data uploading information.
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table TESTDEPT, loaded from every logical record.
Insert option in effect for this table: INSERT
   Column Name                  Position   Len  Term Encl Datatype
—————————— ———- —– —- —- ———————
DEPTNO                               FIRST     *   ,  O(“) CHARACTER     
DNAME                                NEXT     *   ,  O(“) CHARACTER     
LOC                                     NEXT     *   ,  O(“) CHARACTER           
Table TESTDEPT:
  7 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
Space allocated for bind array:49536 bytes(64 rows)
Read   buffer bytes: 1048576
Total logical records skipped:           0
Total logical records read:               7
Total logical records rejected:          0
Total logical records discarded:        0
Run began on Thu Aug 12 09:41:55 2010
Run ended on Thu Aug 12 09:41:56 2010
Elapsed time was:      00:00:00.11
CPU time was:           00:00:00.01
Step 7]
Check in the backend whether the tables got updated or not.
The Bad and Discard files will be created in /conc/out file of the server.
/by
, , , ,

SQL*Loader Basics

SQL*Loader Basics

SQL*Loader is an Oracle-supplied utility that allows you to load data from a flat file into one or more database tables. It has a powerful data parsing engine that supports data present in any format in the data files. It can load data from multiple datafiles during the same load session as well as can load data into multiple tables during the same load session. SQL*Loader can manipulate the data before loading it, using SQL functions.

SQL*Loader’s Capabilities:

SQL*Loader can read from multiple input files in a single load session.
SQL*Loader can handle files with fixed-length records, variable-length records, and stream-oriented data.
SQL*Loader supports a number of different datatypes, including text, numeric, zoned decimal, packed decimal, and various machine-specific binary types.
Not only can SQL*Loader read from multiple input files, but it can load that data into several different database tables, all in the same load session.
SQL*Loader allows you to use Oracle’s built-in SQL functions to manipulate the data being read from the input file.
SQL*Loader includes functionality for dealing with whitespace, delimiters, and null data.
In addition to standard relational tables, SQL*Loader can load data into object tables, varying arrays (VARRAYs), and nested tables.
SQL*Loader can load data into large object (LOB) columns.
SQL*Loader can handle character set translation between the input data file and the database.

/by