?
Solved

Example of calling WCF webservice from Oracle PL SQL

Posted on 2011-09-22
3
Medium Priority
?
3,072 Views
1 Endorsement
Last Modified: 2012-05-12
Hi,

I need to make a call to a .NET WCF webservice from PL/SQL in Oracle. The webservice will have a WSDL. Is there a tool I can use, or generic method available, to interrogate a WSDL and allow me to pass the required parameters to the web service method?

I don't have much experience in PL/SQL, so anything you can provide would be great! FYI, some of the examples on google, like the Tom Krueger example, don't seem to work. I have spent a lot of time googling this one, so would prefer a definitive answer, rather than a few links to check out.

Cheers,
Brett
1
Comment
Question by:designavenue
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1500 total points
ID: 36584843
install utl_dbws  

and try this...

DECLARE
    service_          UTL_DBWS.service;
    call_             UTL_DBWS.call;
    service_qname     UTL_DBWS.qname;
    port_qname        UTL_DBWS.qname;
    operation_qname   UTL_DBWS.qname;
    string_type_qname UTL_DBWS.qname;
    l_namespace       VARCHAR2(1000);
    retx              SYS.XMLTYPE;
    xml_string        SYS.XMLTYPE;
    l_str             LONG;
BEGIN
    DBMS_JAVA.set_output(100000);
    l_namespace := 'http://xmlme.com/WebServices';
    service_qname := UTL_DBWS.to_qname(l_namespace, 'Shakespeare');
    service_ :=
        UTL_DBWS.create_service(
            httpuritype('http://www.xmlme.com/WSShakespeare.asmx?WSDL'),
            service_qname
        );
    port_qname := UTL_DBWS.to_qname(l_namespace, 'ShakespeareSoap');
    operation_qname := UTL_DBWS.to_qname(l_namespace, 'GetSpeech');
    call_ := UTL_DBWS.create_call(service_, port_qname, operation_qname);
    UTL_DBWS.set_property(call_, 'SOAPACTION_USE', 'TRUE');
    UTL_DBWS.set_property(call_, 'SOAPACTION_URI', 'http://xmlme.com/WebServices/GetSpeech');
    UTL_DBWS.set_property(call_, 'ENCODINGSTYLE_URI', 'http://schemas.xmlsoap.org/soap/encoding/');
    UTL_DBWS.set_property(call_, 'OPERATION_STYLE', 'document');
    string_type_qname := UTL_DBWS.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
    UTL_DBWS.ADD_PARAMETER(
        call_,
        'Request',
        string_type_qname,
        'ParameterMode.IN'
    );
    UTL_DBWS.set_return_type(call_, string_type_qname);

    xml_string :=
        xmltype(
'<?xml version="1.0" encoding="utf-8"?>
 <GetSpeech xmlns="http://xmlme.com/WebServices">
   <Request>To be, or not to be</Request>
 </GetSpeech>'
        );
    retx := UTL_DBWS.invoke(call_handle => call_, request => xml_string);

    l_str := retx.EXTRACT('/*').getstringval();

    LOOP
        EXIT WHEN l_str IS NULL;
        DBMS_OUTPUT.put_line(SUBSTR(l_str, 1, INSTR(l_str, CHR(10)) - 1));
        l_str := SUBSTR(l_str, INSTR(l_str, CHR(10)) + 1);
    END LOOP;

    UTL_DBWS.release_service(service_);
END;
/

Open in new window

0
 

Author Closing Comment

by:designavenue
ID: 37111745
Solutin wasn't generic enough
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37111877
you could have asked for help
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question