steps to call REST API from PLSQL

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.
kiranshankarLead EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
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 EngineerAuthor Commented:
ok
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.