1 Reply Latest reply on Jul 17, 2017 6:48 PM by PatrickCimolini

    Call to apex_web_service.make_rest_request fails intermittently (11g, APEX 5.04)

    PatrickCimolini

      Oracle DB: 11g

      APEX:      5.0.4.00.12

      Date:      July 16, 2017

       

       

      Hi,

      The following function returns the following error messages on an intermittent basis (success 10%, failure 90% of the time).

       

       

      Error: ORA-29273: HTTP request failed

      ORA-06512: at "SYS.UTL_HTTP", line 1130

      ORA-29259: end-of-input reached

       

       

      The call to this RESTful service works fine in Postman with exactly the same data.

       

       

      I am puzzled as to why it fails when called by apex_web_service.make_rest_request.

       

       

      The web server and database logs do not contain anything that is related to this call.

       

       

      Does anyone have any insights?

       

       

      Thank you.

       

      -- *************************************************

      function test_ups

      return clob

      as

        l_request        CLOB;

        l_response       CLOB;

        l_buffer         varchar2(32767);

        l_amount         number;

        l_offset         number;

       

      begin

      -- Build the ShipmentConfirmRequest json string.

      l_request :=

      '

      {

      "UPSSecurity": {

        "UsernameToken": {

         "Username": ">>>REDACTED<<<",

         "Password": ">>>REDACTED<<<"

        },

        "ServiceAccessToken": {

         "AccessLicenseNumber": ">>>REDACTED<<<"

        }

      },

      "ShipConfirmRequest": {

        "Request": {

         "RequestOption": "validate"

        },

        "Shipment": {

         "Shipper": {

          "Name" : ">>>REDACTED<<<",

          "ShipperNumber" : ">>>REDACTED<<<",

          "Address": {

           "AddressLine" : ">>>REDACTED<<<",

           "City" : "Victoria",

           "StateProvinceCode" : "BC",

           "PostalCode" : "V9A7R1",

           "CountryCode" : "CA"

          }

         },

         "ShipTo": {

          "Name" : ">>>REDACTED<<<",

          "Phone": {

           "Number" : ">>>REDACTED<<<"

          },

          "Address": {

           "AddressLine" : ">>>REDACTED<<<",

           "City" : "Victoria",

           "StateProvinceCode" : "BC",

           "PostalCode" : "V8X4L7",

           "CountryCode" : "CA"

          }

         },

         "PaymentInformation": {

          "ShipmentCharge": {

           "Type" : "01",

           "BillShipper": {

            "AccountNumber" : ">>>REDACTED<<<"

           }

          }

         },

         "Service": {

          "Code" : "01",

          "Description" : "Next Day Air"

         },

         "Package": {

          "Packaging": {

           "Code" : "02",

           "Description" : "Customer Supplied Package"

          },

          "Dimensions": {

           "UnitOfMeasurement": {

            "Code" : "IN"

           },

           "Length" : "12",

           "Width" : "9",

           "Height" : "3"

          },

          "PackageWeight": {

           "UnitOfMeasurement": {

            "Code" : "LBS"

           },

          "Weight" : "2"

          }

         }

        },

        "LabelSpecification": {

         "LabelImageFormat": {

          "Code" : "GIF"  

         },

         "HTTPUserAgent" : "Mozilla/4.5"

        }

      }

      ' ;

       

        apex_web_service.g_request_headers(1).name  := 'Access-Control-Allow-Headers';

        apex_web_service.g_request_headers(1).value := 'Origin, X-Requested-With, Content-Type, Accept';  

        apex_web_service.g_request_headers(2).name  := 'Access-Control-Allow-Methods';

        apex_web_service.g_request_headers(2).value := 'POST';  

        apex_web_service.g_request_headers(3).name  := 'Access-Control-Allow-Origin';

        apex_web_service.g_request_headers(3).value := '*';  

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

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

       

       

        l_response := apex_web_service.make_rest_request(

                    p_url         => 'https://wwwcie.ups.com/rest/Ship',

                    p_http_method => 'POST',

                    p_body        => l_request,

                    p_wallet_path => 'file:/home/oracle/wallets/dev115',

                    p_wallet_pwd  => 'ucDC5RzgIf14',

                    p_transfer_timeout  => 10000

                  );

       

        return l_response ;

       

      EXCEPTION

        when others then

          return 'Error: ' || SQLERRM ;

      end test_ups;