APEX Web Source Module - PL/SQL equivalent

APEX Web Source Module - PL/SQL equivalent

Gepubliceerd: Categorie: Oracle

The APEX Web Source Module allows you to define a Web Source in a declarative way. No programming is needed, and an Interactive Report based on a Web Source looks the same as one based on a database table. There could be situations where you want to have more control on what happens on the output of the Web Source. For example: if you want to join the results of the Web Source with a table in your own database. Or if you want to use the Web Source output in a dynamic action.

Oracle APEX and the JSON_TABLE function allows you to query the Web Source and use the results in a SQL-query. In the following examples, I will show you different ways of how to use the APEX_WEB_SERVICE package.

Example 1

Below is an example of the equivalent Web Source call in a PL/SQL block:

  1. set define off
  2. set serveroutput on
  3. begin
  4. apex_web_service.g_request_headers.delete();
  5. apex_web_service.g_request_headers(1).name := 'Ocp-Apim-Subscription-Key';
  6. apex_web_service.g_request_headers(1).value := '<MY_NS_SUBSCRIPTION_KEY>';
  7. for x in (select code
  8. , name
  9. from json_table(apex_web_service.make_rest_request(p_url => 'https://gateway.apiportal.ns.nl/places-api/v2/places?type=stationV2&countries=NL'
  10. ,p_http_method => 'GET'
  11. )
  12. , '$.payload.locations[*]'
  13. columns(code varchar2(20) path '$.code'
  14. ,name varchar2(200) path '$.name'
  15. )
  16. )
  17. where rownum < 10 -- Limit the result for this example
  18. )
  19. loop
  20. dbms_output.put_line(x.code||' - '||x.name);
  21. end loop;
  22. end;
  23. /
  24.  

You might notice the elements used in the APEX Web Source Module, such as the Authentication headers (apex_web_service.g_request_headers) and the row selector ('$.payload.locations[*]'). Note that you have to code the Data Profile yourself:

  1. (columns(code varchar2(20) path '$.code'
  2. ,name varchar2(200) path '$.name'
  3. )
  4. )
  5.  

Example 2

The previous example is a PL/SQL block. It’s not very useful when you just need a SQL-query. The challenge here is to put the setting of apex_web_service.g_request_headers in the same series as the APEX processing calls where the SQL-statement is executed.

When setting the proper values of apex_web_service.g_request_headers in a PL/SQL block that is called on the Before Regions processing point on the same page where you want to run the SQL-statement, the request headers will have the proper values. And at the right moment.

Now you can use the plain query:

  1. select code
  2. , name
  3. from json_table(apex_web_service.make_rest_request
  4. (p_url => 'https://gateway.apiportal.ns.nl/places-api/v2/places?type=stationV2&countries=NL'
  5. ,p_http_method => 'GET'
  6. )
  7. , '$.payload.locations[*]'
  8. columns(code varchar2(20) path '$.code'
  9. ,name varchar2(200) path '$.name'
  10. )
  11. )
  12.  

Example 3

There is also another possibility: using a WITH-clause in combination with a PL/SQL function.

  1. with
  2. function f_set return number is
  3. begin
  4. apex_web_service.g_request_headers.delete();
  5. apex_web_service.g_request_headers(1).name := 'Ocp-Apim-Subscription-Key';
  6. apex_web_service.g_request_headers(1).value := '<MY_NS_SUBSCRIPTION_KEY>';
  7. return 1;
  8. end;
  9. select code
  10. , name
  11. from json_table(apex_web_service.make_rest_request(p_url => 'https://gateway.apiportal.ns.nl/places-api/v2/places?type=stationV2&countries=NL'
  12. ,p_http_method => 'GET'
  13. )
  14. , '$.payload.locations[*]'
  15. columns(code varchar2(20) path '$.code'
  16. ,name varchar2(200) path '$.name'
  17. )
  18. )
  19. where f_set=1
  20. ;
  21.  

When using this query in APEX, make sure you use WITH_PLSQL hint to avoid the “ORA-32034: unsupported use of WITH clause” error:

Example 4

My final example shows how to put the PL/SQL calls needed for setting the web service request headers in a function. And then use this function in a view:

  1. create or replace function set_ns_api_request_headers return number as
  2. begin
  3. apex_web_service.g_request_headers.delete();
  4. apex_web_service.g_request_headers(1).name := 'Ocp-Apim-Subscription-Key';
  5. apex_web_service.g_request_headers(1).value := '<MY_NS_SUBSCRIPTION_KEY>';
  6. return 1;
  7. end;
  8. /
  9.  
  10. create or replace view train_stations_vw as
  11. select code
  12. , name
  13. from json_table(apex_web_service.make_rest_request(p_url => 'https://gateway.apiportal.ns.nl/places-api/v2/places?type=stationV2&countries=NL'
  14. ,p_http_method => 'GET'
  15. )
  16. , '$.payload.locations[*]'
  17. columns(code varchar2(20) path '$.code'
  18. ,name varchar2(200) path '$.name'
  19. )
  20. )
  21. where set_request_headers=1
  22. and rownum < 10
  23. ;
  24.  
Peter de Boer
Over auteur Peter de Boer

Peter de Boer is een consultant bij Qualogy.

Meer posts van Peter de Boer
Reacties
Reactie plaatsen