CODEWITHSUNDEEP
oracle-databaseplsqloracle-apex
joek
joek

Reputation: 39

How to access a collection in apex web service module response body

Objective: Present APEX Web Service Module data from a rest service (http://dev.wealthbox.com/#activity-stream-retrieve-activity-stream-get as an example with contact data) in an interactive grid, report, and/or form that includes json collections. In the referenced contact example, repeating data such as tags and phone numbers.

Environment: Oracle Cloud Autonomous Transaction Processing 19c APEX v20

Problem 1: Sccessfully created a Web Service Module, and a page with an interactive grid that references the web service module data. But the IG region columns do not include reference to the collection data, such as tags and phone numbers. The data does appear in the Web Service Module Response Body so I know it is bringing the data back.

Problem 2: When I try accessing the data using PL/SQL, as shown in the example below no data is returned.

 Declare 
    l_Var  Varchar2(3000);
 Begin
 l_Var := apex_web_service.make_rest_request
    (
    p_url => 'https://api.crmworkspace.com/v1/contacts',
    p_http_method => 'GET',
    p_parm_name => apex_util.string_to_table('ACCESS_TOKEN'),
    p_parm_value => apex_util.string_to_table('NotMyRealToken')
    );
Dbms_output.put_line('*' || l_Var);

End;

What am I doing wrong?

Thanks Joe

Upvotes: 0

Views: 1230

Answers (1)

Carsten
Carsten

Reputation: 1124

Answer to "Problem 1":

Web Source Modules in APEX always treat the REST API's response as a "table" with rows and columns - as the APEX components expect it. So Web Source Module can process one array in the response - which makes up the rows. Within a row, arrays cannot be processed, since that would require a "nested" table. You can manually amend the Data Profile of your Web Source module and include specific array elements:

  • Edit the Data Profile
  • Add a column
  • Provide a name, a data type and the Selector. The selector then uses JSON array syntax, e.g. orderItems[0]

You can use Web Source Modules in PL/SQL as well: https://blogs.oracle.com/apex/apex-181-early-adopter-2-rest-services-and-plsql

Answer to "Problem 2":

If no data is returned, you might have a look into the HTTP status code (apex_web_service.g_status_code). Probably this is 400, 500 or another error response. Walking through the array of response headers can also help

for h in 1 .. apex_web_service.g_response_headers.count loop
    dbms_output.put_line( apex_web_service.g_response_headers(h).name );
    dbms_output.put_line( apex_web_service.g_response_headers(h).value );
end loop;

Upvotes: 0

Related Questions