add

About Me

My photo
Oracle Apps - Techno Functional consultant

Thursday, July 19

IMPLEMENTING CONCURRENT PROGRAM FOR SQL LOADER


PROCEDURE:
1.Create a Custom Table
2.Create a Control File for SQL LOADER
3.Define an Executable
4.Define a Program
5.Add it to Request Group
6.Run a Request by a program name
7.Flat File Data

STEPS:
1.      Create a Custom Table : 
Create a emp table with the following columns ie Empno, name, sal, jdat in apps schema.

Syntax: 
create table emp (empno number, name varchar2(20), sal number, jdat date); 

2.      Create a Control File for SQL LOADER :
Create a Control file named as emp.ctl for SQL *LOADER Program.

LOAD DATA
INFILE '/data1/EBSR121/apps/apps_st/appl/po/12.0.0/bin/emp.csv'
BADFILE '/data1/EBSR121/apps/apps_st/appl/po/12.0.0/bin/emp.bad'
DISCARDFILE
'/data1/EBSR121/apps/apps_st/appl/po/12.0.0/bin/emp.dsc'
INSERT
INTO TABLE empl1
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(empno,
name,
sal,
jdat
)

3.      Define an Executable :  
 Login into Oracle Apps:
         System AdministratoràConcurrentàExecutable
        Create a new Executable named as ‘emp’ which is attached to the file  $PO_TOP/bin/emp.ctl already did in step 2.
          Executable File Name: emp

4.Define a Program:
System AdministratoràConcurrentàDefine
         Create a program as ‘Employee Details’ and attach the executable ‘emp’ to this program.
         Program Name: Employee Details
Executable: emp

5.Add a Request Group:
            Add the Program to Request Group in the name of Purchasing      Application. I have saved the file in Po_Top/bin so I use the PO as my  Application.

6.Run a Request:
    Go to Switch ResponsibilityàPurchasing Super User
        RequestsàRun
        Submit Single Request ok
       Program Name : Employee Details
       Submit
       View RequestsàFind with our Request ID shows completed as Normal   
       View Log gives the information about records transferred to tables .

7. Flat File Comma Separated(.CSV):
Data File: emp.csv
1,siva,10000,25/Jun/2011,23:55:00
2,Ram,50000,23/Aug/2011,11:08:09
3,sdf, 9999.55,22-JAN-9,00:00:02
4,sfs, 8888.00,12/12/01,
5,dgd, 0777.1, ,25:36:00

Run the program via concurrent manager it will load the flat file data into emp table in apps schema.



No comments: