How to use RESTful to avoid DB Links with ā'pěks

So the question came up of avoiding a db link by using the APEX Listener's RESTful services to get at the same data.  This is all in the context of an Apex application so apex_collections is the obvious place to stuff transient data that could be used over a session.

Step 1:  Make the RESTful Service.
The only catch is to turn pagination off ( make it zero ).  I didn't need it for now so this code that follows doesn't account for it.

Step 2. Install PL/JSON 

  In the future this can be done with built in support for JSON in the database.


Step 3.  Glue code.

declare
-- APEX
  g_collection_name varchar2(32767) := 'json_data';
  
-- JSON Portion
  l_url  varchar2(32767) := 'http://myhost.mydomain.com/apex/dbtools/features/latestRequests';
  l_jsonObj json;
  l_jsonObj2 json;
  l_jsonKeys json_list;
  l_key varchar2(32767);
  l_value varchar2(32767);
  
  l_tempdata json_value;
  l_temparray json_list;
  i number;  
  ii number;  
 
 values_array dbms_sql.varchar2_table;

  procedure varray2collection(p_values dbms_sql.varchar2_table) as
    l_sql varchar2(32767) := 'begin APEX_COLLECTION.ADD_MEMBER (p_collection_name =>:NAME,';  
    i number;
    c number;
    ret number;
  begin
  -- build up the begin..end sql block
    for i in 1..p_values.count loop
      l_sql := l_sql || 'p_c00'|| i ||'=>:c'||i||',';
    end loop;
    
    -- chop off the last comma
    l_sql := substr(l_sql,1,length(l_sql)-1);
    -- add the end
    l_sql := l_sql || '); end;';
    dbms_output.put_line('SQL='||l_sql);
    
    c := DBMS_SQL.OPEN_CURSOR;
    dbms_output.put_line('c='||c);
    dbms_sql.parse(c,l_sql,dbms_sql.native);
    -- bind in the collection name
    dbms_sql.bind_variable(c,':NAME'||i,g_collection_name);

    -- bind in the values
    for i in 1..p_values.count loop
        dbms_sql.bind_variable(c,':c'||i,p_values(i));
    end loop;
    ret := dbms_sql.execute(c);
    dbms_sql.close_cursor(c);
    exception when others then
       dbms_sql.close_cursor(c);
  end;
  
  -- simple function to remove the leading and trailing "
  function trimQuotes(s varchar2) return varchar2 is
    l_value varchar2(32767);
  begin
    l_value := substr(s,2);
    l_value := substr(l_value,1,length(l_value)-1);
    return l_value;
  end;
  
  -- function to grab the URL and return a JSON object
  function getURL(p_url varchar2) return JSON is
       -- HTTP Portion
      l_http_request   UTL_HTTP.req;
      l_http_response  UTL_HTTP.resp;
      l_clob           CLOB;
      l_text           VARCHAR2(32767);
  begin
      -- Initialize the CLOB.
      DBMS_LOB.createtemporary(l_clob, FALSE);
      -- Make a HTTP request and get the response.
      l_http_request  := UTL_HTTP.begin_request(l_url);
      l_http_response := UTL_HTTP.get_response(l_http_request);
      -- Copy the response into the CLOB.
       BEGIN
          LOOP
             UTL_HTTP.read_text(l_http_response, l_text, 32766);
             DBMS_LOB.writeappend (l_clob, LENGTH(l_text), l_text);
      END LOOP;
      EXCEPTION  WHEN UTL_HTTP.end_of_body THEN
         null;
      END;
       UTL_HTTP.END_RESPONSE(l_http_response);

    -- Relase the resources associated with the temporary LOB.
    dbms_output.put_line('Length:'|| DBMS_LOB.getlength(l_clob));
  return json(l_clob);
  end geturl;
  
BEGIN
-- apex collection
  APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(g_collection_name);

-- JSON
    l_jsonObj := getURL(l_url);
    -- grab the items 
    l_temparray:= json_list(l_jsonObj.get('items'));
--    temparray := json_list(tempdata);
    dbms_output.put_line( 'Count:'|| l_temparray.count);
    for i in 1..l_temparray.count loop
         -- get the Nth item
          l_tempdata :=    l_temparray.get(i);
          dbms_output.put_line( 'Record['|| i||']:'|| l_tempdata.to_char);
          -- bring the Nth item into it's own json object
         l_jsonObj2 := json(l_tempdata);
         -- get all the keys to walk
         l_jsonKeys := l_jsonobj2.get_keys();
                   for ii  in 1..l_jsonKeys.count  loop
                      l_key := trimQuotes(l_jsonKeys.get(ii).to_char);
                      l_value :=  trimQuotes(l_jsonObj2.get(l_key).to_char);
                      dbms_output.put_line(l_key || '=' ||l_value);
                      -- build up the array to pass to the apex collection
                      values_array(ii) := l_value;
                   end loop;
                   dbms_output.put_line('LENGTH:'||values_array.count);
                   -- send it to the collection
                   varray2collection(values_array);
        end loop;
end;
/

Step 4. Make a page in Application Express

  The glue code is in the process named Get JSON.
  The interactive report is simply: select * from apex_collections.

Step 5. It's alive

Oh the ā'pěks is from the oraclenerd store.  If you don't have a shirt already, you should.