9 Replies Latest reply on Jul 9, 2014 12:03 AM by Amethyst Qin

    Invoke Web Services through HTTPS request - SOAP_API.sql - UTL_HTTP

    Amethyst Qin

      Dear Experts,


      I am successful in invoke HTTP WS request through SOAP_API.sql but I am stuck in the HTTPS request.

      The error I am getting is

      ORA-20000: ns2:Server - Cannot find form with id 0.

      ORA-06512: at "DW_OWNER.SOAP_API", line 121

      ORA-06512: at "DW_OWNER.SOAP_API", line 240

      ORA-06512: at "DW_OWNER.PKG_MY_WEBSERVICE", line 43

      ORA-06512: at line 11


      I wonder if anyone here could help me in it? Many thanks!



      I am trying to invoke Web Services( HTTPS request) through PLSQL package UTL_HTTP. The version of Oracle database is 11g R2.

      At this stage, we have granted the domain of the HTTPS WS request to the ACL of the running schema.

      We have got the chained PEM certificate added into the Oracle Wallet and ran UTL_HTTP.SET_WALLET.


      Also the below SQL runs successfully.  




      utl_http.request('https://keysurveydev.ourdomain/Member/api/v81/form/result/FormResultManagementService?wsdl',null,'directory of the oracle wallet',null) from dual





      So I assume at this stage, the connection is ok. The HTTPS request runs successfully through Soap UI.

      Then I have the invoke procedure written this way(changed from original SOAP_API.sql), but then get the error in the check_fault procedure.


      FUNCTION invoke(p_request IN OUT NOCOPY  t_request,
                      p_url     IN             VARCHAR2,
                      p_action  IN             VARCHAR2,
                      p_username  IN VARCHAR2,
                      p_password IN VARCHAR2)
        VARCHAR2 AS
      -- ---------------------------------------------------------------------
        l_envelope       VARCHAR2(32767);
        l_http_request   UTL_HTTP.req;
        l_http_response  UTL_HTTP.resp;
        l_response       t_response;
        l_line_data      VARCHAR2(32767); 
        l_header_name    VARCHAR2(32767);
        l_header_value   VARCHAR2(32767);
        generate_envelope(p_request, l_envelope);
        show_envelope(l_envelope, 'Request');
        l_http_request := UTL_HTTP.begin_request(p_url, 'POST','HTTP/1.1'); 
        IF (p_username IS NOT NULL) THEN
          UTL_HTTP.SET_AUTHENTICATION(l_http_request, p_username, p_password); -- Use HTTP Basic Authen. Scheme
        END IF;
        UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml');
        UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_envelope));
        UTL_HTTP.set_header(l_http_request, 'SOAPAction', p_action);
        UTL_HTTP.write_text(l_http_request, l_envelope);
        l_http_response := UTL_HTTP.get_response(l_http_request);
        IF (l_http_response.status_code = UTL_HTTP.HTTP_UNAUTHORIZED) THEN
              DBMS_OUTPUT.PUT_LINE('HTTP response status code: HTTP_UNAUTHORIZED');
            RETURN ('HTTP_UNAUTHORIZED end response'); 
        END IF;

        FOR i IN 1..UTL_HTTP.GET_HEADER_COUNT(l_http_response) LOOP
          UTL_HTTP.GET_HEADER(l_http_response, i, l_header_name, l_header_value);
          DBMS_OUTPUT.PUT_LINE(l_header_name || ': ' || l_header_value);
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('HTTP response status code: ' || l_http_response.status_code);
        DBMS_OUTPUT.PUT_LINE('HTTP response reason phrase: ' || l_http_response.reason_phrase);
        UTL_HTTP.read_text(l_http_response, l_envelope);

        show_envelope(l_envelope, 'Response');
        l_response.doc := XMLTYPE.createxml(l_envelope);
        l_response.envelope_tag := p_request.envelope_tag;
        l_response.doc := l_response.doc.extract('/'||l_response.envelope_tag||':Envelope/'||l_response.envelope_tag||':Body/child::node()',
        RETURN 'Normal return';
        WHEN utl_http.end_of_body THEN
         RETURN ('end_of_body');


      I get the error in the red line in the check_fault procedure.


      PROCEDURE check_fault(p_response IN OUT NOCOPY  t_response) AS

      -- ---------------------------------------------------------------------

        l_fault_node    XMLTYPE;

        l_fault_code    VARCHAR2(256);

        l_fault_string  VARCHAR2(32767);


        l_fault_node := p_response.doc.extract('/'||p_response.envelope_tag||':Fault',


        IF (l_fault_node IS NOT NULL) THEN

          l_fault_code   := l_fault_node.extract('/'||p_response.envelope_tag||':Fault/faultcode/child::text()',


          l_fault_string := l_fault_node.extract('/'||p_response.envelope_tag||':Fault/faultstring/child::text()',


          RAISE_APPLICATION_ERROR(-20000, l_fault_code || ' - ' || l_fault_string);

        END IF;



      The error is

      ORA-20000: ns2:Server - Cannot find form with id 0.


      Is there anyone who encountered this before?

      Many thanks!





      I downloaded the soap_api.sql from http://www.google.com.au/url?url=http://www.oracle-base.com/dba/miscellaneous/soap_api.sql&rct=j&frm=1&q=&esrc=s&sa=U&ei=sc-fU56aFIjMkQX7mICoDw&ved=0CBQQFjAA&sig2=jxqML7lgxWg0Oa82PJUQnw&usg=AFQjCNHi_IyY1Ir_LUWhO7K01GXQEMKtIQ

      I also get this example working successfully. http://www.oracle-base.com/articles/9i/consuming-web-services-9i.php


      Message was edited by: Amy Qin

        • 1. Re: Invoke Web Services through HTTPS request - SOAP_API.sql - UTL_HTTP

          I prefer to write my own SOAP client code - it is easy and simple enough. And enables one to deal first hand with errors, and not through some clunky 3rd party interface.


          I described my approach (using only PL/SQL and UTL_HTTP) in Re: Calling webservice from PLSQL

          1 person found this helpful
          • 2. Re: Invoke Web Services through HTTPS request - SOAP_API.sql - UTL_HTTP
            Amethyst Qin

            Thanks a lot!

            Did you say the first step is to get the envelop exactly the same as the envelope of the SoapUI in the PLSQL?

            Another quick question is is there any difference in HTTPS request vs HTTP request? I do have a username and password to be used in SoapUI. I guess they are to be used in the SET_AUTHENTICATION procedure?


            I will update shortly once the changes are applied.

            • 3. Re: Invoke Web Services through HTTPS request - SOAP_API.sql - UTL_HTTP

              Yes, the envelope you use in PL/SQL obviously needs to be valid - which is what SOAPUI can provide.


              HTTPS requires an Oracle wallet to be created (on the db server), the wallet to contain the certificate(s) of the web service (complete certificate chain), and to be opened for use by UTL_HTTP for encrypting its communication with the secured web service.


              Specifying username and password for the web service, depends on the authentication method required by the web service. It could be Basic Authentication. It could be WSSE authentication. Etc. The type of auth required by the web service will determine that you need to code in PL/SQL as the web service client/caller.

              1 person found this helpful
              • 4. Re: Invoke Web Services through HTTPS request - SOAP_API.sql - UTL_HTTP
                Amethyst Qin

                Hi BillyVerreynne,


                Thank you very much for your reply!

                I have used your method to re-write the codes.Also, before calling WRITE_TEXT, I called SET_AUTHENTICATION. Without it, I got different errors.

                Now it throws this error to me - It happens at calling UTL_HTTP.get_response:


                ORA-29269: HTTP server error 500 - Internal Server Error

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

                ORA-06512: at line 100


                From this post, it is said its a server side problem. It's the server which hosts the web service needs to be looked into for the error log. https://community.oracle.com/thread/982850


                What are the potential reasons for this error? Any lights?

                Many thanks!




                Message was edited by: Amy Qin

                • 5. Re: Invoke Web Services through HTTPS request - SOAP_API.sql - UTL_HTTP

                  The web service/web server ran into an unhandled exception. That exception propagated to the top of the call stack (on the web server side) and resulted in a HTTP 500 internal server error being send to the client.


                  All you can do on your side is to look at the data you are sending and trying to guess whether this is causing the error, and what specifically in the data being send could be doing that.


                  The actual error can only be realised by looking at the error log of the web server - thus you need to have a look at the web server's log, or ask the web server admin for assistance.


                  If this is not possible, you need to go back to the drawing board with the call you are making and the data you are sending. Reduce and simplify that to some very basic, and that works. Create a working baseline. Then start to increase the complexity of your call/data to the web server, using what is working as the baseline to evaluate changes/increased complexity against.

                  • 6. Re: Invoke Web Services through HTTPS request - SOAP_API.sql - UTL_HTTP
                    Amethyst Qin

                    Thanks a lot!


                    I just read a post from  http://martin-mares.com/2010/08/oracle-db-consume-wsdl-webservice-in-plsql/ (search for ORA-29269) where it was talking about the characterset problem.

                    I have added the two lines into the code:


                    DECLARE section

                    l_xml_encoding VARCHAR2(128) := 'UTF-8';

                    BODY section

                    UTL_HTTP.set_body_charset( l_http_request, l_xml_encoding);

                    UTL_HTTP.set_header( l_http_request, 'Transfer-Encoding', 'chunked');


                    It still gives me the same error.

                    Then Martin (from the post) said it can also relate to the NLS_LANG* parameters. I compared mine with his, and got the only one different: NLS_CHARACTERSET.

                    Mine is WE81SO8859P1. His is AL32UTF8.


                    To change it, it needs to do series steps from DBA side, which I am not certain yet at this stage should this to be done.

                    Any idea regards to this particular one?




                    • 7. Re: Invoke Web Services through HTTPS request - SOAP_API.sql - UTL_HTTP

                      I would not chase solutions like that blindly without knowing what the actual problem is. What happens when changing db char set with great effort, still does not fix the error?


                      1st choice is to have a look at the web server's error log to see what the actual underlying error is.


                      2nd choice is to create a sanity test (simple base test) that works. If this cannot be done using PL/SQL, use Firefox/Chrome and/or SOAPUI. When that sanity test works, implement it in PL/SQL and get it work as the baseline for further PL/SQL development.

                      • 8. Re: Invoke Web Services through HTTPS request - SOAP_API.sql - UTL_HTTP
                        Amethyst Qin

                        Yes you are right.

                        Address 1st choice: I have sent the details to DBA to look into the underlying error from server hosting the WS.

                        Address 2nd choice: I have successfully invoked an external WS through HTTP request. It works fine. This HTTPS request also works fine through SoapUI. I need to think about a way for the baseline function testing against this particular server.


                        I will update when there is any.



                        • 9. Re: Invoke Web Services through HTTPS request - SOAP_API.sql - UTL_HTTP
                          Amethyst Qin

                          For reference, this issue has not been resolved. DBA suspect it's the NLS_CHARACTERSET paramember difference between the calling schema and the web service implementation domain. The WS in this case has to use AL32UTF8 to be implemented. It's not easy to change the characterset in the calling schema. Therefore, in the end, I didn't get to try this path. However, the good news is that if the WS is a HTTP request, by using the method referenced above Re: Calling webservice from PLSQL, it works well.


                          In the end, I succeeded in calling HTTPS WS through SAP Data Services.

                          Thanks for BillyVerreynne's help!