Please update your forum profile with a recognisable username instead of "3315891": Video tutorial how to change nickname available
Can anyone provide an example of a list of values that uses a RESTful web service as its data source?
I've seen an old question from 2012 on this topic, and the "solution" was to use a pipelined function to
call the service and return the data.
Please tell me that a pipelined function is not the only mechanism to provide this functionality.
A pipelined function is not the only mechanism that can be used to provide this functionality.
The potential implementation options depend on various factors, such as database and APEX version, the payload format of the service, whether security permits the database to make the web service request etc: how to get answers from forum
The database is 12.1, the Apex version is 5.0 (although I can go to 5.1 if needed). The payloads are JSON and XML and security does allow the DB to call the web service.
What mechanism(s) allow what I need? Where can I find documentation or example code?
Is the service HTTP or HTTPS?
1 person found this helpful
please have a look at the new Packaged Application "REST Client Assistant". You might add a "REST Service Reference" for your service in that application. After configuring it, it will generate the SQL queries which parse the JSON data to a SQL result set. It works on 11g and 12c. While the REST Client Assistant only works in 5.1, the generated SQL queries can, of course, be uses in 5.0 as well.
You should not need to author a table function yourself.
I hope that helps
The service is currently HTTP, but may (will?) migrate to HTTPS before deployment to production.
Carsten, thanks for your tip about the REST Client Assistant. We don't have 5.1 installed as yet, but that is more impetus to do so.
If anyone has other ideas or a pointer to examples or documentation, I would certainly appreciate it!
1 person found this helpful
I would suggest to create a before header process and in this process to make the REST call, then create an APEX collection and populate it with the retrieved data (if your service returns xml or text data, it can be done fully declaratively, in case of JSON a bit of coding is needed - I'm not familiar with the REST Client Assistant yet, so probably this app could help you with JSON parsing). Then create a LOV based on your APEX collection (standard select statement). If you strongly needed a sample app, I think I could create something for you.
edit: please, install the sample Packaged Application Sample REST Services (if you don't have access to APEX 5.1, create an account on https://apex.oracle.com/en/ ), go to page 2 and see the Fetch data into Collection page process - this is a perfect example how to make a REST call and insert returned values into APEX collection
1 person found this helpful
Hi Pavel and Arthur,
.... and to add: The SQL and PL/SQL code within the Sample Rest Services application was generated with ... (guess what?): The REST Client Assistant. So you might use that code as a template for your final production code.
it looks like the REST Client Assistant is definitely worth of deeper investigation.
I haven't dug too deep into the new apps yet but from what I've seen so far I'm a bit confused how JSONs are handled. Always the returned JSON is converted to xmltype and then data are selected using xquery. I'm just spinning my head why it was "necessary" to remove xml support from ORDS (it used to be there but not anymore) if APEX itself cannot handle JSON (OK, we have the apex_json package and all of this is probably done because of 11g compatibility since 12c has a native JSON support). JSON to xmltype conversion seems to be a pretty resource-consuming operation and I just wonder if it would not be easier to return xmltypes. I asked here Create xml web service few months ago but with no official response.
Carsten and Pavel:
Thanks again for your comments. I really feel like I have a "getting started" point with this issue
Here is what I determined based on Carsten and Pavel's help:
- In After Header, create a process to call the web service and populate an APEX collection with the results. The code will look something like:
applications clob := null ;
application_id number := null ;
applications := apex_web_service.make_rest_request(
p_url => :WEB_SERVICE_NAME
, p_http_method => 'GET'
apex_collection.create_or_truncate_collection( 'APPLICATIONS' ) ;
application_id := apex_collection.add_member(
p_collection_name => 'APPLICATIONS'
, p_xmltype001 => xmltype.createXML( applications )
2. Add the LOV to the form. In the List of Valus attributes, select "SQL Query" and enter a query similar to the following:
from apex_collections col
passing col.xmltype001 as "root"
columns application varchar2( 4000 ) path 'LOOKUP_CODE'
, description varchar2( 4000 ) path 'DESCRIPTION'
where 1 = 1
and col.collection_name = 'APPLICATIONS'
That's all there is to it! Note the default LOV control in 5.0 seems to only accept the first column of the select as its data, but that is OK.
Many thanks again to Carsten and Pavel! I spent several days looking for this answer and once they replied, I found it within a short period.
in the sample app there is used a general and versatile approach - it simply always takes the entire returned clob and converts it to xmltype, inserts it into a collection xmltype column and then data are retrieved via xquery. I would rather suggest to parse directly the returned clob with apex_json.parse https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_json.htm#AEAPI29747 and insert already parsed values into two columns of APEX collection as name-value pairs. I think it could be a more efficient way, especially if you're going to make the REST request once and then query data multiple times and also your LOV query will be more readable (thus easier maintenance).
if you run the REST Client assistant on a 12c database, it will not follow the XML approach but use the JSON_TABLE native SQL function in order to parse the JSON. So on a 12c database, JSON parsing is faster by magnitudes. I would use APEX_JSON only on 11g, where no native functions are available. I also did some comparison on procedural JSON parsing with APEX_JSON and the "convert to XML and use XML functions" approach. Result was, that the performance was more or less the same. The largest part of time is for parsing the JSON itself (APEX_JSON.PARSE vs. APEX_JSON.TO_XMLTYPE). The SQL/XML parsing functions (XMLTABLE) are - again - native functions and very, very fast.
In general, I see the SQL based JSON parsing even as more maintainable, since that JSON can be used in any APEX component or very easily copied into a table. No procedural code is required; all done with SQL - and, of course, you can use the parsing query as a subquery and do additional SQL stuff with the data ...
The REST Sample Application uses the XML approach since it has to run on 11g and 12c.
I hope that helps.
thanks a lot for further explanation and performance comparison. I was going to make some performance tests myself but since you've already done it, I think I can trust your results.
To be honest, after this experience Re: Select start_with-connect by from XML document (looks like a bug in 11.2g) I would rather avoid any XML querying if possible and I definitely prefer parsing JSONs, even if it requires a bit of coding (at least I can see all the time what's going on under the hood). I'm really looking forward to 12 XE (because of more reasons, mainly native JSON and support for SHA-2 based certificates).
That's what's great about this forum: not only do we get answers that work, we get answers that are better and why they are.