Process to execute shell script from oracle R12

Process to Call a shell script or REST API from oracle R12

Ø    To call a rest api we need to run curl command in bash script

Example of simple bash script calling API

#!/bin/bash

curl -L 'https://aet-trackine.com/services/' \

-H 'Content-Type: application/json' \

-H 'Authorization: Bearer F95U05owDB6e8%2b0e3bAV5GSRmBzJB0Q0WeplT5XDSOMoLBpk' \

-d '{

    "method": "add",

    "data":

        {

            "recipient_details":

                {

                    "recp_mobile": "+91991273324",

                    "recp_name": "John Doe",

                    "recp_messaging": "sms"

                }

       }'

 

Ø     Save the script in file with .prog extension example : restapi.prog

Ø     Store it in this path in winscp “/u02/oracle/VIS/fs2/EBSapps/appl/ont/12.0.0/bin” replace ‘ont‘ with your particular top name

Ø     giving the file executable permissions >right click on file>click on properties>in octal write 0777

Ø  we need to create soft link for this file, 

 STEP-1 - "cd" to the path of host file.                        

 cd $PO_TOP/bin                                                                                                                  

 STEP-2 - run this command                                                                                                        

 ln -s $FND_TOP/bin/fndcpesr softlinkname  

 (softlinkname = file name without .prog extension exmpl: if file name restapi.prog then softlinkname is restapi)

Ø     open oracle application go to application developer responsibility and create a executable with type as HOST and executable name as softlinkname that we created

Ø     now create a program for that execuatable

Ø     attach the program for required responsibility request group

Ø     run the concurrent program , the output of the host script can be checked by clicking on log


Here is a scenario if we have to use sql statements to populate any values in bash or shell script

#!/bin/bash

# parameter for sql query

ename=${5}

deptno="${6}"

# SQL query for details

sql_query="SELECT

    name

    || '|'

    || empno

    || '|'

    || deptno

   from emp where deptno=${deptno} ans ename=${ename}

    )"

# Execute the SQL query using SQL*Plus and capture the results

result=$(sqlplus -S apps/apps <<EOF

set heading off

set feedback off

set pagesize 0

set linesize 1000

set trimspool on

set termout off

set echo off

set verify off

whenever sqlerror exit sql.sqlcode

$sql_query;

EOF

)

#Echo the result of sql query

echo "$result"

# Iterate over the results using a for loop

IFS=$'\n'       # Set the Internal Field Separator to newline

for row in $result; do

name=$(echo $row | cut -d '|' -f1)

empno=$(echo $row | cut -d '|' -f2)

deptno=$(echo $row | cut -d '|' -f3)

# Send POST request

curl -L 'https://aet-trackinine.com/services/' \

-H 'Content-Type: application/json' \

-H 'Authorization: Bearer F95U05owDB6e8%2b0epno%2baYZWzJB0Q0WeplT5XDG1SOMoLBpk' \

-d "{

    \"method\": \"add\",

    \"data\":

        {

            \"recipient_details\":

                {

                    \"recp_mobile\": \"$name\",

                    \"recp_name\": \"$empno\",

                    \"recp_messaging\": \"$deptno\"

                }

       }" > apiout.txt

done

 

Ø      follow same steps to run this script

Ø      when it comes to pass parameters in shell script, when we are creating program in oracle apps click on parameters tab and give parameters in there in a sequence , giving it in a sequence because in script parameters are not knon by their name but by their sequence first parameter is stored in {5} in shell script and second parameter is stored in {6} etc..

 

Comments

Post a Comment

Popular posts from this blog

opaque schema xsd (standard file used in OIC)

 opaque schema xsd (standard file used in OIC) You can use an opaque schema in a stage file action Read File or Write File operation without concern for a schema for the file.   The only condition is that whatever is sent to the opaque element in the opaque schema must be base64-encoded data.  or in other words  it is used to decode base64 files Save the below xml as  opaqueschema.xsd  file and then you can use it in the integration. <?xml version = '1.0' encoding = 'UTF-8'?> <schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/opaque/" xmlns="http://www.w3.org/2001/XMLSchema" > <element name="opaqueElement" type="base64Binary" /> </schema>
Read more

SQL,PLSQL interview practice and DSA patterns

 --=============sql=========================== --How to find duplicate records with the number they are duplicated? select job,count(*) from emp group by job; --Write a Query to select to the Nth highest salary from a table? select min(sal) from (select  sal from emp order by sal desc) where rownum < 3;   --Can Dual table be deleted or dropped or altered or inserted? select round(123.45) from dual; --it cant be deleted or dropped or altered or inserted --List the emps in dept 20 whose sal is > the avg sal of deptno 10 emps? select * from emp where deptno = 20  and sal>(select avg(sal)from emp where deptno = 10); --List the empno, ename, sal, dname of all the 'Mgrs' and 'Analyst' working in NEWYORK, --DALLAS with an exp more than 7 years without receiving the Comma Asc order of Loc? select empno,ename,sal,dname from emp,dept where emp.deptno= dept.deptno and  loc in('NEWYORK', 'DALLAS') and job in ('manager','analyst')and trunc(mo...
Read more

QR code Generate From Oracle PLSQL

QR code Generate From Oracle PLSQL Here is a GITHUB link For necessary Files. https://github.com/Rehman0270/PLSQLQRcode 1) Download The required files from above link. 2) upload all jar archives in lib folder to your database  (Eample path in WINSCP to put the jar files : /u02/oracle/VIS/fs1/EBSapps/10.1.2/lib ) 3) After uploading open putty and change directory to lib folder in WINSCP and run below commands one by one cd  /u02/oracle/VIS/fs1/EBSapps/10.1.2/lib loadjava -force -genmissing -r -user username/password@database -verbose core-1.7.jar loadjava -force -genmissing -r -user username/password@database -verbose javase-1.7.jar loadjava -force -genmissing -r -user username/password@database -verbose qrgen-1.2.jar 4) Open sql developer and run this functions CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "QRCodeBean" as import oracle.sql.BLOB; import oracle.sql.*; import oracle.jdbc.driver.*; import java.sql.*; import javax.imageio.ImageIO; import java.awt.image.Buffered...
Read more