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!

where/what exactly is this 'API' you will be working with for your JSON? — thatjeffsmith
– thatjeffsmith, Commented Dec 14, 2021 at 16:06
I’m sorry but i’m not sure if get your question. It’s REST API. And my request is “POST” — xstitch
– xstitch, Commented Dec 14, 2021 at 16:12
Yeah but we don't know anything about your API...is it ords/Oracle or something completely external to oracle? — thatjeffsmith
– thatjeffsmith, Commented Dec 14, 2021 at 16:13
Then have a look at the external web source feature docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/… — thatjeffsmith
– thatjeffsmith, Commented Dec 14, 2021 at 16:40
if you want to hand-write a ton of plsql code, sure...but why do that when you can just take advantage of this feature? — thatjeffsmith
– thatjeffsmith, Commented Dec 14, 2021 at 16:46

kfinity · Accepted Answer · 2021-12-16 17:22:49Z

2

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:

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.
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.

edited Dec 16, 2021 at 17:22

answered Dec 14, 2021 at 16:47

kfinity

9,1411 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

xstitch Over a year ago

Hi! Thank you so much this is enough for me. I'll do it step by step. I'm done with the first step. Just need to parse it and I think I'm done.

xstitch Over a year ago

Hi, i need your help. Unfortunately I can't create post new question. I'm having a problem with the APEX collection. Am I just going to create a collection with p_clob001 field only or i have to include all the json parameter(body) in the collection?

kfinity Over a year ago

I edited to add some examples, see if that helps

xstitch Over a year ago

Hi! Thank you so much! I’ll follow your guides.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related