End-to-End REST Service Testing on Oracle Database

See how to perform end-to-end REST service testing on Oracle database.

Erkin Karanlık

Jul. 21, 21 · Tutorial

Likes (2)

Comment

Save

8.7K Views

You can test a REST service on Oracle Database. You can perform our tests through the database by giving a request in JSON format and receiving a response in JSON format.

As in the example below, the name input of the related rest service is given. We can read the URL information from the newly created table structure via the NAME input.

UTL_HTTP.begin_request function can be called with url information, method type (GET,POST), http_version (1.1 , 1.0 etc).

You can get a return in JSON format as in the example.

Below I have tried to explain this structure step-by-step.

Creating the REST Service Information Table

You can get the rest URL information from a service information table. You can see the create script below.

     PLSQL 
   
 
 
   CREATE TABLE TRESTHEADERDEFINITION
(
  NRESTID      NUMBER,
  TRESTNAME    VARCHAR2(100 BYTE),
  TSERVICEURL  VARCHAR2(500 BYTE)
); 
  

     PLSQL 
   
   ALTER TABLE TRESTHEADERDEFINITION ADD (
  PRIMARY KEY
  (NRESTID);

Calling the Rest Service

We provide the name of the rest service in the information table as input.
We enter the request of the rest service in json format. It is given below as an example.
With these two inputs we are calling the p_get_post_json_request procedure.

With this logic, you can create the get method yourself.

     PLSQL 
   
 
 
   DECLARE
    -- Declarations
    REST_NAME          VARCHAR2 (32767);
    CONTENT_JSON_REQ   VARCHAR2 (32767);
    JSON_RESP          VARCHAR2 (32767);
BEGIN
    -- Initialization
    REST_NAME := 'CONVERTERVALIDATE';
    CONTENT_JSON_REQ :=
        '{"msisdn": "55XXXXYYZZ","targetOfferId": 12345,  "transactionId": "1234567"}';

    -- Call
    P_GET_POST_JSON_REQUEST (P_REST_NAME          => REST_NAME,
                             P_CONTENT_JSON_REQ   => CONTENT_JSON_REQ,
                             P_JSON_RESP          => JSON_RESP);

    -- Transaction Control
    COMMIT;
    -- Output values, do not modify
    DBMS_OUTPUT.PUT_LINE ('JSON_RESP = ' || JSON_RESP);
END; 
  

Returning the Response With the P_GET_POST_JSON_REQUEST Procedure

In the example p_get_post_json_request below, we call the getregisteredservice method to retrieve the data in the TRESTHEADERDEFINITION table.
We can take the url information from the table and give it as input to the UTL_HTTP.begin_request method.
You can enter charset information in UTL_HTTP.SET_BODY_CHARSET. Entered UTF-8 as an example.
You can enter UTL_HTTP.set_header information.
For json request, you can enter 'content_type' as application/json'.
You can enter 'accept' as 'application/json'.
If you don't want to cache, you can enter Cache-Control as 'no-cache'.
'Content-Length' can be entered as long as the json request length.
Json request content is given to the UTL_HTTP.write_text method.
Response information is obtained with UTL_HTTP.get_response.
You can set the response with UTL_HTTP.read_line and give it to the out variable with p_json_resp.

     PLSQL 
   
 
 
   PROCEDURE p_get_post_json_request (p_rest_name          IN     VARCHAR2,
                                   p_content_json_req   IN     VARCHAR2,
                                   p_json_resp          OUT    CLOB)
IS
    req              UTL_HTTP.req;
    res              UTL_HTTP.resp;
    url              VARCHAR2 (500);
    content          VARCHAR2 (4000);
    l_res_line       VARCHAR2 (32767);
    lv_namespace     VARCHAR2 (250);
    lv_soap_action   VARCHAR2 (250);
    lv_result_name   VARCHAR2 (250);
    lv_method        VARCHAR2 (32767);
    lv_tag           VARCHAR2 (100);
BEGIN

    getregisteredservice (piv_rest_name => p_rest_name, pov_serviceurl => url);
    content := p_content_json_req;
    req := UTL_HTTP.begin_request (url, 'POST', 'HTTP/1.1');
    UTL_HTTP.SET_BODY_CHARSET (req, 'UTF-8');
    UTL_HTTP.set_header (req, 'Content-Type', 'application/json');
    UTL_HTTP.set_header (req, 'Accept', 'application/json');
    UTL_HTTP.set_header (req, 'Cache-Control', 'no-cache');
    UTL_HTTP.set_header (req, 'Content-Length', LENGTHB (content));
    UTL_HTTP.write_text (req, content);

    res := UTL_HTTP.get_response (req);

    BEGIN
        LOOP
            UTL_HTTP.read_line (res, l_res_line);
            p_json_resp := p_json_resp || l_res_line;
        END LOOP;
        UTL_HTTP.end_response (res);
    EXCEPTION
        WHEN UTL_HTTP.end_of_body
        THEN
            UTL_HTTP.end_response (res);
        WHEN OTHERS
        THEN
            UTL_HTTP.end_response (res);
    END;
END p_get_post_json_request; 
  

Receiving Sevice information With Register Service Function

The following register service procodure is used to pull the data from the TRESTHEADERDEFINITION table.

     PLSQL 
   
 
 
   PROCEDURE getregisteredservice (piv_rest_name    IN     VARCHAR2,
                                pov_serviceurl      OUT VARCHAR2)
AS
    l_nrestid   trestheaderdefinition.nrestid%TYPE;
BEGIN
    BEGIN
        SELECT trhd.nrestid, trhd.tserviceurl
          INTO l_nrestid, pov_serviceurl
          FROM trestheaderdefinition trhd
         WHERE trhd.trestname = piv_rest_name;

      DBMS_APPLICATION_INFO.set_module (
            module_name =>
                SUBSTR (piv_rest_name, 1, 48),
            action_name =>
                SUBSTR ('NRESTID=' || TO_CHAR (l_nrestid), 1, 32));
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            raise_application_error (
                -20001,
                   '(invokeRegisteredService) : Cannot find any registered service called "'
                || piv_rest_name
                || '" on "trestheaderdefinition"');
        WHEN TOO_MANY_ROWS
        THEN
            raise_application_error (
                -20001,
                   '(invokeRegisteredService) : There are multiple definitions for service called "'
                || piv_rest_name
                || '" on "."trestheaderdefinition"');
    END;
END getregisteredservice;
 
  

All Script

     PLSQL 
   
 
 
    DECLARE
    -- Declarations
    REST_NAME           VARCHAR2 (32767);
    CONTENT_JSON_REQ   VARCHAR2 (32767);
    JSON_RESP          VARCHAR2 (32767);

PROCEDURE getregisteredservice (piv_rest_name    IN     VARCHAR2,
                                pov_serviceurl      OUT VARCHAR2)
AS
    l_nrestid   trestheaderdefinition.nrestid%TYPE;
BEGIN
    BEGIN
        SELECT trhd.nrestid, trhd.tserviceurl
          INTO l_nrestid, pov_serviceurl
          FROM trestheaderdefinition trhd
         WHERE trhd.trestname = piv_rest_name;

        DBMS_APPLICATION_INFO.set_module (
            module_name =>
                SUBSTR (piv_rest_name, 1, 48),
            action_name =>
                SUBSTR ('NRESTID=' || TO_CHAR (l_nrestid), 1, 32));
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            raise_application_error (
                -20001,
                   '(invokeRegisteredService) : Cannot find any registered service called "'
                || piv_rest_name
                || '" on "trestheaderdefinition"');
        WHEN TOO_MANY_ROWS
        THEN
            raise_application_error (
                -20001,
                   '(invokeRegisteredService) : There are multiple definitions for service called "'
                || piv_rest_name
                || '" on "."trestheaderdefinition"');
    END;
END getregisteredservice;

PROCEDURE p_get_post_json_request (p_rest_name          IN     VARCHAR2,
                                   p_content_json_req   IN     VARCHAR2,
                                   p_json_resp          OUT    CLOB)
IS
    req              UTL_HTTP.req;
    res              UTL_HTTP.resp;
    url              VARCHAR2 (500);
    content          VARCHAR2 (4000);
    l_res_line       VARCHAR2 (32767);
    lv_namespace     VARCHAR2 (250);
    lv_soap_action   VARCHAR2 (250);
    lv_result_name   VARCHAR2 (250);
    lv_method        VARCHAR2 (32767);
    lv_tag           VARCHAR2 (100);
BEGIN
    getregisteredservice (piv_rest_name => p_rest_name, pov_serviceurl => url);
    content := p_content_json_req;
    req := UTL_HTTP.begin_request (url, 'POST', 'HTTP/1.1');
    UTL_HTTP.SET_BODY_CHARSET (req, 'UTF-8');
    UTL_HTTP.set_header (req, 'Content-Type', 'application/json');
    UTL_HTTP.set_header (req, 'Accept', 'application/json');
    UTL_HTTP.set_header (req, 'Cache-Control', 'no-cache');
    UTL_HTTP.set_header (req, 'Content-Length', LENGTHB (content));
    UTL_HTTP.write_text (req, content);

    res := UTL_HTTP.get_response (req);
    BEGIN
        LOOP
            UTL_HTTP.read_line (res, l_res_line);
            p_json_resp := p_json_resp || l_res_line;
        END LOOP;

        UTL_HTTP.end_response (res);
    EXCEPTION
        WHEN UTL_HTTP.end_of_body
        THEN
            UTL_HTTP.end_response (res);
        WHEN OTHERS
        THEN
            UTL_HTTP.end_response (res);
    END;
END p_get_post_json_request;

 BEGIN
     -- Initialization
    REST_NAME := 'CONVERTERVALIDATE';
    CONTENT_JSON_REQ :=  '{"msisdn": "55XXXXYYZZ","targetOfferId": 12345,  "transactionId": "1234567"}';
    -- Call
        P_GET_POST_JSON_REQUEST (
        P_REST_NAME           =>  REST_NAME,
        P_CONTENT_JSON_REQ    =>  CONTENT_JSON_REQ,
        P_JSON_RESP           =>  JSON_RESP);

    -- Transaction Control
    COMMIT;
    -- Output values, do not modify
 DBMS_OUTPUT.PUT_LINE ( 'JSON_RESP = ' || JSON_RESP );
 END; 
  

DBMS_OUTPUT ON MODE:

Response Output:

     JSON 
   
     {
    "responseCode": 0,
    "responseDescription": "SUCCESS"
}

Database REST Web Protocols Oracle Database

Opinions expressed by DZone contributors are their own.

Related

Trending