8 Replies Latest reply: May 8, 2013 2:58 AM by BluShadow RSS

    how to call web services from PL/SQL?

    751828
      Hi,


      Can one help in how to call web services from PL/SQL? Steps, pros and cons, etc....


      Thanks in advance
        • 1. Re: how to call web services from PL/SQL?
          BluShadow
          Here's some example skeleton code to get you started...
            PROCEDURE p_soap_request(p_username IN VARCHAR2, p_password IN VARCHAR2, p_proxy IN VARCHAR2) IS
              soap_request  VARCHAR2(30000);
              soap_respond  CLOB;
              http_req      utl_http.req;
              http_resp     utl_http.resp;
              resp          XMLType;
              soap_err      exception;
              v_code        VARCHAR2(200);
              v_msg         VARCHAR2(1800);
              v_len number;
              v_txt Varchar2(32767);
            BEGIN
              UTL_HTTP.SET_PROXY(p_proxy);
              -- Define the SOAP request according the the definition of the web service being called
              soap_request:= '<?xml version = "1.0" encoding = "UTF-8"?>'||
                             '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">'||
                             '  <SOAP-ENV:Body>'||
                             '    <m:DownloadRequest xmlns:m="http://www.website.net/messages/GetDetails">'||
                             '      <m:UserName>'||p_username||'</m:UserName>'||
                             '      <m:Password>'||p_password||'</m:Password>'||
                             '    </m:DownloadRequest>'||
                             '  </SOAP-ENV:Body>'||
                             '</SOAP-ENV:Envelope>';
              http_req:= utl_http.begin_request
                        ( 'http://www.website.net/webservices/GetDetailsService.asmx'
                        , 'POST'
                        , 'HTTP/1.1'
                        );
              utl_http.set_header(http_req, 'Content-Type', 'text/xml');
              utl_http.set_header(http_req, 'Content-Length', length(soap_request));
              utl_http.set_header(http_req, 'Download', ''); -- header requirements of particular web service
              utl_http.write_text(http_req, soap_request);
              http_resp:= utl_http.get_response(http_req);
              utl_http.get_header_by_name(http_resp, 'Content-Length', v_len, 1); -- Obtain the length of the response
              FOR i in 1..CEIL(v_len/32767) -- obtain response in 32K blocks just in case it is greater than 32K
              LOOP
                  utl_http.read_text(http_resp, v_txt, case when i < CEIL(v_len/32767) then 32767 else mod(v_len,32767) end);
                  soap_respond := soap_respond || v_txt; -- build up CLOB
              END LOOP;
              utl_http.end_response(http_resp);
              resp:= XMLType.createXML(soap_respond); -- Convert CLOB to XMLTYPE
            END;
          Using secure web services (https)...

          Web serivces call in Plsql


          As for 'pros and cons'... there's nothing to compare against... either you want to call a web service or you don't.
          • 2. Re: how to call web services from PL/SQL?
            Kim Berg Hansen
            For non-ssl webservice calls an alternative could be to use JPublisher to [url http://docs.oracle.com/cd/E11882_01/java.112/e10587/usejpub.htm#CHDGCHCA]generate a webservice call-out package, java and objects for you.
            One command-line call to jpub with a few arguments (notably where to find the wsdl file) and jpub can create all the code necessary to have all the methods of the webservice available as PL/SQL procedures/functions returning the data as object types.

            On the other hand sometimes it may seem like overkill if you just need a single method call out of many that the webservice expose. In such cases it may often be simpler and give you more control if you write the call yourself using http like in BluShadows example. Or alternatively use the utl_dbws package which I believe is mostly a "wrapper" on utl_http helping you to create the SOAP.

            If you need ssl (https), then you cannot use the JPublisher method - you need to use either utl_dbws or utl_http to handle the certificate/wallet stuff.

            We use all three methods at my work - use whichever one fits best the requirements of each case.
            • 3. Re: how to call web services from PL/SQL?
              Billy~Verreynne
              I prefer using UTL_HTTP. Dislike the UTL_DBWS interface - that looks like it sits on top of Java stored procs.

              My approach (very simlar to Blu - as is often the case) is explained in {message:id=10448611}.

              As for pros and cons.

              I do not see any cons with using UTL_HTTP. It does an excellent job for the purpose it is intended - a web browser/HTTP interface. It also provides one with direct programatic command interface to dealing with HTTP. Instead of some abstract layer (like IUTL_DBWS) where you have no clue as to what is happening on the HTTP application protocol level.
              • 4. Re: how to call web services from PL/SQL?
                751828
                Hi all,


                Do we need to install any additional things on the database server?
                • 5. Re: how to call web services from PL/SQL?
                  Billy~Verreynne
                  user9093700 wrote:

                  Do we need to install any additional things on the database server?
                  No. UTL_HTTP is a standard Oracle database package. It installs as part of the data dictionary.

                  There are however one or more configurations needed.

                  On 11g, Oracle does not allow external TCP access to PL/SQL code. Oracle has a "firewall" that prevents PL/SQL and SQL code from accessing to the network. An ACL (access control list) needs to be created for port 80 (http) for example, and assigned to the schema executing that PL/SQL code.

                  Likewise, another configuration is needed when using https access - an Oracle wallet needs to be created (if not already existing) and the web server's certificate(s) need to be added to this Oracle wallet. PL/SQL code needs to open this wallet for using https access to that web server. (this is the very same method used by your web browser, such as IE, Firefox, etc.)
                  • 6. Re: how to call web services from PL/SQL?
                    1006915
                    I use Oracle database 10 G and I need to create a procedure or function that will call a web service from a 3rd party system.

                    The other party has given me the HTTP GET code request and response that I need to use.

                    request is

                    GET /ZsmartServiceewb/AccountService.asmx/Payment?MSISDN=string&Amount=string&PreBalance=string&PartyCode=string&BillID=string&BillNbr=string&AccountID=string&AccountCode=string&SN=string HTTP/1.1
                    Host: 192.168.12.247

                    Response is

                    HTTP/1.1 200 OK
                    Content-Type: text/xml; charset=utf-8
                    Content-Length: length

                    <?xml version="1.0" encoding="utf-8"?>
                    <PaymentDto xmlns="http://www.ZTEsoft.com/ZSmart">
                    <PaymentID>string</PaymentID>
                    <AccountCode>string</AccountCode>
                    <Balance>string</Balance>
                    <balDtoList>
                    <BalDto>
                    <AcctResID>string</AcctResID>
                    <AcctResName>string</AcctResName>
                    <Balance>string</Balance>
                    <BalType>string</BalType>
                    <EffDate>string</EffDate>
                    <ExpDate>string</ExpDate>
                    <UpdateDate>string</UpdateDate>
                    <BalShareDtoList>
                    <BalShareDto d5p1:nil="true" xmlns:d5p1="http://www.w3.org/2001/XMLSchema-instance" />
                    <BalShareDto d5p1:nil="true" xmlns:d5p1="http://www.w3.org/2001/XMLSchema-instance" />
                    </BalShareDtoList>
                    <BalID>string</BalID>
                    </BalDto>
                    <BalDto>
                    <AcctResID>string</AcctResID>
                    <AcctResName>string</AcctResName>
                    <Balance>string</Balance>
                    <BalType>string</BalType>
                    <EffDate>string</EffDate>
                    <ExpDate>string</ExpDate>
                    <UpdateDate>string</UpdateDate>
                    <BalShareDtoList>
                    <BalShareDto d5p1:nil="true" xmlns:d5p1="http://www.w3.org/2001/XMLSchema-instance" />
                    <BalShareDto d5p1:nil="true" xmlns:d5p1="http://www.w3.org/2001/XMLSchema-instance" />
                    </BalShareDtoList>
                    <BalID>string</BalID>
                    </BalDto>
                    </balDtoList>
                    <SubscriberInfoDtoList>
                    <SubscriberInfoDto>
                    <MSISDN>string</MSISDN>
                    <SubscriberID>string</SubscriberID>
                    <CustomerName>string</CustomerName>
                    <ProdState>string</ProdState>
                    <Due>string</Due>
                    </SubscriberInfoDto>
                    <SubscriberInfoDto>
                    <MSISDN>string</MSISDN>
                    <SubscriberID>string</SubscriberID>
                    <CustomerName>string</CustomerName>
                    <ProdState>string</ProdState>
                    <Due>string</Due>
                    </SubscriberInfoDto>
                    </SubscriberInfoDtoList>
                    </PaymentDto>


                    I now need to call the web service from my oracle procedure(function) and pass the required parameters and get a response (ok or not ok).

                    Any suggestion on the code to call that web service will be very appreciated.

                    Thanks,
                    • 7. Re: how to call web services from PL/SQL?
                      Billy~Verreynne
                      Please do no resurrect old and dead threads, and hijack it to ask your question.

                      Post your question as a new thread - which will benefit you as you will be the thread owner. And as it is a new thread, you will have a far bigger audience that will read your problem.
                      • 8. Re: how to call web services from PL/SQL?
                        BluShadow
                        As Billy says, this needs to be a new question in it's own right. (and you can reference back to this thread if you like)

                        Ensure you read: {message:id=9360002}

                        making sure you format your code as it suggests, and also showing what code you've already tried. (This thread pretty much shows you what is needed, so you need to explain why it's not working for you).