Home » Applications » Oracle Fusion Apps & E-Business Suite » UNIX Script
UNIX Script [message #240754] Fri, 25 May 2007 11:57 Go to next message
Krishna_dev
Messages: 32
Registered: May 2007
Member
Hello...
I need to write a UNIX Shell Script to load data with Control file into staging table. After that the UNIX Script should call a pl/sql validation program that will validate data in staging table and then insert into main interface table. Can someone give me an idea of how to do this and maybe some online resources I can use to research this problem. Thanks
Re: UNIX Script [message #240765 is a reply to message #240754] Fri, 25 May 2007 12:31 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member

Following is the code where in you have parameter for File Input and Data Set of data you are loading for you it will be different so you can change accordingly.

this shell script is for data loading only......
i am creating Control file dynamically using shell script

FCP_LOGIN=`echo $*|awk '{print $3}'`
DATA_FILE=`echo $*|awk '{print $9}'`
DATA_SET_NAME=`echo $*|awk '{print $10}'`

echo $FCP_LOGIN
FCP_LOGIN=${FCP_LOGIN#FCP_LOGIN=$quotes}
FCP_USERID=${FCP_USERID#FCP_USERID=$quotes}
#FCP_LOGIN=${FCP_LOGIN%$quotes}
FCP_LOGIN=`echo $FCP_LOGIN|sed 's/"//g'`
echo $FCP_LOGIN

DATA_FILE=`echo $DATA_FILE|sed 's/"//g'`
DATA_SET_NAME=`echo $DATA_SET_NAME|sed 's/"//g'`



rm -f $SMC_BOM_TOP/bin/SMCBOMFBDLOAD.ctl
rm -f $SMC_BOM_TOP/bin/SMCBOMFBDLOAD.log


echo "LOAD DATA
APPEND
INTO TABLE SMCBOM.SMCBOM_FB_DOLLAR_VALUES
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
( DATA_SET_NAME CONSTANT \""$DATA_SET_NAME\"" ,
  DEPARTMENT,
  REPORT_COMPONENT, 
  QUARTER,
  FISCAL_YEAR,
  DOLLAR_VALUE,
  Q_START_DATE DATE,
  Q_END_DATE DATE, 
  CREATED_BY CONSTANT  \""-1\"" ,
  CREATION_DATE SYSDATE ,
  LAST_UPDATED_BY CONSTANT \""-1\"" ,
  LAST_UPDATE_DATE SYSDATE 
  )" >> $SMC_BOM_TOP/bin/SMCBOMFBDLOAD.ctl


$ORACLE_HOME/bin/sqlload userid=$FCP_LOGIN control=$SMC_BOM_TOP/bin/SMCBOMFBDLOAD.ctl data=$DATA_FILE LOG=$SMC_BOM_TOP/bin/SMCBOMFBDLOAD.log

after this point you can use CONSUB to call a Validation program
Assumption:
You have developed a different concurrent program which validates your data



Regards
Prashant Pathak
Re: UNIX Script [message #243282 is a reply to message #240765] Wed, 06 June 2007 14:20 Go to previous messageGo to next message
Krishna_dev
Messages: 32
Registered: May 2007
Member
Hello...

Using the method you have illustrated, how do I load multiple files with the same extension at the same time into the table. all files will have .dat extension.
Re: UNIX Script [message #243300 is a reply to message #243282] Wed, 06 June 2007 16:13 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
lets suppose you files name are like
YOUR_TEST_DATA_FILE_1
YOUR_TEST_DATA_FILE_2

you have make some code change for this to handle and give parameter as YOUR_TEST_DATA_FILE*.

try out your option in Unix shell scripting...it is like looping in UNIX.

Regards
Prashant Pathak
Re: UNIX Script [message #243528 is a reply to message #243300] Thu, 07 June 2007 11:12 Go to previous messageGo to next message
Krishna_dev
Messages: 32
Registered: May 2007
Member
I guess my next question is how do I define the parameters DATA_FILE & DATA_SET_NAME. DATA_FILE would be my input files so would I enter *.dat for these or just *.* on the Apps side.
Next for my DATA_SET_NAME, would this be the directory path in which the data files are stored. 'u01/data'
One other issue would be setting permissions for the control file once it is created. Please advise.
Re: UNIX Script [message #243530 is a reply to message #243528] Thu, 07 June 2007 11:25 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi ,

DATA_SET_NAME was related to my program.
you dont have to have that. you can remove the data set if you want to.

Regards
Prashant Pathak
Re: UNIX Script [message #243551 is a reply to message #240754] Thu, 07 June 2007 13:19 Go to previous message
Krishna_dev
Messages: 32
Registered: May 2007
Member
Hello...

I am not to sure what I am doing wrong but I still have not got it to work. I have defined 2 parameters in Oracle Apps.
1.DATA_FILE - Have given this constant value of *
2.DATA_SET_NAME - Have given data directory of files u01/data

I am thinking the logic is these parameters will be passed to shell script and this will execute and load all the data files in u01/data. Please look at the script I have attached and advise where I am making a mistake. Would be very grateful.
Previous Topic: Disable Export Menu option in Oracle Applications 11.5.9
Next Topic: How to retain all the responsibilities in Dev, even after the refresh of Apps from Prod?
Goto Forum:
  


Current Time: Sat Dec 10 12:32:18 CST 2016

Total time taken to generate the page: 0.04193 seconds