Oracle Apex Use of apex_web_service.make_rest_request

Instead of use UTL_HTTP.BEGIN_REQUEST in PL/SQL oracle apex offered to make web service request through “apex_web_service.make_rest_request” API. 

Example Web Service Sample URL:

http://quotes.rest/qod.json

Syntax:-

DECLARE
l_response CLOB;
BEGIN
l_response:= apex_web_service.make_rest_request(p_url => ‘http://quotes.rest/qod.json’,
p_http_method => ‘GET’
);
END;

Webservice Response:-

{
    "success": {
        "total": 1
    },
    "contents": {
        "quotes": [
            {
                "quote": "If you want to test your memory, try to recall what you were worrying about one year ago today.",
                "length": "95",
                "author": "E. Joseph Cossman",
                "tags": [
                    "inspire",
                    "memory",
                    "tests",
                    "tod"
                ],
                "category": "inspire",
                "date": "2019-07-30",
                "permalink": "https://theysaidso.com/quote/e-joseph-cossman-if-you-want-to-test-your-memory-try-to-recall-what-you-were-wor",
                "title": "Inspiring Quote of the day",
                "background": "https://theysaidso.com/img/bgs/man_on_the_mountain.jpg",
                "id": "hkwVbTc2edsUSvNVnjTrEweF"
            }
        ],
        "copyright": "2017-19 theysaidso.com"
    }
}

From above response we want to get only Quote, Author and background with use if JSON parser and apex_json.get_varchar2,

apex_json.parse(l_response);

/* Get Data from Response */
lv_quote := apex_json.get_varchar2(‘contents.quotes[1].quote’);
lv_author := apex_json.get_varchar2(‘contents.quotes[1].author’);

Sample Output:-

Quote:- If you want to test your memory, try to recall what you were worrying about one year ago today.
Author:- E. Joseph Cossman

Sample Full Executable Code:-

DECLARE
l_response CLOB;
lv_quote VARCHAR2(4000);
lv_author VARCHAR2(4000);
lv_bg_url VARCHAR2(4000);
BEGIN
/* Set Content Type */

apex_web_service.g_request_headers(1).name := ‘Content-Type’;

/* Set Webservice Response Output */
apex_web_service.g_request_headers(1).value := ‘application/json’;

/* Make Webservice Request */
l_response := apex_web_service.make_rest_request(

p_url => ‘http://quotes.rest/qod.json’,

p_http_method => ‘GET’);

/* Parsing Webservice Response as JSON */
apex_json.parse(l_response);

/* Get Data from Response */
lv_quote := apex_json.get_varchar2(‘contents.quotes[1].quote’);
lv_author := apex_json.get_varchar2(‘contents.quotes[1].author’);
lv_bg_url := apex_json.get_varchar2(‘contents.quotes[1].background’);
/* Print Response */

dbms_output.put_line(‘Quote:- ‘ || lv_quote);
dbms_output.put_line(‘Author:- ‘ || lv_author);
dbms_output.put_line(‘Background URL:- ‘ || lv_bg_url);

END;

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s