This discussion is archived
8 Replies Latest reply: May 8, 2013 12:58 AM by BluShadow RSS

how to call web services from PL/SQL?

751828 Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi all,


    Do we need to install any additional things on the database server?
  • 5. Re: how to call web services from PL/SQL?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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).

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points