Give it a REST PL/SQL

In a rapid follow up to the wildly popular post 'give it a rest apex' i decided to have a go at implementing the same concept in plsql. In fact i wanted to be able to basically run a select statement that could return me the results from the REST call as a series of rows to then do something with - this would be more flexible than only having it available in Apex.

So i went about investigating how this could be done (by investigating i mean trawling the internet for code i could 'reuse' of course).

And i couldn't find anything that did exactly what i wanted, in fact it was difficult to actually find the right search terms to even get me in the right ball park - there is lots of stuff out there about turning oracle in to a rest enabled database - but i just wanted oracle to call a rest service that existed elsewhere.

I ended up having to write my own :-(

It was a little fiddly and used a few different tricks and techniques but i think it's really worth sharing as with quite a short code block i can do what i want honest.....

At a high level i've defined a couple of types (i know , i know no-one really likes these but they are essential to make the pipelined function work), then a pipelined table function to return data and then just a SQL statement to call the pipelined table function.

The function uses an apex function to actually make the REST call which made things quite easy on that front (although some of the inputs were a little fiddly as the REST url contained quotes and spaces).

Anyway enough waffle i'm sure you just want to see the code - so here it is:

1) First the types - a 'row' definition, and then a 'table' definition based on that

CREATE TYPE rt_row AS OBJECT (
  ticket VARCHAR2(4000)
);
/

CREATE TYPE rt_tab IS TABLE OF rt_row;
/

2) Then we create the pipelined table function

create or replace function rt return rt_tab
  pipelined as
  rtresults clob;
  l_pos     PLS_INTEGER := 1;
  l_idx     PLS_INTEGER;
  l_delim   varchar2(1) := CHR(10);

begin
  apex_web_service.g_request_headers(1).name := 'Content-Type';
  apex_web_service.g_request_headers(1).Value := 'application/x-www-form-urlencoded; charset=utf-8';
  rtresults := apex_web_service.make_rest_request(p_url         => 'http://servername/REST/1.0/search/ticket?query=queue=''DB%20Administrator''',
                                                  p_http_method => 'POST',
                                                  p_parm_name   => apex_util.string_to_table('user:pass'),
                                                  p_parm_value  => apex_util.string_to_table('username:password'));
  LOOP
    l_idx := INSTR(rtresults, l_delim, l_pos);
    IF l_idx > 0 THEN
      PIPE ROW(rt_row(substr(rtresults, l_pos, l_idx - l_pos)));
      l_pos := l_idx + LENGTH(l_delim);
    ELSE
      PIPE ROW(rt_row(substr(rtresults, l_pos)));
      RETURN;
    END IF;
  END LOOP;
  RETURN;
end;
/

3) So everything is in place now we just need to select from the function


select * from table(RT);

15782: UAT42 Refresh
15785: RP creation

etc

etc

And there we go - calling a remote REST interface from a select statement - neat huh?

(oh and a couple of notes that might stop this working - make sure your proxy allows this kind of access if you have one and make sure the database acls allow the request through).