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.