How to fetch data from API in Oracle APEX without web source module

Question

I'm just new with APEX, PL/SQL and API/JSON so please bear with me.

I need to create a search page where the data will be coming from the API.

I tried to do it with web source but unfortunately I'm having an error, checked already with the dba team, etc. the error still there, thinking its about the version issue or something, so i remove this idea, though this will really help me a lot.

So the workaround is that the PL/SQL will connect to the API.

So it goes like this: In APEX, I will input some data on the textbox and when I click the search button it will fetch the data from API to the interactive report.

**UPDATED This is what I have and I believe there's a conversion of JSON thing that I also need to do.

 declare  
    v_url      varchar2(1000); 
    v_wallet_path   varchar2(120) :='<walletvalue>';
    v_body    clob :=  '{<json body>}';   
    l_response clob;
begin  
    apex_web_service.g_request_headers.delete;  
    apex_web_service.g_request_headers(1).name := 'Ocp-Apim-Subscription-Key';
    apex_web_service.g_request_headers(1).value := '<key value>';
    v_url := '<url>'; 
    l_response := apex_web_service.make_rest_request(
                             p_url        => v_url,   
                             p_http_method => 'POST',
                             p_wallet_path => v_wallet_path,
                             p_wallet_pwd =>'<password>',
                             p_body        => v_body);  
    if apex_web_service.g_status_code = 200 then    --OK    
         --dbms_output.put_line(l_response);
    else    --ERROR?
        dbms_output.put_line('ERROR');
    End If; 
End;

Can someone please help me, I've been thinking about this for weeks. I don’t know where to start. What are the things I need to have, to know and the steps on how to create the page. I know this is a lot but I will really appreciate your help! Thanks in advance also!

Answer 1

This is a very broad question, so my answer is also pretty vague.

I don't think you want to create a function - before the Web Source module was introduced, this kind of thing was often done in an on-submit page process. In your process you'd need to:

1. Call the web API, pass in your search term, and get back a response. The old way to do this was with UTL_HTTP, but the newer APEX_WEB_SERVICE package made it much easier.
2. Using APEX_COLLECTION, create/truncate a collection and save the response clob into the collection's p_clob001 field.

Edit: here's a code snippet for that

l_clob := apex_web_service.make_rest_request(....);

APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(p_collection_name => 'API_RESPONSE');

APEX_COLLECTION.ADD_MEMBER(
        p_collection_name => 'API_RESPONSE'
        p_clob001 => l_clob);

Then create an interactive report. The source will be a SQL query which will take the collection's clob, parse it as JSON, and convert into a tabular format (rows and columns) using JSON_TABLE.

Edit: add example

SELECT jt.id, jt.name
FROM APEX_collections c
cross join JSON_TABLE(
        clob001,  -- the following lines depend on your JSON structure
        '$[*]',  
        columns(
            id number path '$.id',
            name varchar2(10) path '$.name')
    ) jt
WHERE collection_name = 'API_RESPONSE'

Alternately, you could parse the clob using JSON_TABLE as part of your page process, save the output table into a collection using APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY, and then just query that collection for your interactive report.

Edit: I'm not sure if this would work, but something like:

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
    p_collection_name => 'API_RESPONSE',
    p_query => 'SELECT t.id, t.name
    FROM JSON_TABLE(
            l_clob,
            ''$[*]'',
            columns(
                id number path ''$.id'',
                name varchar2(10) path ''$.name'')
        ) t');

Side note: as a very different option, you could also call the web service using JavaScript/jQuery/AJAX. I think this would be more complicated, but it's technically possible.