Query an address from a Web Source Module [APEX 18.1]

In this example, we will learn how to create and consume data from a Web Source Module in Oracle Application Express (APEX) 18.1. It is very simple and useful!

What is a Web Source Module in Oracle APEX?

Web Source Modules allow developers to access REST services or JSON data in applications, enabling the use of this data in APEX components such as interactive reports and forms.

Unlike Web Services references, a Web Source Module contains Web Service metadata that can be used in APEX component processes as well as in a PL/SQL block to perform a service call and process the responses

To learn more about Web Source Modules, go to: Application Express App Builder User's Guide.

How create and use a Web Source Module?

In this example, I will demonstrate how to create a Web Source Module by accessing a Web Service that contains address data from Brazil. The Web Service is available for free and is maintained by ViaCEP. Web service access instructions are available on viacep.com.br.

1. To create a Web Source Module go to Shared Components > Data Sources > Web Source Modules and click on Create.

2. In the next step, enter a name of your choice for the Web Source Module and the URL Endpoint and click on Next. In this case, our URL Endpoint is: https://viacep.com.br. Note: The URL must begin with http:// or https://

3. On the next screen, enter the data for the Remote Server. Select the Remote Server, entered in the previous step, and enter the Service URL Path. In this example, we pass a parameter that the zip code and the JSON output format.

4. Now it is necessary to inform if the Web Service needs authentication. In our example, we will not need authentication.

5. We now declare the parameters needed to query the addresses. We'll just need the zip code. We select the Parameter Type, we define a Parameter Name and, just to verify that everything is working correctly, we pass a Value as parameter.

Note: In our example, the parameter values ​​will be dynamic, so we define that it will not be static in the Is Static.

By clicking on Discover, we can see the Data returned based on the parameter we reported, as well as the Data Profile of the Web Service.

6. Once you have verified that the data returned and the Data Profile is correct, click on Create Web Source.

Once this is done, our Web Source Module will be created. We can verify, after its creation, in our Data Profile that the Response Format is JSON and that there are nine columns visible.

7. After creating the Web Source Module, we created a Blank Page named Search Address and added three items to it.

We define the P3_ZIP_CODE item as the Number Field and maximum length of 8 - number of digits required for the Brazilian zip code standard. And other items (P3_ADDRESS and P3_CITY) as Display Only field.

8. We now create a dynamic action for the P3_ZIP_CODE item when the item is changed (Event: Change). The condition for execution must be set to when the item is not null. If the action is true, we execute the following PL/SQL code:

declare

   l_context     apex_exec.t_context;
   l_parameters  apex_exec.t_parameters;
   
   l_logidx      pls_integer;
   l_baridx      pls_integer;
   l_locidx      pls_integer;
   l_ufidx       pls_integer;
   
begin

   apex_exec.add_parameter (
      p_parameters => l_parameters,
      p_name       => 'zipcode',
      p_value      => :P3_ZIP_CODE);
   
   -- Open Web Source
   l_context: = apex_exec.open_web_source_query (
      p_module_static_id => 'Viacep_Web_Source',
      p_parameters       => l_parameters);
      
   
   l_logidx: = apex_exec.get_column_position (l_context, 'LOGRADOURO');
   l_baridx: = apex_exec.get_column_position (l_context, 'BAIRRO');
   l_locidx: = apex_exec.get_column_position (l_context, 'LOCALITY');
   l_ufidx:  = apex_exec.get_column_position (l_context, 'UF');
   
   
   while apex_exec.next_row (l_context) loop
      
      if apex_exec.get_varchar2 (l_context, l_logidx) is null or 
         apex_exec.get_varchar2 (l_context, l_locidx) is null then
         : P3_ADDRESS: = null;
         : P3_CITY: = null;
      else
         : P3_ADDRESS: = apex_exec.get_varchar2 (l_context, l_logidx) || 
                         ',' || apex_exec.get_varchar2 (l_context, 
                         l_baridx);
         : P3_CITY: = apex_exec.get_varchar2 (l_context, l_locidx) || 
                      '('|| apex_exec.get_varchar2 (l_context, l_ufidx) 
                      ||') ';
      end if;
      
   end loop;
   
   -- Close Web Source
   apex_exec.close (l_context);

exception
   when others then
      -- Close Web Source
      apex_exec.close (l_context);
   
end;

In this PL/SQL block we use the OPEN_WEB_SOURCE_QUERY function of the APEX_EXEC package. Note that we use the GET_COLUMN_POSITION functions to get the index of the columns we are retrieving from our Web Source Module. Afterwards, we use the GET_VARCHAR2 function, passing as a parameter the context of the Web Source that we open and the index of the column from where we want to retrieve the value, passing to page items P3_ADDRESS and P3_CITY.

To learn more about APEX_EXEC package, go to: Application Express API Reference.

9. To conclude, we save the changes made to the page and run to see the result.

In this post we could see how working with Web Source Modules in Oracle Application Express (APEX) makes the work of the developer less costly and more agile.

Demo Application

Link: Learning APEX App

User: demo

Password: demo