6 Replies Latest reply on Jan 22, 2019 2:35 PM by Abha G

    Calling the Oracle EBS standard webservice in oracle apex using apex_web_service.make_rest_request

    Abha G

      Hi,

      I am trying to call the EBS REST webservice 'assign_role' from Oracle APEX. I have deployed this webservice in EBS and got the WADL link.

      Now, with this link I am able to successfully call the webservice using Postman tool by providing below JSON message :

        {

              "InputParameters": {

                  "P_REGISTRATION_DATA": [{

                          "P_REGISTRATION_DATA_ITEM": [{

                                  "ATTR_NAME": "requested_for_user_id",

                                  "ATTR_VALUE": 4901

                              },

                              {

                                  "ATTR_NAME": "wf_role_name",

                                  "ATTR_VALUE": "FND_RESP|XXA|CUSTOM_ADFIN|STANDARD"

                              },

                              {

                                  "ATTR_NAME": "requested_start_date",

                                  "ATTR_VALUE": "2019/01/09 15:00:00"

                              },

                              {

                                  "ATTR_NAME": "justification",

                                  "ATTR_VALUE": "TEST BRLEBRUYN"

                              }

                          ]

                      }

                  ]

              }

          }

       

      But when I am trying to call the APEX_WEB_SERVICE.make_rest_request process, I am not sure, how can I provide the list of parameters (p_parm_name), as the parameters are of Table type. (The webservice internally calls standard EBS ORacle package UMX_REGISTRATION_PVT.assign_role). I am trying to call it as below :

      apex_web_service.make_rest_request(

                p_url => 'https://ofd.ntwerpen.be/webservices/rest/XXUA_UMX_PUB/assign_role/',

                p_http_method => 'POST',

                p_parm_name => apex_util.string_to_table('requested_for_user_id:wf_role_name:requested_start_date:justification'),             

                p_parm_value => apex_util.string_to_table(l_user_id||':'||l_role_name||':'||l_requested_start_date||':'||l_justification));

       

      Can somebody please help me write the correct way to call this procedure. I believe, If I am able to give the parameters name correctly in

                p_parm_name => apex_util.string_to_table('requested_for_user_id:wf_role_name:requested_start_date:justification'),   

      then it will work fine.

      Please suggest, the proper way to give the parameter list here .

      Thanks

        • 1. Re: Calling the Oracle EBS standard webservice in oracle apex using apex_web_service.make_rest_request
          Pavel_p

          Hi,

          it looks like in the first case you're sending JSON object in the request body while in your PL/SQL code you tried to specify parameters as name-value pairs, so you're invoking some (probably non-existing) URL which looks like

          https://ofd.ntwerpen.be/webservices/rest/XXUA_UMX_PUB/assign_role?param1=value1&param2=value2...etc 
          

          I assume (from the description you provided) you need to send a JSON in the request body like this

          apex_web_service.make_rest_request(
                    p_url => 'https://ofd.ntwerpen.be/webservices/rest/XXUA_UMX_PUB/assign_role/',
                    p_http_method => 'POST',
                    p_body => '
                      {
                       "InputParameters":{
                          "P_REGISTRATION_DATA":[
                             {
                                "P_REGISTRATION_DATA_ITEM":[
                                   {
                                      "ATTR_NAME":"requested_for_user_id",
                                      "ATTR_VALUE":4901
                                   },
                                   {
                                      "ATTR_NAME":"wf_role_name",
                                      "ATTR_VALUE":"FND_RESP|XXA|CUSTOM_ADFIN|STANDARD"
                                   },
                                   {
                                      "ATTR_NAME":"requested_start_date",
                                      "ATTR_VALUE":"2019/01/09 15:00:00"
                                   },
                                   {
                                      "ATTR_NAME":"justification",
                                      "ATTR_VALUE":"TEST BRLEBRUYN"
                                   }
                                ]
                             }
                          ]
                       }
                    }'
                    );
          

          (assuming you have properly imported the site's certificate into the wallet Re: utl_http.request on ssl site fails with ORA-29024 ).

          As an aside, functions apex_util.string_to_table/table_to_string have been deprecated https://docs.oracle.com/database/apex-5.1/AEAPI/STRING_TO_TABLE-Function.htm#AEAPI185 , however the good thing is that parameter names&values can be specified one by one Re: STRING_TO_TABLE CLOB ).

          Regards,

          Pavel

          1 person found this helpful
          • 2. Re: Calling the Oracle EBS standard webservice in oracle apex using apex_web_service.make_rest_request
            Abha G

            Hello Pavel,

             

            Thanks a lot for the information. It makes sense to use the p_body parameter rather than p_parm_name and p_parm_value.

            Can you please also explain more about the wallet. I assume that what you are trying to say is that I need to create a wallet and pass that information as well while calling APEX_EBS_SERVIC.make_rest_request.

             

            When I deployed the webservice I gave the username and password, which I was using while I was calling this webservice from Postman. Similarly, I should have some authentication here as well...right ?

             

            This is the final code that I am writing :

             

            apex_web_service.g_request_headers(1).name := 'Content-Type';

            apex_web_service.g_request_headers(1).value := 'application/json';

              

            l_clob := apex_web_service.make_rest_request(

                          p_url => 'https://ofd.uantwerpen.be/webservices/rest/XXUA_UMX_PUB/assign_role/',

                          p_http_method => 'POST',

                          p_body => ' 

                        { 

                         "InputParameters":{ 

                            "P_REGISTRATION_DATA":[ 

                               { 

                                  "P_REGISTRATION_DATA_ITEM":[ 

                                     { 

                                        "ATTR_NAME":"requested_for_user_id", 

                                        "ATTR_VALUE":'||l_user_id ||'

                                     }, 

                                     { 

                                        "ATTR_NAME":"wf_role_name", 

                                        "ATTR_VALUE":'|| :P2_ROLE ||'

                                     }, 

                                     { 

                                        "ATTR_NAME":"requested_start_date", 

                                        "ATTR_VALUE":"2019/01/18 15:00:00" 

                                     }, 

                                     { 

                                        "ATTR_NAME":"justification", 

                                        "ATTR_VALUE":'||:P2_JUSTIFICATION||'

                                     } 

                                  ] 

                               } 

                            ] 

                         } 

                      }'

              );

             

            But, when I am calling this code with p_body included, It doesn't give any error runs successfully, but doesn't do what it is supposed to do, i.e insert in the tables. Is it related to Wallet ?

             

            Regards,

            Abha

            • 3. Re: Calling the Oracle EBS standard webservice in oracle apex using apex_web_service.make_rest_request
              Pavel_p

              In order to invoke any https service you need to import site's certificate into oracle wallet (please, read the thread from my previous post for more details), however since you're able to invoke the service without getting errors, we can quite safely assume that this part is done (otherwise you would be getting errors coming from utl_http package which apex_rest_service uses under the hood).

              The site probably uses simple authentication and you need to specify username and pwd the same way you did in Postman. Also it's a good idea to inspect what the web service call returned, i.e. check the response code and what contains the clob returned by the service. So the code should look like (untested, so some minor fixes might be needed):

              declare
                l_clob clob;
              begin
                apex_web_service.g_request_headers(1).name := 'Content-Type';
                apex_web_service.g_request_headers(1).value := 'application/json';
                l_clob := apex_web_service.make_rest_request(
                  p_url          => 'https://ofd.uantwerpen.be/webservices/rest/XXUA_UMX_PUB/assign_role/',
                  p_http_method  => 'POST',
                  p_username      => 'username',
                  p_password      => 'pwd',
                  p_body          => ' 
                          { 
                          "InputParameters":{ 
                              "P_REGISTRATION_DATA":[ 
                                { 
                                    "P_REGISTRATION_DATA_ITEM":[ 
                                      { 
                                          "ATTR_NAME":"requested_for_user_id", 
                                          "ATTR_VALUE":'
                  || l_user_id || '
                                      }, 
                                      { 
                                          "ATTR_NAME":"wf_role_name", 
                                          "ATTR_VALUE":'
                  || :p2_role || '
                                      }, 
                                      { 
                                          "ATTR_NAME":"requested_start_date", 
                                          "ATTR_VALUE":"2019/01/18 15:00:00" 
                                      }, 
                                      { 
                                          "ATTR_NAME":"justification", 
                                          "ATTR_VALUE":'
                  || :p2_justification || '
                                      } 
                                    ] 
                                } 
                              ] 
                          } 
                        }'
                );
              
                if apex_web_service.g_status_code = 200 then --OK 
                  continue_with_normal_processing();
                else
                  dbms_output.put_line('REST call failed with status code ' || apex_web_service.g_status_code); 
                  --since APEX 18.2 there is also a g_reason_phrase 
                  dbms_output.put_line('Reason phrase: ' || apex_web_service.g_reason_phrase);
                end if;
              
                --and check what contains the
                dbms_output.put_line('l_clob contains: ' || dbms_lob.substr(l_clob,32767));
              end;
              

              I would suggest to run the anonymous block from the SQL workshop (in case of SQL Developer and other clients you might need to set also p_wallet_path and p_wallet_pwd parameters, in this case the wallet seems to be set on APEX instance level) and let's see what happens

              1 person found this helpful
              • 4. Re: Calling the Oracle EBS standard webservice in oracle apex using apex_web_service.make_rest_request
                Abha G

                Hello Pavel,

                 

                This was perfect. I managed to call the webservice successfully by passing in the p_username and p_password. Thanks a lot for your help.

                It worked with username and password but i believe it will be better to create a wallet for this webservice and then just pass the p_wallet_path and p_wallet_pwd in the parameters. It should be fine..right ?

                 

                I have one more question if you can help me with that..Here I have used apex_web_service.make_rest_request package to call the webservice from APEX. But there is also "apex_exec.execute_web_source" where we can create a web source reference and then access that websource in this package call. Can I use that here as well. Which one is a better option to choose ?

                 

                Thanks and regards,

                Abha

                • 5. Re: Calling the Oracle EBS standard webservice in oracle apex using apex_web_service.make_rest_request
                  Pavel_p

                  Hi Abha,

                  good to hear that it worked for you.

                  It worked with username and password but i believe it will be better to create a wallet for this webservice and then just pass the p_wallet_path and p_wallet_pwd in the parameters.

                  The wallet is used for storing site certificates and p_wallet path and p_wallet_pwd are used to access the wallet. These parameters can be either specified in the web service call or may be set at APEX instance level by the admin (which seems to be your case) and this configuration is taken by default if p_wallet_path and pwd parameters are omitted (so devs do not have to care about it and also wallet credentials are not exposed). But the wallet credentials may be different (and usually are different) than the credentials being sent to the webservice via p_username and p_password parameters, so I'm afraid you cannot store everything in the wallet.

                  You can use both, however I believe that apex_exec.execute_web_source calls apex_web_service under the hood anyway. I think there is no "better" option - it just depends on your preferences and if you want to have more control over the entire process or write less code and let APEX do everything for you. I think in the web source module you can specify most of the things declaratively (I must admit that I'm not that familiar with the new web source modules yet) but gives you less options to track what is happening and eventually why the things went wrong. I personally prefer to write the call "manually" but your preferences may differ, so I would suggest to try both and then make your choice.

                  Regards,

                  Pavel

                  • 6. Re: Calling the Oracle EBS standard webservice in oracle apex using apex_web_service.make_rest_request
                    Abha G

                    Thanks for the detailed explanations. Really appreciate your help.