PLSQL PROCEDURE TO CALL A REST API POST METHOD

 PLSQL PROCEDURE TO CALL A REST API POST METHOD

create or replace procedure XXXCC_XXX_XXX_PROC(oredrno number) as
-- varibles for running REST API
  l_http_request  UTL_HTTP.req;
  l_http_response UTL_HTTP.resp;
  l_response_text CLOB;
  l_payload       CLOB;
--below 3 are non mandatroy varibales only to store data from table to paas in payload
  NUM varchar2(200);
  RNAME varchar2(200);
BEGIN

--sql query only use if you are passing values from tables into api
SELECT a,b,into num,rname FROM dual where ord = oredrno;

-- Create the payload
l_payload := '{
    "method": "add",
    "data": {
      "recipient_details": {
        "recp_mobile": "'||NUM||'",
        "recp_name": "'||RNAME||'"
      },
  }';

  -- Initialize the HTTP request              URL                  METHOD
  l_http_request := UTL_HTTP.begin_request('http://x.x/xxx_xxxx', 'POST');
 
  --use this if your authentication type is basic here i have used bearer type
  --UTL_HTTP.SET_AUTHENTICATION(r => l_http_request, username => 'IDAS',password => 'id34');
 
  -- Set the headers
  UTL_HTTP.set_header(l_http_request, 'Content-Type', 'application/json');
  UTL_HTTP.set_header(l_http_request, 'Authorization', 'Bearer dfjnsfjkf439439055555555555555555502934pk');
  UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_payload));

  -- Write the payload to the request
  UTL_HTTP.write_text(l_http_request, l_payload);

  -- Get the response
  l_http_response := UTL_HTTP.get_response(l_http_request);

  -- Read the response into a variable
  DBMS_LOB.createtemporary(l_response_text, TRUE);
  UTL_HTTP.read_text(l_http_response, l_response_text);
  UTL_HTTP.end_response(l_http_response);

  -- Process the response
  DBMS_OUTPUT.put_line('Response: ' || DBMS_LOB.substr(l_response_text, 32767, 1));

EXCEPTION
  WHEN UTL_HTTP.TOO_MANY_REQUESTS THEN
    -- Handle too many requests error
    DBMS_OUTPUT.put_line('Too many requests');
END;

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