Host Programs - calling shell script as concurrent program

There are multiple scenarios when we want to have a shell script as a concurrent program. Some of the very common reasons to go for shell scripts could be –

-          Involves file operations like loading a file and then processing it or generating an output file

-          Involves Encryption and or transmission

-          Involves multithreading

Disadvantages of having a Host Program:

-          Impact analysis is a little cumbersome since all our code is present in a host file. Searching through the text is required for analysis

-          Since it requires unix skill as well along with the database programming, effort required is more

Below are the steps to be followed to create host based programs:

1.       Create a shell script

Create a shell script which fulfills the business needs. The program is not required to be having extension .prog though it is the most commonly used extension. Once the script is ready, FTP the script in ASCII mode to corresponding application TOP’s bin folder ie if I’m developing under custom PO, I might place the file under $XXPO_TOP/bin location. The script should be given read and execute permission

2.       Create symbolic link to fndcpesr

To execute a script via Oracle apps, we need to pass 4 mandatory parameters. Alternatively (and most commonly) we create a link to the fndcpesr file present in $FND_TOP/bin. fndcpesr takes care of passing these 4 parameters to the script

ln –fs $FND_TOP/bin/fndcpesr $XX_TOP/bin/XX_CUSTOM

Once the link is created first 4 parameters will always be passed to the shell script ie $1 to $4:

$1 -> User Password

$2 -> User ID

$3 -> User Name

$4 -> Request ID

All other parameters will be taken

3.       Create executable, concurrent program and assign it to the request group

Notes:

• If there are more than 5 parameters, then you might have to use {} to fetch the values Eg: xx_val=${10}
• We can connect to DB using below command:

Xx_value=`sqlplus -s <<EOSQL

$APPSLOGIN

SET SERVEROUTPUT ON SIZE 1000000

WHENEVER SQLERROR EXIT SQL.SQLCODE

DECLARE

….

BEGIN

…..

DBMS_OUTPUT.PUT_LINE(‘Y’); -- this is the value which will be assigned to the variable Xx_value from sqlplus

EXCEPTION

               WHEN OTHERS THEN

               DBMS_OUTPUT.PUT_LINE('SQLERRM: '||SQLERRM);

               END;

               /

EXIT

EOSQL`

• We can mark the program as success, error or warning by exiting with the value 0, 1 and 2 respectively Eg: exit 0 at the end of the code will complete the concurrent program with success
• We can use commonly used utilities like sqlldr or ftp or sftp or mail etc in the host scripts
• We can submit child concurrent programs from host programs in two ways. 

1. By connecting to DB and submitting the concurrent program via fnd_request.submit_request 
2. By using CONCSUB utility