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.
- By connecting to DB and submitting the concurrent program via fnd_request.submit_request
- By using CONCSUB utility
