Solved

Example of calling WCF webservice from Oracle PL SQL

Posted on 2011-09-22
3
2,873 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
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

685 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