Create SQL*Loader Concurrent Program in R12

SQL*Loader Usage:
                                   SQL LOADER is an Oracle utility used to load data into table given a data file which has the records that need to be loaded. SQL*Loader takes  data file, as well as a control file, to insert data into the table. When a Control file is executed, it can create Three (3) files called log file, bad file or reject file, discard file.


Log file tells you the state of the tables and indexes and the number of logical records already read from the input data file. This information can be used to resume the load where it left off.

 Bad file or reject file gives you the records that were rejected because of formatting errors or because they caused Oracle errors.

Discard file specifies the records that do not meet any of the loading criteria like when any of the WHEN clauses specified in the control file. These records differ from rejected records.

Structure of the data file:
1.Data file can be in fixed record format
2.Variable record format.

 Fixed Record Format would look like the below. In this case you give a specific position where the Control file can expect a data field:

INV100AMOUNT3000ADDRCHENNAI
INV102AMOUNT5000ADDRCHENNAI

Control file example :



LOAD DATA
INFILE '$FILE_NAME'
APPEND
INTO TABLE student
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
( STUDENT_ID                POSITION(1:6)   INTEGER,
  STUDENT_NAME        POSITION(7:15)  CHAR,
  ADDRESS                      POSITION(15:20) CHAR,
  MARKS                          POSITION(20:40) INTEGER
 )


Variable record format : Data file having the delimiter based values.

Create Table : student


CREATE TABLE student(student_id   NUMBER,
                     student_name VARCHAR2(50),
                     address      VARCHAR2(100),
                     marks        NUMBER
                     )

Below is the step by step process of registering shell script as a host program or concurrent program in Oracle Applications R12

Step 1 : Create the Executable name SQLLDR_TEST_PROGRAM with Executable file Name as sqlldr_demo_prog

Step 2 : Register the concurrent program Demo SQL*Loader Concurrent Program

Step 3 : Adding the parameter in the concurrent program

Step 4:  Add the concurrent program in to the request group & Add that request group into the Responsibility.

Create the control file sqlldr_demo_prog.ctl under $CUSTOM_TOP/bin directory

LOAD DATA

INFILE '$FILE_NAME'

APPEND

INTO TABLE student

FIELDS TERMINATED BY '|'

TRAILING NULLCOLS

( STUDENT_ID                    DECIMAL EXTERNAL,

  STUDENT_NAME            CHAR,

  ADDRESS                          CHAR,

  MARKS                              DECIMAL EXTERNAL

 )

$FILE_NAME --> We can hard code the file name with full directory path else we can pass the file name with file location as parameter. 

Note : We can put it OPTIONS (SKIP = 1)   --The first row in the data file is skipped without loading

Place the data file sqlldr_data_file.dat under under /u06/app/applmgr/dvoa044/oc/1.0.0/bin directory

1000|Chidambaram|Chennai|98

1001|Ram|Bangalore|67

1002|Kamal|Chennai|89

1003|Preethi|Hyderabad|90

Note : can use any directory for data file path.

Step 5: Submit the concurrent program Demo SQL*Loader Concurrent Program

from Added responsibility

Program should be completed in normal and out put parameter values are displayed in log file.

Log Information :

Control File:   /u06/app/applmgr/dvoa044/oc/1.0.0/bin/sqlldr_demo_prog.ctl

Data File:      /u06/app/applmgr/dvoa044/oc/1.0.0/bin/sqlldr_data_file.dat

  Bad File:     /u06/app/applmgr/dvoa044/out/o55553058.out

  Discard File: /u06/app/applmgr/dvoa044/out/o55553058.out 

 (Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Bind array:     64 rows, maximum of 65536 bytes

Continuation:    none specified

Path used:      Conventional

Table STUDENT, loaded from every logical record.

Insert option in effect for this table: APPEND

TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype

------------------------------ ---------- ----- ---- ---- ---------------------

STUDENT_ID                          FIRST     *   |       CHARACTER            

STUDENT_NAME                  NEXT     *   |       CHARACTER            

ADDRESS                                NEXT     *   |       CHARACTER            

MARKS                                   NEXT     *   |       CHARACTER            

Table STUDENT:

  4 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:                  65016 bytes(63 rows)

Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:         0

Total logical records read:              4

Total logical records rejected:        0

Total logical records discarded:      0

Run began on Mon May 13 01:51:57 2013

Run ended on Mon May 13 01:51:58 2013

Elapsed time was:     00:00:00.81

CPU time was:         00:00:00.02    

Data's are successfully inserted into back-end table student

sqlldr used in unix code

The below code can be used in unix shell script.

sqlldr ${FCP_LOGIN} control=$CUSTOM_PATH/bin/sqlldr_demo_prog.ctl log=sqlldr_data_file.log bad=sqlldr_data_file.bad  data=sqlldr_data_file.dat 

${FCP_LOGIN}  --> This is default credentials for APPS ,It provides Oracle Database User Name/Password@DataBase name, 

we can hardcode those values like USERID=apps/apps_passwd@apps_database

If the data file having invalid content then only bad file will be created otherwise bad file wont created.