ORACLE APPS TECHNICAL: shell scripting

Host Concurrent Program

SAMPLE HOST FILE OR SHELL SCRIPT FOR SQL LOADER:

i have written the shell script it might help someone.
step1 open note pad and write the below script and save it with extension .prog ie XX_JOB_HOST_PROG.prog and deploy it in oracle server.

DATAFILE=$5
DIR_NAME=$6
LOG_BAD_PATH=$7
CTRLFILE=$8
LOG_BAD_PATH_LOG=$9
LOG_BAD_PATH_BAD=${10}
LOG_BAD_PATH_DSC=${11}

APPLTOP=$DIR_NAME/bin
FILE_NAME=${LOG_BAD_PATH}/${DATAFILE}.csv
LOGFILE=${LOG_BAD_PATH_LOG}/${CTRLFILE}.log
BADFILE=${LOG_BAD_PATH_BAD}/${DATAFILE}.bad
DISCARDFILE=${LOG_BAD_PATH_DSC}/${DATAFILE}.dsc

echo "****************************************************************************"
echo "Parameters for SQL Loader Process "
echo " "
echo "Control File:" $APPLTOP/$CTRLFILE
echo "Data File:" $FILE_NAME
echo "Log File:" $LOGFILE
echo "Bad File:" $BADFILE
echo "Discard File:" $DISCARDFILE
echo "****************************************************************************"

echo "Executing the SQL Loader Process.."

sqlldr userid=apps/apps@ebs12 control=${APPLTOP}/${CTRLFILE}.ctl data=${FILE_NAME} log=${LOGFILE} bad=${BADFILE} discard=${DISCARDFILE} errors=100000

echo "****************************************************************************"

echo "Process Complete - Exiting"
exit 0

step 2. create a soft link in unix using "ln -s $FND_TOP/bin/fndcpesr XX_JOB_HOST_PROG" (without any extension)

step 3. deploy the .ctl and the data file into oracle server.

step 4 create conc. executable as host execution method. and proceed to run the request(hope u all know how to do that)

note: while creating conc.programme please pass all the parameters.

Implementing a Host Concurrent Request

Host concurrent programs are best for performing operating system tasks such as

copying files.

Setting up a Host Concurrent program in the UNIX operating system is relatively

painless. First you must write your shell script and save the script under the

$PRODUCT_TOP/bin directory corresponding to the application that the executable is

registered under. When the host concurrent program is invoked five standard

parameters are passed in $0 thru $4. The five standard parameters do not need to be

defined on the Concurrent Program Parameters Form.

Variable Contents

$0 Name of the Concurrent Program

$1 Oracle User ID/PASSWORD

$2 User ID (numeric representation)

$3 User Name that submitted the request (character representation)

$4 Request ID for the request

The parameters in $5 thru $9 are user defined and are passed in the order defined on

the Concurrent Program Parameters form. The total number of parameters passed is

shell (ksh, csh, bourne, etc) dependent. To be able to reference parameters 6 thru N

where parameter 6 would be the equivalent of $10 you must use the shift command to

place the value of the 10 th argument into $9 variable (see example script APPENDIX

3).

If you specify “encrypt” in the Execution Options filed of the Concurrent Programs

window.

This signals the concurrent manager to pass orauser\pwd in the environment variable

fcp_login. The password of the argument $1 will be populated with asterisks (SEE UNIX

Installation Guide B-12).After you have created your host concurrent program make sure

that you save the program with an extension of “YOUR_HOST.prog”. The execution file

specified when the executable is defined should be equal to “YOUR_HOST” excluding

the .prog extension. After you have saved your executable it is important to create a

symbolic link from “YOUR_HOST” to fndcpesr in the same directory that your host

concurrent program is saved. The command to create the symbolic link is:

“ln -s fndcpesr YOUR_HOST”

If you fail to create the symbolic link the parameters passed to your program will not be

passed in separate variables rather they will be passed as one long string in the $1

parameter. It is important to remember to grant executable permissions on the script file

that is your host concurrent program. The command to do this is:

chmod 755 YOUR_HOST.prog

if you fail to do this your concurrent program will fail and exit with status 1.

If you wish to direct output to the request log or output log for your host concurrent

program it is the responsibility of the programmer to construct the correct file name

based on the user name ($3) and request id ($4) parameters. The request log is of the

format “l######.req” where ###### = request id ($4). The output files fomat is

dependent upon the environment variable $APPCPNAM. If $APPCPNAM is set to

USER.REQUID then the naming convention for output files will be USERNAME.######

where ###### = request id ($4) and USERNAME is the user that submitted the request

($3). If APPCPNAM is not set the default 8.3 naming convention will be used which is

o######.out where ###### = request id ($4).

Host Concurrent Program

Below are the steps to register host concurrent program in oracle apps

1) Define Concurrent Program with Executable Type = Host and Execution File Name = Name of .prog file without extension

2) Copy your .prog file (Shell Script) to Custom Top Bin directory (Eg: $XXCUST_TOP/bin)

3) Create symbollic link by executing below commands on Unix Servercd $XXCUST_TOP/bin
ln -s $FND_TOP/bin/fndcpesr sample_file
WHERE sample_file = Name of your Host program script

Dear Friends, i want to use host command in a concurrent request to load data in a table from a flat file .

for that i wrote one flat file(test_data_host.txt) , one control file(test_data_host.ctl) , one script file(test_data_host_prog.prog)

Finally i made one concurrent program BPIL_TEST_DATA_HOST and saved it as ‘ HOST ‘.

i added to my test_data_host_prog.prog file four compulsory parameters ,but during registration in sysadmin -> concurrent-> program -> define ,In parameters window, i had left all blank.(previously, same was running when i made one link from ‘$FND_TOP/bin/fndcpesr’ file using command: *ln -s $FND_TOP/bin/fndcpesr test_data_host_prog * through telnet.)

During running of the concurrent program it is giving error: The executable file /dev02/CPS/apps/apps_st/appl/ja/12.0.0/bin/test_data_host_prog for this concurrent program can not be executed.

my files data is attached here with:

1) test_data_host.txt file:

Tamojit,1,history
Vishnu,2,maths
Krishna,3,HRMS

*******************

2) test_data_host.ctl file:

LOAD DATA
APPEND
INTO TABLE BPIL_TEST_CONTROL_FILE
FIELDS TERMINATED BY “,”
OPTIONALLY ENCLOSED BY’”‘
TRAILING NULLCOLS
( name1,
class1,
subject,
record_status CONSTANT ‘NEW’)

*******************************

3) test_data_host_prog.prog file:

# Parameters passed into program

ORA_USER_PASS=$1
USERID=$2
USERNAME=$3
REQUESTID=$4

#LOGON_STRING=’apps/appscps@cps’
#FILENAME=$5

sqlload userid=apps/appscps@cps control=/usr/tmp/test_data_host.ctl data=/usr/tmp/test_data_host.txt log=/usr/tmp/test_data_host_log.log bad=/usr/tmp/test_data_host_bad.bad ERRORS=100000 silent=FEEDBACK <<!
!

RC=$?
echo “The sql loader exit code for loading Header table is :”$RC

if [$RC -eq 0 -o $RC -eq 3 |http://forums.oracle.com/forums/]
then
echo ‘Loading of file table successful.’
else
echo ‘Error: Loading of file table has errors. Sqlload return code is ‘$RC
exit 1
fi

case “$RC” in
0) echo “SQL*Loader execution successful” ;;
1) echo “SQL*Loader execution exited with failure, see logfile” ;;
2) echo “SQL*Loader execution exited with warning, see logfile” ;;
3) echo “SQL*Loader execution encountered a fatal error” ;;
*) echo “unknown return code $RC” ;;
esac

kindly help me with the same to run the concurrent request successfully to load the data .

Thanks & Regards

Vishnu Pratap Patel
(vishnu.patel@tcs.com)

Sanjit Anand Says:September 5th, 2008 at 8:15 pm

echo should work.what is version of your oracle u are using.

Command like
echo “Line 1.”
echo “line 2.”

suppose to print
Line 1.
Line 2.
in log file.

are u sure prog completed successfully. some time access previlege also causing , as there is no debug options unless developer is realize that something pre-requssite is not performed yet.

this is simple host based concuuurent prog used to Moves files from one directory to another

Three input parameter is there.

once you put the script in to Unix box, donot forgot to run this command
ln -s -f $FND_TOP/bin/fndcpesr XXTEST

–here is code

# Initialise environment variables

echo " Initialise location specific environment variables"

ORA_USER_PASS=$1

USERID=$2

USERNAME=$3

REQUESTID=$4

P_SOURCE_DIR=`eval echo $5`

P_TARGET_DIR=`eval echo $6`

P_FILENAME=$7

DIR=`eval echo $1`

# Initialise common environment variables

echo " Initialise common environment variables"

SCRIPT=XXTEST.prog

# Variables for Load filenames

echo " "

echo "Input Directory = " $P_SOURCE_DIR

# Setting the Directory Names

CTLDIR=$XXPH_TOP/bin

LOGDIR=$XXPH_TOP/log

BADDIR=$XXPH_TOP/log

# Setting the Log file for the .prog file

REPFILE=$APPLCSF/$APPLLOG/l$REQUESTID.req

PROBLEMS=0

# ----------------------------------------------------------

echo "\n Start" $SCRIPT

echo " ************************************"

# ------------------------------------------------------------------

# Display all parameters entered

echo " For Supplier data conversion -- Parameters entered:-" >> $REPFILE

echo " SQL*Plus userid = " $USERID >> $REPFILE

# Display Other parameters :-

echo "Other Parameters :-" >> $REPFILE

echo "--------------------" >> $REPFILE

echo "Source directory = " $P_SOURCE_DIR >> $REPFILE

echo "Target directory = " $P_TARGET_DIR >> $REPFILE

echo "Filename = " $P_FILENAME >> $REPFILE

echo "At the end of Parameter Listing " >> $REPFILE

# Check if the Data Files Exist

echo "At the start of Data Files Check " >> $REPFILE

if [ ! -f $P_SOURCE_DIR/$P_FILENAME ]

then

echo $P_SOURCE_DIR/$P_FILENAME " does not exist"

echo "**************************************************" >> $REPFILE

echo "*$P_SOURCE_DIR/$P_FILENAME " does not exist" *" >> $REPFILE

echo "**************************************************" >> $REPFILE

PROBLEMS=1

exit 1

echo "At the end of Data File Check " >> $REPFILE

# Move the file to the target directory

# ------------------------------------------------------------------

mv $P_SOURCE_DIR/$P_FILENAME $P_TARGET_DIR

if [ $? != 0 ]

then

echo "**************************************************" >> $REPFILE

echo "*$P_SOURCE_DIR/$P_FILENAME " failed to move " *" >> $REPFILE

echo "**************************************************" >> $REPFILE

PROBLEMS=1

mv $P_SOURCE_DIR/$P_FILENAME'.o' $P_TARGET_DIR

if [ $? != 0 ]

then

echo "**************************************************" >> $REPFILE

echo "*$P_SOURCE_DIR/$P_FILENAME'.o " failed to move " *" >> $REPFILE

echo "**************************************************" >> $REPFILE

PROBLEMS=1

# ------------------------------------------------------------------

if test $PROBLEMS = 1

then

echo "\nUnsuccessful termination of" $SCRIPT >> $REPFILE

echo "\n------------------------------------" >> $REPFILE

exit 1

else

echo "\n----------------------------------" >> $REPFILE

echo "Successful termination of" $SCRIPT >> $REPFILE

echo "----------------------------------" >> $REPFILE

exit 0

Sanjit Anand Says:September 5th, 2008 at 8:33 pm

Dear Vishnu,

there should not be any issue.

errors message seems there need a proper access on file in unix.

Do a quick check

1. when create a soft line you should do like

ln -s -f $FND_TOP/bin/fndcpesr test_data_host_prog

2. Make sure soft link is create or not

3. Make sure you should have proper access on the site.

When you are calling sqllodaer from its better use directly from home and just pass userid which internally passed by fndcpesr prog.

$ORACLE_HOME/bin/sqlldr USERID=$ORA_USER_PASS CONTROL=$CTLDIR/$LOADER1.ctl LOG=$LOGDIR/$LOADER1.log BAD=$BADDIR/$LOADER1.bad ERRORS=0 DATA=$LOGDIR/filenames.dat >>$REPFILE

and you can see how these are derived from parameter

ORA_USER_PASS=$1

USERID=$2

USERNAME=$3

REQUESTID=$4

P_INPUT_DIR=`echo $5`

# Setting the Control File Names

LOADER1=XXTESTLOAD

CTLDIR=$XXPH_TOP/bin

DATDIR=`echo $P_INPUT_DIR`

LOGDIR=$XXPH_TOP/log

BADDIR=$XXPH_TOP/log

Let me know , in case if this does not work, I will send you code.send me offline message.