3 Replies Latest reply on Apr 16, 2019 7:25 AM by Carsten Czarski-Oracle

    how to debug web source modules  and apex_web_service.make_rest_request func?




      Looking for ways to see/debug the actual requests that are made from Apex Web Source Module (Test Operation button press) and apex_web_service.make_rest_request function.


      Database XE 18.0, Apex 18.2, Tomcat 8


      I am trying to query non Oracle (Simple HTTP) REST API that requires POST method with URL and parameters passed in the body i.e. {"apikey": "mykey3434", "request":{}}. The request is HTTPS.


      When using SQL Developer or IR in Apex and calling apex_web_service.make_rest_request from select statement the query returns jason formatted results as expected. However, when setting up the same call via Web Source Modules, the POST call fails. The connection is made, but the server returns an error indicating that a missing or malformed parameters are at fault. Web Source Module is set up as follows: Operations > url pattern=., module base url=https://test.com/eq/list, http method=post, database operation=fetch rows, request body template={"apikey": "mykey3434", "request":{}}. Operations Parameters: Content-Type=application/json as HTTP Header.


      To figure out what is going on I would like to compare the actual two calls, but not even sure if the information is available in DB logs. Any hints/debug ideas are appreciated.


      Thank you!

        • 1. Re: how to debug web source modules  and apex_web_service.make_rest_request func?
          Carsten Czarski-Oracle



          In Application Builder there is no easy way to debug Web Source Modules.


          However, you can from an Application:

          • Create the Web Source Module
          • Create a page with a report (Classic, Interactive)
          • Run the page and enable Debug Mode. To get finest debug output, you can replace the "YES" keyword in the page URL with "LEVEL9"
          • In the Developer Toolbar, click "View Debug".


          I hope this helps


          Best regards



          • 2. Re: how to debug web source modules  and apex_web_service.make_rest_request func?

            Hello Carsten!


            Thanks for a quick response. I did manage to get things working, albeit I believe there is a bug in the web source module.


            LEVEL 9 debugging works to some extent. Debugging messages are still at the very high level + no ability to see the actual return string APX&CLOB_VALUE nor the actual string sent. On the other hand, I was able to confirm that inputs generated from web source module to make_rest_request function are identical. However, the headers are not. Web source module adds User Agent header. I do not think that's a problem, but still.


            The above gave me some confidence to keep trying different things. Ultimately, I discovered that the "Test Operation" does not work in my case with POST method and request string in the body. All I get is a malformed request string error back. However, I am able to use Data Profile -> Rediscover Data Profile button and as long as I have a proper Row Selector specified the request returns proper json. Also, when web source is mapped to a classical report it works as expected.


            Thus, if anybody has a similar case : 1)  make_rest_request call works in SQL developer; 2)  make_rest_request call works as a source to IR; 3) Web Source Module --> Test Operation FAILS, then you can ignore test operation failure and go straight to Data Profile and set it up. I tested this in 18.2 and 19.1.


            As a side note, I noticed that "Apply Changes" button in Data Profile does not take you back to Web Source Module. You have to press Apply Changes and then Cancel. It is not consistent with the Apply behavior on the other pages (Operations, Web Source Module).


            Thanks again for all your help.

            • 3. Re: how to debug web source modules  and apex_web_service.make_rest_request func?
              Carsten Czarski-Oracle



              happy to hear that things are working for you.


              if the Web Source Module does not contain a User-Agent Parameter, APEX adds one with a standard value. The reason for this is that many REST APIs expect the User-Agent header to be set - requests fail if that is not the case. To make working with Web Source Modules as easy as possible, APEX adds the header automatically, if no one is defined. So, in order to have your own User-Agent header, simply define one with your value in Web Source Module (or Operation) parameters.


              Is your REST API available on the internet, so that I could have a look why the "test" did fail ...? Maybe this could be improved - but I would need to have a few more details, then. If you wish, feel free to contact me privately. The Oracle email scheme is {firstname}.{lastname}{at}oracle.com.
              Best regards