Friday 16 March 2012

Oracle apps(Loading data to Stagging table...by using sql loader)

i am explainning all steps with an example as
1)create csv file from excel file by save as with proper format u required(remove all headings  from the excel so that the headings automatically removed in csv )
eg data
85,1-Jan-12,6-Jan-12,7-Jan-12,14-Jan-12,15-Jan-12,22-Jan-12,23-Jan-12,30-Jan-12,31-Jan-12
85,1-Feb-12,7-Feb-12,8-Feb-12,15-Feb-12,16-Feb-12,23-Feb-12,24-Feb-12,,
85,1-Mar-12,2-Mar-12,3-Mar-12,10-Mar-12,11-Mar-12,18-Mar-12,19-Mar-12,26-Mar-12,27-Mar-12
85,1-Apr-12,3-Apr-12,4-Apr-12,11-Apr-12,12-Apr-12,19-Apr-12,20-Apr-12,27-Apr-12,28-Apr-12
85,1-May-12,5-May-12,6-May-12,13-May-12,14-May-12,21-May-12,22-May-12,29-May-12,30-May-12
85,1-Jun-12,6-Jun-12,7-Jun-12,14-Jun-12,15-Jun-12,22-Jun-12,23-Jun-12,30-Jun-12,
85,1-Jul-12,1-Jul-12,8-Jul-12,9-Jul-12,16-Jul-12,17-Jul-12,24-Jul-12,25-Jul-12,
85,1-Aug-12,1-Aug-12,2-Aug-12,9-Aug-12,10-Aug-12,17-Aug-12,18-Aug-12,25-Aug-12,26-Aug-12
85,1-Sep-12,2-Sep-12,3-Sep-12,10-Sep-12,11-Sep-12,18-Sep-12,19-Sep-12,26-Sep-12,27-Sep-12
85,1-Oct-12,4-Oct-12,5-Oct-12,12-Oct-12,13-Oct-12,20-Oct-12,21-Oct-12,28-Oct-12,29-Oct-12
85,1-Nov-12,5-Nov-12,6-Nov-12,13-Nov-12,14-Nov-12,21-Nov-12,22-Nov-12,28-Nov-12,29-Nov-12
85,1-Dec-12,6-Dec-12,7-Dec-12,14-Dec-12,15-Dec-12,22-Dec-12,,29-Dec-12,30-Dec-12

2)create stagging table as given below
CREATE TABLE XX_CUST_STG_TAB_V2
(
  EMPLOYEE_NUMBER       VARCHAR2(30 BYTE),
  MONTHYEAR VARCHAR2(200), 
  SEGMENT1              VARCHAR2(200 BYTE),
  SEGMENT2              VARCHAR2(200 BYTE),
  SEGMENT3              VARCHAR2(200 BYTE),
  SEGMENT4              VARCHAR2(200 BYTE),
  SEGMENT5              VARCHAR2(200 BYTE),
  SEGMENT6              VARCHAR2(200 BYTE),
  SEGMENT7              VARCHAR2(200 BYTE),
  SEGMENT8              VARCHAR2(200 BYTE),
  SEGMENT9              VARCHAR2(200 BYTE),
  SEGMENT10             VARCHAR2(200 BYTE),
  SEGMENT11             VARCHAR2(200 BYTE),
  SEGMENT12             VARCHAR2(200 BYTE),
  ATTRIBUTE1            VARCHAR2(200 BYTE),
  ATTRIBUTE2            VARCHAR2(200 BYTE),
  ATTRIBUTE3            VARCHAR2(200 BYTE),
 CREATED_BY             NUMBER(15),
 LAST_UPDATE_LOGIN      NUMBER(15),
 LAST_UPDATED_BY        NUMBER(15),
 CREATION_DATE          DATE,
 LAST_UPDATE_DATE       DATE
);

3)create the CTL file to load the data of csv to the stagging table
as
LOAD DATA
INFILE '/u01/DEv/apps/apps_st/appl/xxcustomtop/12.0.0/bin/XX_SHIFT_EMP.csv'
APPEND INTO TABLE XX_CUST_STG_TAB_V2
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  EMPLOYEE_NUMBER,
  MONTHYEAR, 
  SEGMENT1 ,
  SEGMENT2 ,
  SEGMENT3 ,
  SEGMENT4 ,
  SEGMENT5 ,
  SEGMENT6 ,
  SEGMENT7 ,
  SEGMENT8 ,
  SEGMENT9 ,
  SEGMENT10,
  SEGMENT11,
  SEGMENT12,
  ATTRIBUTE1 "IN",----this is keep the track of all records i have inserted it as IN
  ATTRIBUTE2 "XX_SHIFT_STG_SEQ.NEXTVAL", ---this is the sequence value ..
  CREATED_BY        "FND_GLOBAL.USER_ID",
  LAST_UPDATE_LOGIN "FND_GLOBAL.LOGIN_ID",
  LAST_UPDATED_BY   "FND_GLOBAL.USER_ID",
  CREATION_DATE     SYSDATE,
  LAST_UPDATE_DATE  SYSDATE
 )



4)place or copy the  Csv and CTL files in the bin of the custom top
as example
/u01/DEv/apps/apps_st/appl/xxcustomtop/12.0.0/bin/XX_SHIFT_EMP.csv'
(always give the same name to the csv ,ctl ,concurrent program name )

5)then create the concurrent program to load the data to stagging table as select the type as sql loader
and always give the custom application top while creating the concurrent program.execution name  should be same as the ctl and csv..

6)and for running the concurrent program attach it to the proper request group and to the responsibility through which you have to run the cocurrent program .

and 7)query the table to see all data of csv has been loaded in the stagging table or not ..




No comments:

Post a Comment