12 Replies Latest reply on Nov 3, 2018 12:53 AM by partlycloudy

    IG Dynamic Action - REST API

    partlycloudy

      APEX 18.2

       

      The Sample Interactive Grid has an example of using Dynamic Actions. I modified the example to add a Change handler on the Salary column that does the following

      1. Take the least significant digit of the salary (sal%10)

      2. Pass that to a dummy REST API like this one

      3. Take the output from the REST API and do something with it

       

      I can't figure out how to implement #3 above so I put the REST endpoint in a DIV instead.

       

      It is an admittedly contrived example but the idea is to use client side credentials to do a GET/POST to a REST endpoint using some data from the IG model and do something with the response.

       

      The REST capabilities that APEX provides are nice but the problem is that the Oracle database acts as the client and not the logged in user so managing credentials and passing them along to the REST endpoint is a hassle, especially with Windows/Kerberos in an Intranet type environment. Using jQuery $.ajax (or apex.server.process?) instead seems easier.

       

      Does this make sense? Any ideas appreciated.

       

      Thanks

       

      Update: Made some progress, let me know if I am on the wrong track.

       

      var val=$(this.triggeringElement).val();
      var post_id=val%10;
      var rest_url='https://jsonplaceholder.typicode.com/posts/'+post_id;
      
      $.ajax({
         type: "GET",
         url: rest_url,
         dataType: "json",
         success: 
            function(data){
               $('#rest_return').html(JSON.stringify(data));
            }
      });
      
        • 1. Re: IG Dynamic Action - REST API
          Pavel_p

          Hi,

          if "something" means for instance to write the parsed JSON value to the debug trace, you can do it this way (assuming we have an editable IG based on DEPT table with DEPT column among the Item(s) to submit):

          declare
            l_resp      clob;
            l_post_id  number := :DEPTNO;
            l_values    apex_json.t_values;
            l_user_id  number;
            l_title    varchar2(1000);
          begin
          
            apex_debug.message('l_post_id = %d, deptno = %s', l_post_id , :deptno);
            l_resp := apex_web_service.make_rest_request(p_url => 'http://jsonplaceholder.typicode.com/posts/' || l_post_id,
                                                        p_http_method => 'GET');
          
          apex_json.parse(p_values => l_values,p_source => l_resp);
            l_user_id := apex_json.get_number(p_values => l_values,p_path => 'userId');
          
          
            l_title := apex_json.get_varchar2(p_values => l_values,p_path => 'title');
          
            apex_debug.message('userId=%d,  title: %s', l_user_id , l_title);
          end;
          

          It looks like uneditable IG does not take into account Item(s) to submit and simply does not submit anything (for instance when the DA is bound to its Selection Change event), however it would be possible to get the selected row/value from the model in JS and $s() assign it to some hidden item and the rest would be the same. Also note that I'm calling the plain http method to avoid painful messing with Oracle Wallet (also highly dependent on DB version). Also if the DB version 12.2+, it's better to use its native JSON capabilities to parse the returned JSON object and do something with it.

          I wrote it server-side as it seems that "something" should be done server-side anyway (just an assumption as you mentioned apex.server.process), moreover to invoke the REST service server-side seems to be more reliable as you can probably never guarantee that all the clients will have access to the target site. Also it should be possible to get client credentials from CGI env variables (Re: Re: Windows Integrated Authentication - HOWTO btw, great writeup).

          If I'm missing something, please, describe your requirements in more detail.

          Regards,

          Pavel

          • 2. Re: IG Dynamic Action - REST API
            partlycloudy
            if "something" means for instance to write the parsed JSON value

             

            Yes, depending on what the REST endpoint returns (e.g. Just a HTTP status code and/or content/payload), I would like to parse out the elements of the response/payload and update UI elements on the page accordingly. But I think I have a handle on this.

             

            moreover to invoke the REST service server-side seems to be more reliable as you can probably never guarantee that all the clients will have access to the target site. Also it should be possible to get client credentials from CGI env variables

             

            I'm not so sure. Over the past few years, I have had several (PM) conversations with Kris, Christian, Colm and Joel about this and  I don't think there is an easy way to make server-side API calls using APEX_WEB_SERVICE and pass along client credentials. I could be wrong. That's why I went down the path of using $.ajax which seems to be a viable option. Browser/client not having network connectvity to the REST endpoint is a good point, will keep that in mind.

             

            I am waiting for details on what kind of authentication & authorization the REST endpoint needs, will update this thread shortly.

             

            btw, great writeup

             

            Thanks!

            • 3. Re: IG Dynamic Action - REST API
              Alli Pierre Yotti

              Pavel_p write:

               

              Also if the DB version 12.2+, it's better to use its native JSON capabilities to parse the returned JSON object and do something with it.

               

               

              I agree with you. I alway suggest to use the native JSON capabilities when started with  Version 12.1.0.2.

              Because The JSON Types for PL/SQL Use the object-oriented functions of Oracle Database as opposed to APEX_JSON, which is more procedural

               

              declare
                  l_resp       clob;
                  l_post_id    number := :DEPTNO;
                  l_object     json_object_t;
                  l_key_list   json_key_list;
              begin
                  l_resp       := apex_web_service.make_rest_request(
                      p_url           => 'http://jsonplaceholder.typicode.com/posts/' || l_post_id,
                      p_http_method   => 'GET'
                  );
                  l_object     := json_object_t.parse(l_resp);
                  l_key_list   := l_object.get_keys;
                  for counter in 1..l_key_list.count loop
                      dbms_output.put_line(l_key_list(counter)|| ' = '|| l_object.get_string(l_key_list(counter) ) );
                  end loop;
              end;
              
              • 4. Re: IG Dynamic Action - REST API
                Alli Pierre Yotti

                partlycloudy wrote:

                 

                I'm not so sure. Over the past few years, I have had several (PM) conversations with Kris, Christian, Colm and Joel about this and  I don't think there is an easy way to make server-side API calls using APEX_WEB_SERVICE and pass along client credentials. I could be wrong. That's why I went down the path of using $.ajax which seems to be a viable option. Browser/client not having network connectvity to the REST endpoint is a good point, will keep that in mind.

                 

                you thing it is not possible? Sorry that i should ask again.

                If the server-Side Rest API support basic or Oauth2 Authentication then you can call the API use APEX_WEB_SERVICE and pass the Authorization Code or

                the Client Credentials. I don't think that $.ajax is the best way to call secured Rest Service. Maybe i don't understand what you try to achieve. If so, than give more Details

                • 5. Re: IG Dynamic Action - REST API
                  partlycloudy
                  If the server-Side Rest API support basic or Oauth2 Authentication

                   

                  No, as stated, if the REST API endpoint only supports Windows authenentication in an intranet-environment, a Windows/.NET/C#/IIS-based application stack is able to handle this effortlessly but Oracle/APEX, being a server-side engine, is unable to do Kerberos negotiation.

                  • 6. Re: IG Dynamic Action - REST API
                    Pavel_p

                    With native capabilities I meant something like this

                    with resp as (
                      select '{
                      "userId": 1,
                      "id": 1,
                      "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
                      "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
                    }'
                      json_resp
                      from dual
                    ) select json_value(r.json_resp,'$.userId') user_id,
                            json_value(r.json_resp,'$.title') title
                      from resp r;
                    

                    because it's natively compiled to DB kernel and as such it's always faster and more efficient than PL/SQL. I may be wrong but in my opinion this was implemented in 12.2.

                    • 7. Re: IG Dynamic Action - REST API
                      Alli Pierre Yotti

                      Pavel wrote:

                       

                      because it's natively compiled to DB kernel and as such it's always faster and more efficient than PL/SQL. I may be wrong but in my opinion this was implemented in 12.2.

                       

                      Yes but json_value only return scalar values, not complex values like nested records or arrays. An attempt to return a non-scalar value results in NULL.

                       

                      Is json_table no native? It come started with 12.1.0.2

                      https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973

                       

                      select userid,
                             id,
                             title,
                             body
                        from
                          json_table ( '{  
                        "userId": 1,  
                        "id": 1,  
                        "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",  
                        "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"  
                      }'
                      ,'$'
                              columns (
                                  userid varchar2 path '$.userId',
                                  id varchar2 path '$.id',
                                  title varchar2 path '$.title',
                                  body varchar2 path '$.body'
                              )
                          );
                      
                      • 8. Re: IG Dynamic Action - REST API
                        partlycloudy

                        Alli is right, Oracle 12.1 added some native JSON features to SQL and 12.2 added even more. As always, Tim Hall does a great job unpacking it for us

                         

                        https://oracle-base.com/articles/12c/json-support-in-oracle-database-12cr1

                        https://oracle-base.com/articles/12c/json-support-in-oracle-database-12cr2

                        • 9. Re: IG Dynamic Action - REST API
                          Pavel_p

                          Thanks!

                          No, thanks to you! Actually the whole world should be grateful to you for writing it and it definitely deserves some more honorable place than to be hidden somewhere in the ORDS forum. I really struggled Get Windows username into APEX variable  with SSO implementation and at the end I wrote an authentication applet (at the times when applets were still somehow usable and generally acceptable).

                          But anyway, if the web service (eventually) only supports some arcane windows-specific authentication, then it does not deserve to be invoked at all.

                          But back to the point - it's obviously not a problem to invoke the service neither client-side using $.ajax call nor server-side in PL/SQL, so I'm probably missing what the problem exactly is right now.

                          • 10. Re: IG Dynamic Action - REST API
                            Pavel_p

                            OK, I'm sorry, obviously I was wrong.

                            • 11. Re: IG Dynamic Action - REST API
                              partlycloudy
                              But anyway, if the web service (eventually) only supports some arcane windows-specific authentication, then it does not deserve to be invoked at all

                              That's what I said but that's above my pay-grade :-)

                              it's obviously not a problem to invoke the service neither client-side using $.ajax call nor server-side in PL/SQL, so I'm probably missing what the problem exactly is right now.
                              I am waiting for details on what kind of authentication & authorization the REST endpoint needs, will update this thread shortly.

                              Yup, I think I am fine for now, thanks for chiming in.

                              • 12. Re: IG Dynamic Action - REST API
                                partlycloudy

                                One more question...if the APEX page were to have a File Browse input, I can use the HTML5 FormData API to POST the file contents to the API using something like this.  I googled around a bit and while there are several web hook, post bin type ephemeral sites that echo back anything you POST, I couldn't find one that reconstructs and displays binary content (e.g. image, PDF, Word document, etc.) sent via  the FormData API.  Any ideas?

                                 

                                Here is a jsFiddle just to play along with me. I realize that this is dangerously close to being off-topic for this forum since it is really all about jQuery, HTML and web technologies in general but please bear with me!

                                 

                                Thanks