steps to call REST API from PLSQL

kiranshankar
kiranshankar used Ask the Experts™
on
Can you please tell me how to call REST API from PLSQL stored procedure. I know I need to use UTL_HTTP. but I want to understand the steps to set up the ACL list for the REST end points. any working sample on setting up ACL list and service invocation using UTL_HTTP would be helpful.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
I suggest the following two articles for setting up the ACL, especially the second article if you have other ACLs already in place as there can be unexpected interactions between them.

http://www.experts-exchange.com/articles/8429/How-to-use-Access-Control-Lists-in-Oracle.html
http://www.experts-exchange.com/articles/9074/How-to-use-Network-Access-Control-Lists-in-Oracle.html


for calling a REST api,  you might not even need UTL_HTTP.   The httpuritype function may be sufficient if you just need to do gets

select httpuritype('http://some.address.com') from dual
Most Valuable Expert 2011
Top Expert 2012
Commented:
If you need to push data to the service then you will need utl_http

http://jsonplaceholder.typicode.com  offers a free webservice you can read from and write to
it doesn't actually keep your changes, but processes the request and returns a status to indicate success or failure.

the source for the server is available on github

https://github.com/typicode/jsonplaceholder


Some examples:


Read all posts:

DECLARE
    v_request    UTL_HTTP.req;
    v_response   UTL_HTTP.resp;
    v_text       VARCHAR2(1024);
BEGIN
    v_request := UTL_HTTP.begin_request('http://jsonplaceholder.typicode.com/posts');

    v_response := UTL_HTTP.get_response(v_request);

    DBMS_OUTPUT.put_line(RPAD('=', 80, '='));
    DBMS_OUTPUT.put_line('Response status code: ' || v_response.status_code);
    DBMS_OUTPUT.put_line('Response reason phrase: ' || v_response.reason_phrase);

    LOOP
        BEGIN
            UTL_HTTP.read_text(v_response, v_text);
            DBMS_OUTPUT.put_line(v_text);
        EXCEPTION
            WHEN UTL_HTTP.end_of_body
            THEN
                NULL;
        END;

        EXIT WHEN v_text IS NULL;
    END LOOP;

    DBMS_OUTPUT.put_line(RPAD('=', 80, '='));

    UTL_HTTP.end_response(v_response);
END;

Open in new window


Read filtered posts:

DECLARE
    v_request   UTL_HTTP.req;
    v_response   UTL_HTTP.resp;
    v_text      VARCHAR2(1024);
BEGIN
    v_request := UTL_HTTP.begin_request('http://jsonplaceholder.typicode.com/posts?userId=2');   

    v_response := UTL_HTTP.get_response(v_request);

    DBMS_OUTPUT.put_line(RPAD('=', 80, '='));
    DBMS_OUTPUT.put_line('Response status code: ' || v_response.status_code);
    DBMS_OUTPUT.put_line('Response reason phrase: ' || v_response.reason_phrase);

    LOOP
        BEGIN
            UTL_HTTP.read_text(v_response, v_text);
            DBMS_OUTPUT.put_line(v_text);
        EXCEPTION
            WHEN UTL_HTTP.end_of_body
            THEN
                NULL;
        END;

        EXIT WHEN v_text IS NULL;
    END LOOP;

    DBMS_OUTPUT.put_line(RPAD('=', 80, '='));

    UTL_HTTP.end_response(v_response);
END;

Open in new window


Note, both of these could be done with httpuritype

for example

select httpuritype('http://jsonplaceholder.typicode.com/posts?userId=2').getclob() from dual

Open in new window


POST a new record

DECLARE
    v_request   UTL_HTTP.req;
    v_response   UTL_HTTP.resp;
    v_text      VARCHAR2(1024);
BEGIN
    v_text := q'[data:{title: 'foo', body: 'bar', userId: 1}]';

    v_request := UTL_HTTP.begin_request('http://jsonplaceholder.typicode.com/posts', 'POST');

    UTL_HTTP.set_header(v_request, 'Content-Length', LENGTH(v_text));

    UTL_HTTP.write_text(v_request, v_text);

    v_response := UTL_HTTP.get_response(v_request);

    DBMS_OUTPUT.put_line(RPAD('=', 80, '='));
    DBMS_OUTPUT.put_line('Response status code: ' || v_response.status_code);
    DBMS_OUTPUT.put_line('Response reason phrase: ' || v_response.reason_phrase);

    LOOP
        BEGIN
            UTL_HTTP.read_text(v_response, v_text);
            DBMS_OUTPUT.put_line(v_text);
        EXCEPTION
            WHEN UTL_HTTP.end_of_body
            THEN
                NULL;
        END;

        EXIT WHEN v_text IS NULL;
    END LOOP;

    DBMS_OUTPUT.put_line(RPAD('=', 80, '='));

    UTL_HTTP.end_response(v_response);
END;

Open in new window


Delete an existing record

DECLARE
    v_request   UTL_HTTP.req;
    v_response   UTL_HTTP.resp;
    v_text      VARCHAR2(1024);
BEGIN
    v_request := UTL_HTTP.begin_request('http://jsonplaceholder.typicode.com/posts/1', 'DELETE');   

    v_response := UTL_HTTP.get_response(v_request);

    DBMS_OUTPUT.put_line(RPAD('=', 80, '='));
    DBMS_OUTPUT.put_line('Response status code: ' || v_response.status_code);
    DBMS_OUTPUT.put_line('Response reason phrase: ' || v_response.reason_phrase);

    LOOP
        BEGIN
            UTL_HTTP.read_text(v_response, v_text);
            DBMS_OUTPUT.put_line(v_text);
        EXCEPTION
            WHEN UTL_HTTP.end_of_body
            THEN
                NULL;
        END;

        EXIT WHEN v_text IS NULL;
    END LOOP;

    DBMS_OUTPUT.put_line(RPAD('=', 80, '='));

    UTL_HTTP.end_response(v_response);
END;

Open in new window

kiranshankarLead Engineer

Author

Commented:
ok

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial