5 Replies Latest reply on Nov 9, 2018 1:05 AM by Jason Blankenship

    SparkPost EMAIL REST API

    Jason Blankenship

      I am trying to Send Emails using SparkPost Email REST API but am running into the following error:

       

      [

      ERR-ORA-29273: HTTP request failed

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

      ORA-29259: end-of-input reached

      ]

       

      I can execute the REST API without issue from Postman.

      I have imported SparkPosts SSL cert into my oracle wallet, and I believe I have set my headers correctly, below is the code that I am using.

       

      *******************************************

      declare

          lv_sparkpost_url       varchar2(1024);

          lv_sparkpost_key      varchar2(256);

          lv_return                   CLOB;

          lv_request                 CLOB;

         

          ldap_wallet VARCHAR2(256)    := 'file:/u01/app/oracle/admin/PROD/ldapauth';

          ldap_wpass  VARCHAR2(256)    := '<walletpass>';

         

          v_code             varchar2(1024);

          v_errm             varchar2(1024);

         

      BEGIN

          lv_sparkpost_url    := 'https://api.sparkpost.com/api/v1/transmissions';

          lv_sparkpost_key    := '<sparkpostkey>';

              

          lv_request := '{"content": {"from": "test@test.domain.com, "subject": "A Great Email", "text": "With great things to say."}, "recipients": [{"address": "<my-email-addr>"}]}';

            

             BEGIN

             

              apex_web_service.g_request_headers.delete;

              APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).NAME := 'Content-Type';

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

             

              APEX_WEB_SERVICE.G_REQUEST_HEADERS(2).NAME := 'Authorization';

              --apex_web_service.g_request_headers(1).Value := 'Basic '|| lv_sparkpost_key;

              apex_web_service.g_request_headers(2).Value := lv_sparkpost_key;     

             

              lv_return := APEX_WEB_SERVICE.MAKE_REST_REQUEST(

                  p_url => lv_sparkpost_url,

                  p_http_method => 'POST',

                  --p_username => null,

                  --p_password => null,

                  --p_proxy_override => null,

                  p_transfer_timeout => 180,

                  --p_parm_name => apex_util.string_to_table(p_string => , p_separator => ';'),

                  --p_parm_value => apex_util.string_to_table(p_string => , p_separator => ';'),

                  p_body => lv_request,

                  p_wallet_path => ldap_wallet,

                  p_wallet_pwd => ldap_wpass

                  );

                 

                  dbms_output.put_line('lv_return: '|| lv_return); 

           EXCEPTION

           WHEN OTHERS THEN

           v_code := SQLCODE;

           v_errm := SUBSTR(SQLERRM, 1, 255);

           DBMS_OUTPUT.PUT_LINE('ERR-' || v_errm);

           END;

       

      COMMIT;

      END;

      *******************************************

       

      Any help would be appreciated, I have used simular code before to execute Twilio SMS API, execute Google maps API, etc, but this is the first time I have come across this error before.

       

      Thanks

      Jason

        • 1. Re: SparkPost EMAIL REST API
          Pavel_p

          Hi Jason,

          it seems that you're missing double quotes after the mail address. It's usually a good idea to add debug messages (either dbms_output or more convenient https://docs.oracle.com/database/apex-5.1/AEAPI/MESSAGE-Procedure.htm#AEAPI29228 ) to your code like

          dbms_output.put_line('Message body: ' || lv_request );
          

          which should discover pretty soon what is wrong. Also this site https://jsonformatter.curiousconcept.com/ might be helpful - it took just a few seconds to (in)validate your JSON object. Another good habit is to check the response status code

          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;
          

          which should also give you some clues what really happened.

          Please, do not explicitly COMMIT and let the caller (APEX engine) decide whether to commit or rollback the entire transaction. In this particular case it seems that it does not matter but if this process was a part of some more complicated transaction, COMMIT issued in the middle could cause data inconsistencies. APEX automatically commits if there were no errors during page processing, otherwise rollback is issued, so unless you have some very special reasons to do so (I can't think of any), do not bother with committing "manually".

          Other than that your code looks OK, maybe just reconsider if you really need the v_code variable.

          Regards,

          Pavel

          • 2. Re: SparkPost EMAIL REST API
            Jason Blankenship

            Thanks, I confirmed that my JSON contains the proper formatting and tried again with the same result.

             

             

            I'm still running APEX 5.1 with Oracle 11.2.0.4, we will be moving to APEX 18.2 with Oracle 18c db in a couple of months but until then I am stuck with 5.1 capabilities.

             

            Thanks

            Jason

            • 3. Re: SparkPost EMAIL REST API
              Pavel_p

              Hi Jason,

              sorry for misleading info, my original estimation was just too hasty. Even with the "broken" JSON you should not be getting this error, just some different response code than 200 OK. The exception is being raised right after the REST call before eventual evaluation of the status code. On my 11g XE I'm getting the very same error (I know that it cannot work, I just tried to compare the behaviour) and unfortunately I am not able to make it working even on 12.2 developer VM (however I'm at least getting the standard "certificate validation error", unfortunately I have no idea why as I imported all of the 4 certificates shown by this command openssl s_client -showcerts -connect api.sparkpost.com:443). Maybe it could work on 18c but unfortunately I don't have enough time to elaborate with it right now.

              Anyway, the whole thing with Ora wallets and certificates is extremely tricky and cryptic (sometimes also buggy) and I'm not sure if it's even possible to make it working with your rather old DB version, so I would suggest not to waste your time and either setup a reverse proxy Apex the Smart way: making https (webservice) requests from PL/SQL without a wallet or wait for the DB upgrade (or choose another solution for sending emails).

              If you want to elaborate further with the wallet and certificates, read this thread Re: utl_http.request on ssl site fails with ORA-29024 , you might find there some useful info.

              Regards,

              Pavel

              • 4. Re: SparkPost EMAIL REST API
                Pavel_p

                I just tried on 18c XE and it works as expected. Steps to make it working:

                openssl s_client -showcerts -connect api.sparkpost.com:443
                

                there are 4 certificates, the last one is the one we need:

                -----BEGIN CERTIFICATE-----
                MIIEdTCCA12gAwIBAgIJAKcOSkw0grd/MA0GCSqGSIb3DQEBCwUAMGgxCzAJBgNV
                BAYTAlVTMSUwIwYDVQQKExxTdGFyZmllbGQgVGVjaG5vbG9naWVzLCBJbmMuMTIw
                MAYDVQQLEylTdGFyZmllbGQgQ2xhc3MgMiBDZXJ0aWZpY2F0aW9uIEF1dGhvcml0
                eTAeFw0wOTA5MDIwMDAwMDBaFw0zNDA2MjgxNzM5MTZaMIGYMQswCQYDVQQGEwJV
                UzEQMA4GA1UECBMHQXJpem9uYTETMBEGA1UEBxMKU2NvdHRzZGFsZTElMCMGA1UE
                ChMcU3RhcmZpZWxkIFRlY2hub2xvZ2llcywgSW5jLjE7MDkGA1UEAxMyU3RhcmZp
                ZWxkIFNlcnZpY2VzIFJvb3QgQ2VydGlmaWNhdGUgQXV0aG9yaXR5IC0gRzIwggEi
                MA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDVDDrEKvlO4vW+GZdfjohTsR8/
                y8+fIBNtKTrID30892t2OGPZNmCom15cAICyL1l/9of5JUOG52kbUpqQ4XHj2C0N
                Tm/2yEnZtvMaVq4rtnQU68/7JuMauh2WLmo7WJSJR1b/JaCTcFOD2oR0FMNnngRo
                Ot+OQFodSk7PQ5E751bWAHDLUu57fa4657wx+UX2wmDPE1kCK4DMNEffud6QZW0C
                zyyRpqbn3oUYSXxmTqM6bam17jQuug0DuDPfR+uxa40l2ZvOgdFFRjKWcIfeAg5J
                Q4W2bHO7ZOphQazJ1FTfhy/HIrImzJ9ZVGif/L4qL8RVHHVAYBeFAlU5i38FAgMB
                AAGjgfAwge0wDwYDVR0TAQH/BAUwAwEB/zAOBgNVHQ8BAf8EBAMCAYYwHQYDVR0O
                BBYEFJxfAN+qAdcwKziIorhtSpzyEZGDMB8GA1UdIwQYMBaAFL9ft9HO3R+G9FtV
                rNzXEMIOqYjnME8GCCsGAQUFBwEBBEMwQTAcBggrBgEFBQcwAYYQaHR0cDovL28u
                c3MyLnVzLzAhBggrBgEFBQcwAoYVaHR0cDovL3guc3MyLnVzL3guY2VyMCYGA1Ud
                HwQfMB0wG6AZoBeGFWh0dHA6Ly9zLnNzMi51cy9yLmNybDARBgNVHSAECjAIMAYG
                BFUdIAAwDQYJKoZIhvcNAQELBQADggEBACMd44pXyn3pF3lM8R5V/cxTbj5HD9/G
                VfKyBDbtgB9TxF00KGu+x1X8Z+rLP3+QsjPNG1gQggL4+C/1E2DUBc7xgQjB3ad1
                l08YuW3e95ORCLp+QCztweq7dp4zBncdDQh/U90bZKuCJ/Fp1U1ervShw3WnWEQt
                8jxwmKy6abaVd38PMV4s/KCHOkdp8Hlf9BRUpJVeEXgSYCfOn8J3/yNTd126/+pZ
                59vPr5KW7ySaNRB6nJHGDn2Z9j8Z3/VyVOEVqQdZe4O/Ui5GjLIAZHYcSNPYeehu
                VsyuLAOQ1xk4meTKCRlb/weWsKh/NEnfVqn3sF/tM+2MR7cwA130A4w=
                -----END CERTIFICATE-----
                

                open the wallet manager => right-click Trusted Certificates => Import Trusted Certificates => Paste the certificate (the entire thing with --BEGIN CERTIFICATE-- ... --END CERTIFICATE--).

                Now you should have Starfield Services Root Certificate Authority - G2 among Trusted Certificates and the command

                select utl_http.request(url => 'https://api.sparkpost.com/api/v1/transmissions',
                                        wallet_path => 'file:/opt/oracle/product/18c/dbhomeXE/owm/wallets/root',
                                        wallet_password => 'wallet_pwd') resp
                  from dual;
                

                produces output

                "{"errors": [ {"message": "Unauthorized."} ]}"
                

                exactly as expected.

                I'm afraid you'll have a really hard time to make it working on 11.2.0.4 which actually means that I don't think it's possible, so one of the recommended alternatives in the previous post looks like the way to go.

                • 5. Re: SparkPost EMAIL REST API
                  Jason Blankenship

                  Thanks for the help, I ended up using an F5 LTM as an HTTP -> HTTPS proxy allowing Oracle to connect via HTTP, worked like a charm!

                   

                  1.) Configure F5 LTM Virtual Server

                   

                  With FQDN Node pointing to Sparkpost

                   

                  2.) Then defined a local host entry on the Oracle Server pointing to the internal proxy ip for the sparkpost url

                   

                  3.) Add Oracle ACL for api.sparkpost.com

                   

                  Change my code above to not use the SSL wallet and change the rest url from https to HTTP and it worked the first time.

                   

                   

                  Thanks

                  Jason