Advice how to get p_https_host right in apex_web_service.make_request ?


    Just switching from and faced challenging situation with not having proper CN values for p_https_host parameter set.

    While I have several ws to be consumed and there will be several CN values, I would like to have programmatic method to find correct value for p_https_host .

    It seems to pretty difficult to find correct CN by investigating the server certs etc.


    Any hints on this?


        l_xml := apex_web_service.make_request(
             p_url          => ''
            ,p_action       => v_full_url
            ,p_envelope     => v_envelope
            ,p_proxy_override => ''
            ,p_wallet_path  => wallet_path
            ,p_wallet_pwd   => wallet_password
            ,p_https_host => ''


    rgrds Paavo

    ps. I have read the following and I have done several manual trials to get the p_https_host correctly:
    12.2 upgrade - it can break all your outgoing https calls | Oracle FAQ

    error ORA-24263 with social login microsoft

      • 1. Re: Advice how to get p_https_host right in apex_web_service.make_request ?

        Hmm. thought so that it is quite hard to derive the correct p_https_host dynamically if it is actually different from the p_url .

        I have understood that apache reverse proxying is ultimate solution to this challenge, but I still would like to understand and have a proper fix.

        Is it time to turn to MOS and if so, are there patches which brings ease?


        rgrds Paavo

        • 2. Re: Advice how to get p_https_host right in apex_web_service.make_request ?

          After googling furiously I found this:

               Without SNI, the domain first appears in cleartext in the Server Hello of the TLS handshake (In the rdnSequence of the Certificate field).


          and it raises the question is it possible to get the domain name from in the rdnSequence of the Certificate field in the 'Server Hello of the TLS handshake'   ?


          rgrds Paavo

          • 3. Re: Advice how to get p_https_host right in apex_web_service.make_request ?

            Hi Paavo,

            please, read also this Re: utl_http.request on ssl site fails with ORA-29024. I'm not sure if by "programmatically" you meant also invoking the openssl command, but it should not be a big deal to process its output.



            1 位用户发现它有用
            • 4. Re: Advice how to get p_https_host right in apex_web_service.make_request ?

              Yes indeed this aint easy.

              To be frank I assume that some of errors I have been able to generate while iterating the p_https_host are due 'snakeoil self-signed certs' somewhere deep where I can't see from the plsql.

              Only indication about it is that the error remains the same ORA-29024 in our pretty patched 12.2.x for them regardless about the p_https_host.

              The valid certificate at the ws server end gives more reasonable errors like: ORA-28788, ORA-24263 or just works when p_https_host appears to be correct.

              For 12.1.x there is no need to define p_https_host.


              For the programmatic fetch I dreamed about the MLE python script something like or so, but it seems that its still in vbox pampering phase its not said that we are allowed to wireshark db.


              rgrds Paavo

              • 5. Re: Advice how to get p_https_host right in apex_web_service.make_request ?

                I must admit that I don't fully understand your last post. I cannot test your code and I have no idea what site your trying to invoke, so there is very little that can be done. The p_https_host parameter was introduced in 12.2 (… ) because of multi-site certificates, so obviously there is no such parameter in 12.1 and in 18+ version this is being handled automatically by the database.

                I know very little about Python (as it's the most "dangerous" and error-prone language I've ever seen - just my opinion, of course) but it should not be that tough to invoke openssl and process its output instead of using native Python module(s).

                its not said that we are allowed to wireshark db

                I have no idea whatsoever what this can possibly mean.

                1 位用户发现它有用
                • 6. Re: Advice how to get p_https_host right in apex_web_service.make_request ?

                  Yes, I should have elaborated more my findings. 1st I was perhaps barking the wrong tree with the https_host parameter, because it seemed be already patched in 'my' environment, so no bugs. Also the error ORA-29024 was thrown for all possible variants I was trying for both the Oracle wallet + https_host parameter in the call for some of the webservices and for some of the web_services which apparently had 'valid CA for the client certificate' and which had 'selfsigned client certificate' I started to get more descriptive errors like ORA-28788 and ORA-24263 and I managed to get those webservices up & running in 12.2.

                  It is very hard to see from plsql what are the actual ssl certificates in the client-server TCP+TLS1.2 handshake and further on what keeps failing when the Client + Server Hellos and TCP handshakes have been done.

                  Even the openssl is not showing what happens, so tcpdump to listen the network device is needed to see. The wireshark is just cool gui to e.g. analyze the tcpdumps, and it showed for the working connections before actual enciphered data exchanged between client-server the enciphering is agreed between client and server (tls handshake).
                  For that the server client certificate which might be different and self-signed for the outgoing data might be the source of the problem - or - not having client certificate at all (currently cant say which one of these). For selfsigned certs the issuer is the same as the certs cn itself so their CA-issuer chain might also be not known by the wallet. Wireshark can show from the tcpdump 'unknown CA' 'from 'client db' for the connections which fail miserably with the ORA-29024.

                  What I meant with wireshark db is that it is not typical to be able to dump network traffic on the database servers network interface.

                  Also what I was hoping to see is more details about the actual TLS handshake between the client and server, so was thinking first python code and some premade scripts with them. (I like you don't like Python at all and just because of not being able to have strongly typed null, just dull none type for nulled values. So if we could do this with plsql then it would be the best.).


                  As said there were numerous forum articles e.g. yours etc. to be checked before I was able to reach this step having some of the ws working and some of them not. Imagine the situation for 'casual' developer trying to contact those ws via proxy and having no idea whatsoever about the needed checks.


                  It wouldn't hurt if there would be a good 'sequence diagram' about this doc-page:…

                  where also the ORA-nnnn errors would be presented for e.g. typical Apex application consuming the webservices. Good subject for very descriptive blog I think. Also it wouldn't hurt to give SNI examples both working and erronous with network traffic dumps showing what is going wrong in the handshakes.


                  Actually this whole discussion (sort of question) has triggered more generic question about how to really capture the ORA-nnnn errors from harmless ws-calls which are not necessarily needed. I opened other thread for Robustness to page with error-prone ws requests?  ,  but havent yet been able to wrap the 'flaky' ws calls with better 'error handlers' because such want fix the root cause for the problems.


                  rgrds Paavo

                  ps. sorry for not being able to share screenshots about the actual network analysis dumps etc. - it would require more time to make clean test case for this...

                  • 7. Re: Advice how to get p_https_host right in apex_web_service.make_request ?

                    I must first thank Carsten Czarski-Oracle and Pavel_p for giving more insight to this matter and persistence to keep trying to get the ws connections to work despite the errors.

                    Below some ORA-errors and how I have interpreted them:


                    First I spend lots of time to get see errors behind the ORA-29273


                    Error report -
                    ORA-29273: HTTP request failed
                    ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 1988
                    ORA-53203: security violation
                    ORA-06512: at "SYS.UTL_HTTP", line 380
                    ORA-06512: at "SYS.UTL_HTTP", line 1127
                    ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 1891
                    ORA-06512: at "APEX_190100.WWV_FLOW_WEBSERVICES_API", line 196
                    ORA-06512: at "MYSCHEMA.MY_CODE_PKG", line 364
                    ORA-06512: at "MYSCHEMA.SP_MY_TEST_CALL", line 41
                    ORA-06512: at line 1
                    29273. 00000 -  "HTTP request failed"
                    *Cause:    The UTL_HTTP package failed to execute the HTTP request.
                    *Action:   Use get_detailed_sqlerrm to check the detailed error message.
                               Fix the error and retry the HTTP request.


                    And looked for the more detailed ORA-error like in the above ORA-53203


                    my interpretation
                    ORA-29273: HTTP request failedthis is the error the end-user will see e.g. in the Apex application and deeper analysis is needed why.
                    ORA-24263: Certificate of the remote server does not match the target address.certificate is ok, but p_https_host FQDN is not. I was sure that I had faced the 12c 12.2.* challenges with the https_host but that wasn't the case for my setup. And I was desperately iterating all imaginable host names for the ws with multi-site certificate I was trying to consume.
                    ORA-28788: user provided invalid information, or an unknown error.   error from erroneous wildcard in p_https_host = *.somwhere.tla
                    ORA-29024: Certificate validation failure      

                    no p_wallet_path defined, missing correct certificate.
                    I really think there should be perhaps more descriptive error which gives hint about missing wallet or explains that no wallet has been used, so the certificates are not trusted in TLS handshake.

                    It was hard to believe that only CA cert was needed to get things working

                    ORA-12535: TNS:operation timed out no p_proxy_override defined or just no connectivity
                    ORA-29106: Cannot import PKCS #12 wallet.this is actually because of wrong password for wallet
                    ORA-53203: security violation

                    this is shown in sqldeveloper (not disconnected session)
                    after issuing for wallet wrong password,
                    all wallets throw this so all apex_web_service.make_request connections for this sql client fail.