14 Replies Latest reply on Jan 13, 2017 1:00 PM by Umbdae Arthur

    Use RESTful web service as data source for Apex 5.0/5.1 list of values?

    Umbdae Arthur

      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.

       

      Thanks in advance!

       

      Kurt Arthur

        • 1. Re: Use RESTful web service as data source for Apex 5.0/5.1 list of values?
          fac586

          3315891 wrote:

          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

          • 2. Re: Use RESTful web service as data source for Apex 5.0/5.1 list of values?
            Umbdae Arthur

            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?

            • 4. Re: Use RESTful web service as data source for Apex 5.0/5.1 list of values?
              Carsten Czarski-Oracle

              Hi Kurt,

               

              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

               

              -Carsten

              1 person found this helpful
              • 5. Re: Use RESTful web service as data source for Apex 5.0/5.1 list of values?
                Umbdae Arthur

                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!

                 

                KA

                • 6. Re: Use RESTful web service as data source for Apex 5.0/5.1 list of values?
                  Pavel_p

                  Hi,

                  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.

                  Regards

                  Pavel

                   

                  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
                  • 7. Re: Use RESTful web service as data source for Apex 5.0/5.1 list of values?
                    Carsten Czarski-Oracle

                    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.

                     

                    Best regards

                     

                    -Carsten

                    1 person found this helpful
                    • 8. Re: Use RESTful web service as data source for Apex 5.0/5.1 list of values?
                      Pavel_p

                      Hi Carsten,

                      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.

                      Regards,

                      Pavel

                      • 9. Re: Use RESTful web service as data source for Apex 5.0/5.1 list of values?
                        Umbdae Arthur

                        Carsten and Pavel:

                         

                        Thanks again for your comments.  I really feel like I have a "getting started" point with this issue

                         

                        Kurt

                        • 10. Re: Use RESTful web service as data source for Apex 5.0/5.1 list of values?
                          Umbdae Arthur

                          Here is what I determined based on Carsten and Pavel's help:

                           

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

                           

                          declare

                            applications  clob  := null ;

                            application_id number := null ;

                          begin

                            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 )

                                                                        ) ;

                          end ;

                           

                          2. Add the LOV to the form.  In the List of Valus attributes, select "SQL Query" and enter a query similar to the following:

                           

                          select xtr.application

                              , xtr.description

                            from apex_collections col

                              , xmltable(

                                        '$root/ROWSET/ROW'

                                        passing col.xmltype001 as "root"

                                        columns application varchar2( 4000 )  path 'LOOKUP_CODE'

                                              , description varchar2( 4000 )  path 'DESCRIPTION'

                                        ) xtr

                          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.

                          • 11. Re: Use RESTful web service as data source for Apex 5.0/5.1 list of values?
                            Pavel_p

                            Hi Kurt,

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

                            Regards,

                            Pavel

                            • 12. Re: Use RESTful web service as data source for Apex 5.0/5.1 list of values?
                              Carsten Czarski-Oracle

                              Hi Pavel,

                               

                              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.

                               

                              Best regards

                               

                              -Carsten

                              • 13. Re: Use RESTful web service as data source for Apex 5.0/5.1 list of values?
                                Pavel_p

                                Hi Carsten,

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

                                Best regards,

                                Pavel

                                • 14. Re: Use RESTful web service as data source for Apex 5.0/5.1 list of values?
                                  Umbdae Arthur

                                  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.

                                   

                                  Thanks!!