Oracle REST API using bash and SQL

Hi There,

This is a bash script through which we can invoice a REST API from backend in ORACLE R12 and we are populating data in api payload from sql database.

Using a HOST concurrent program execute this bash script to invoke api , replace your API urls and etc.

#!/bin/bash

# API URL
api_url="https://yourapi url"

# parameter for order No. and Delivery Nmber not mandatory to be used in sql query
orderno=${5}
del_number="${6}"


# Authorization Code(Bearer Token)
Autho="give_autho_code_here____"

# SQL query for details
sql_query="SELECT
    a
    || '|'
    || b
    || '|'
    || c
    || '|'
    || d
    || '|'
    || e
    || '|'
    || f
   || '|'
    || g
   || '|'
    || h
   || '|'
    || i
   || '|'
    || j
   || '|'
    || k
   || '|'
    || l
   || '|'
    || m
   || '|'
    || n
   || '|'
    || o
   || '|'
    || p
   || '|'
    || q
   || '|'
    || r
   || '|'
    || s
   || '|'
    || t
   || '|'
    || u
   || '|'
    || v
   || '|'
    || w
   || '|'
    || x
   || '|'
    || y
   || '|'
    || z
   || '|'
    || aa
   || '|'
    || ab|| '|' || '--' ncc_api
FROM
   dual"

# 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
)

# if statement - to execute all below commands only if sql query was executed without errors
if [ $? -eq 0 ]; then

# 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

NUM=$(echo $row | cut -d '|' -f1)
RNAME=$(echo $row | cut -d '|' -f2)
DIVN=$(echo $row | cut -d '|' -f3)
ORDNO=$(echo $row | cut -d '|' -f4)
LNUM=$(echo $row | cut -d '|' -f5)
LDQTY=$(echo $row | cut -d '|' -f6)
LOC=$(echo $row | cut -d '|' -f7)
BLDG=$(echo $row | cut -d '|' -f8)
ROAD=$(echo $row | cut -d '|' -f9)
BLOCK=$(echo $row | cut -d '|' -f10)
AREA=$(echo $row | cut -d '|' -f11)
VRN=$(echo $row | cut -d '|' -f12)
VFN=$(echo $row | cut -d '|' -f13)
VID=$(echo $row | cut -d '|' -f14)
DLVNO=$(echo $row | cut -d '|' -f15)
CNAME=$(echo $row | cut -d '|' -f16)
DIVNAME=$(echo $row | cut -d '|' -f17)
SON=$(echo $row | cut -d '|' -f18)
CPONO=$(echo $row | cut -d '|' -f19)
DLVNOO=$(echo $row | cut -d '|' -f20)
IDESC=$(echo $row | cut -d '|' -f21)
MCODE=$(echo $row | cut -d '|' -f22)
TOQ=$(echo $row | cut -d '|' -f23)
TDQ=$(echo $row | cut -d '|' -f24)
DQ=$(echo $row | cut -d '|' -f25)
CSITE=$(echo $row | cut -d '|' -f26)
VHVLNO=$(echo $row | cut -d '|' -f27)
DRIVRN=$(echo $row | cut -d '|' -f28)
DISCON=$(echo $row | cut -d '|' -f29)

# change the phone number in required format
if [[ $NUM =~ ^\+([0-9]{2})([0-9]{4})([0-9]{3})([0-9]{3})$ ]] || [[ $NUM =~ ^\+([0-9]{3})([0-9]{4})([0-9]{3})([0-9]{3})$ ]] || [[ $NUM =~ ^\+([0-9]{1})([0-9]{4})([0-9]{3})([0-9]{3})$ ]]; then
    formatted_number="+${BASH_REMATCH[1]} ${BASH_REMATCH[2]} ${BASH_REMATCH[3]} ${BASH_REMATCH[4]}"
    echo "Formatted phone number: $formatted_number"
    NUM=${formatted_number}
else
    echo "Phone number is in expected format"
fi
   

echo "$NUM Phone number"
echo "$RNAME customer name"
echo "$DIVN division_name"
echo "$ORDNO order_number"
echo "$LNUM line_number"
echo "$LDQTY dilevery quantity"
echo "$LOC location"
echo "$BLDG building"
echo "$ROAD road"
echo "$BLOCK block"
echo "$AREA area"
echo "$VRN veh_registration_no"
echo "$VFN veh_fleet_no"
echo "$VID veh_id"
echo "$DLVNO delivery_number"
echo "$CNAME customer_name"
echo "$DIVNAME division_name"
echo "$SON salesorderno"
echo "$DON delivery_orderno"
echo "$DNN delivery_note_number"
echo "$IDESC item_description"
echo "$MCODE mixcode"
echo "$TOQ total order quantity"
echo "$TDQ total delivered quanity"
echo "$DQ delivery_quanitiy"
echo "$CSITE address"
echo "$VHVLNO vehicle"
echo "$DRIVRN driver_name"
echo "$DISCON discon"



# Send POST request
        # Send POST request
        curl -X POST -H 'accept: application/json' -H "Authorization: Bearer ${Autho}" \
         -H 'Content-Type: application/json' -d "{
            \"method\": \"add\",
            \"data\":
                {
                    \"recipient_details\":
                        {
                            \"recp_mobile\": \"${NUM}\",
                            \"recp_name\": \"${RNAME}\",
                            \"recp_messaging\": \"sms\"
       
                
                    ,
                    \"destination_details\":
                        {
                            \"gps_location\": \"${LOC}\",
                            \"addr_bldg\": \"${BLDG}\",
                            \"addr_road\": \"${ROAD}\",
                            \"addr_block\": \"${BLOCK}\",
                            \"addr_area\": \"${AREA}\"
                        }
                    ,
                    \"vehicle_details\":
                        {
                            \"veh_registration_no\": \"${VRN}\",
                            \"veh_fleet_no\": \"${VFN}\",
                            \"veh_id\": \"${VID}\"
                        }
                    ,
                    \"delivery_details\":
                        {
                            \"delivery_note_number\" : \"${DLVNO}\" ,  
                            \"Customer Name\": \"${CNAME}\",
                            \"Item Type\": \"${DIVNAME}\",
                            \"Sales Order No.\": \"${CPONO}\",
                            \"Delivery Order No.\": \"${DON}\",
                            \"Delivery Note No.\": \"${DLVNO}\",
                            \"Item Description\": \"${IDESC}\",
                            \"Mix Code\": \"${MCODE}\",
                            \"Total Order Qty\": \"${TOQ}\",
                            \"Total Delivered Qty\": \"${TDQ}\",
                            \"Delivery Qty\": \"${DQ}\",
                            \"Delivery Address\": \"${CSITE}\",
                            \"Vehicle\": \"Mixer Truck ${VHVLNO}\",
                            \"Driver Name\": \"${DRIVRN}\",
                            \"Dispatcher Contact\": \"${DISCON}\",
                            \"Etc.\": \"Etc.\"
                        }
                    
                }
            
        }" "$api_url" > apiout.txt
done

else
    echo "Error executing SQL query. Exiting."
    exit 1

fi

END OF SCRIPT

for any queries contact me through mail : rehman0270@gmail.com

ABDUL REHMAN (Oracle Technical Concultant)