2017/05/03

Send HTTP Post Request Using PL/SQL In Oracle Database

May 03, 2017  Ahmed Abo Ghaly  4 comments

In the name of Allah, the Merciful, the Compassionate




Today we will learn how to send HTTP post request from Oracle database using PL/SQL and get the response, we can use it with (RESTful-SOAP) services to send (JSON-XML) to API or in any another thing.
We will use package UTL_HTTP and this package starting in database Oracle 11g.

It's a simple way ... let's start:

Requirements:
1- Database 11g XE or above.
    Note : you can't use any database older than 11g
2- API (to receive the request and return response).

Steps:
1- Connect "sys" as sys dba and grant privilege to the user who send the request.
1
2
grant execute on utl_http to scott;
grant execute on dbms_lock to scott;

 2- Create an Access Control List (ACL) called (acl_file.xml) associated with database user (SCOTT).
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
begin
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'acl_file.xml', 
    description  => 'test xml file',
    principal    => 'SCOTT',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);
end;
 
begin
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'acl_file.xml',
    host        => 'localhost', 
    lower_port  => 8085,
    upper_port  => NULL);    
end;
Notes : 
ACL            => Change it to the Access Control List File name.
description => Enter a short description.
principal     => Change it to your user name (Must Be Upper Case).
host             => Change it to API IP (If you use the same machine leave it localhost).
lower_port  => Change it to API port (If You Have Range Of Ports Set Upper_Port).
After that the database will allow the user (scott) to make a call with the API.




3- Connect as user (SCOTT) and create this function.








 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41


CREATE OR REPLACE FUNCTION SEND_DB_REQUST (
                                            P_API     VARCHAR2,
                                            P_CONTENT VARCHAR2
                                          ) RETURN VARCHAR2
IS
  REQ UTL_HTTP.REQ;
  RES UTL_HTTP.RESP;
  BUFFER VARCHAR2(4000); 
  RESPONS VARCHAR2(4000);
BEGIN
  -- Defined Http Version And Request Type
  REQ := UTL_HTTP.BEGIN_REQUEST(P_API, 'POST',' HTTP/1.1');
  
  -- Defined Browser Type
  UTL_HTTP.SET_HEADER(REQ, 'user-agent', 'mozilla/4.0'); 
  
  -- Defined Content Type (JSON - XML .. etc)
  UTL_HTTP.SET_HEADER(REQ, 'content-type', 'application/xml'); 
  
  -- Defined Content Length
  UTL_HTTP.SET_HEADER(REQ, 'Content-Length', LENGTH(P_CONTENT));
 
  -- Sent HTTP Request
  UTL_HTTP.WRITE_TEXT(REQ, P_CONTENT);
  RES := UTL_HTTP.GET_RESPONSE(REQ);
  
  
  -- Get The Response From The HTTP Call
  BEGIN
    LOOP
      UTL_HTTP.READ_LINE(RES, BUFFER);
      RESPONS := RESPONS || BUFFER;
    END LOOP;
    UTL_HTTP.END_RESPONSE(RES);
    RETURN RESPONS;
  EXCEPTION
    WHEN UTL_HTTP.END_OF_BODY THEN UTL_HTTP.END_RESPONSE(RES);
    RETURN RESPONS;
  END;
  
END;











This function will take :
 - The API URL with port like ('http://localhost:8085/AboGhaly/api/xml').
 - The Contan Which you want send it to the API like (JSON, XML).
Notes :  - I'm using XML code if you want send JSON code you have to change it.




4- Send the Request by the function.







 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


SELECT
    SEND_DB_REQUST (
                    'http://localhost:8085/AboGhaly/api/xml',
                    '<note>
                        <to>Tove</to>
                        <from>Jani</from>
                        <heading>Reminder</heading>
                        <body>Hello Eng Ahmed Abo Ghaly</body>
                    </note>'
                    )
FROM DUAL;











Now I Finished if there is any question please ask me.



















Posted in:  



















































Twitter
Delicious
Facebook
Digg
Stumbleupon
Favorites
More