SOAP Calls from Banner (PL/SQL) to outside Web Service
Tue, 2008-05-20 16:55 — bafoofa
Has anyone been successful in calling an outside web service from Banner in a batch PL/SQL process (online would be even better) using a SOAP call and XML?
I have just recently built a PL/SQL package that uses a combination of Oracle's DBMS_HTTP, DBMS_XMLDOM, and DBMS_XMLSAVE to make HTTPS posts to PayPal's report engine to download settlement report data. It is not exactly soap but I do build XML requests to send and receive XML responses.
Oracle does have a different package, UTL_DBWS, that handles webservices, but I'm not familiar with it and don't know exactly if it is for acting as a webservice or connecting to one.
FUNCTION get_local_time(zipcode IN VARCHAR2) RETURN VARCHAR2 IS
req demo_soap.request;
resp demo_soap.response;
BEGIN
req := demo_soap.new_request('LocalTimeByZipCode',
'xmlns="http://ripedev.com/xsd/ZipCodeResults.xsd"');
demo_soap.add_parameter(req, 'ZipCode', 'xsd:string', zipcode);
resp := demo_soap.invoke(req,
'http://www.ripedev.com/webservices/LocalTime.asmx',
'http://ripedev.com/xsd/ZipCodeResults.xsd/LocalTimeByZipCode');
RETURN demo_soap.get_return_value(resp, 'LocalTimeByZipCodeResult',
'xmlns="http://ripedev.com/xsd/ZipCodeResults.xsd"');
END;
BEGIN
/*
* Since the Web service resides outside of the firewall, we need to set
* the proxy in the current session before invoking the service.
*/
utl_http.set_proxy('proxy.xxx.com', NULL);
utl_http.set_persistent_conn_support(TRUE);
END;
And my PL/SQL Soap call:
exec dbms_output.put_line(time_service.get_local_time('77803'));
Will you please share the method and sample code of your approach? Thanks!
Luminis provides a web services API for sending targeted announcements. We have a lot of skill with pl/sql programming, so if we could access that API this way, it would make it a lot easier for us to develop applications that can send targeted announcement to portal users.
We are successfully able to make SOAP calls from PL/SQL to the Web. I am unable to post the code here because the website thinks it is SPAM. If you would like a copy of the working code, please e-mail me and I will send it to you!
RE:SOAP Calls from Banner (PL/SQL) to outside Web Service
I have just recently built a PL/SQL package that uses a combination of Oracle's DBMS_HTTP, DBMS_XMLDOM, and DBMS_XMLSAVE to make HTTPS posts to PayPal's report engine to download settlement report data. It is not exactly soap but I do build XML requests to send and receive XML responses.
Oracle does have a different package, UTL_DBWS, that handles webservices, but I'm not familiar with it and don't know exactly if it is for acting as a webservice or connecting to one.
RE: Soap Calls from Banner
Here is the SOAP API I have been working with:
http://www.oracle-base.com/dba/miscellaneous/soap_api.sql
My Simple Web_Service package:
create or replace PACKAGE BODY time_service AS
-- Location of Web service definition
-- http://www.ripedev.com/webservices/LocalTime.asmx?WSDL
FUNCTION get_local_time(zipcode IN VARCHAR2) RETURN VARCHAR2 IS
req demo_soap.request;
resp demo_soap.response;
BEGIN
req := demo_soap.new_request('LocalTimeByZipCode',
'xmlns="http://ripedev.com/xsd/ZipCodeResults.xsd"');
demo_soap.add_parameter(req, 'ZipCode', 'xsd:string', zipcode);
resp := demo_soap.invoke(req,
'http://www.ripedev.com/webservices/LocalTime.asmx',
'http://ripedev.com/xsd/ZipCodeResults.xsd/LocalTimeByZipCode');
RETURN demo_soap.get_return_value(resp, 'LocalTimeByZipCodeResult',
'xmlns="http://ripedev.com/xsd/ZipCodeResults.xsd"');
END;
BEGIN
/*
* Since the Web service resides outside of the firewall, we need to set
* the proxy in the current session before invoking the service.
*/
utl_http.set_proxy('proxy.xxx.com', NULL);
utl_http.set_persistent_conn_support(TRUE);
END;
And my PL/SQL Soap call:
exec dbms_output.put_line(time_service.get_local_time('77803'));
Will you please share the method and sample code of your approach? Thanks!
I'd like to see how you are doing this too
Luminis provides a web services API for sending targeted announcements. We have a lot of skill with pl/sql programming, so if we could access that API this way, it would make it a lot easier for us to develop applications that can send targeted announcement to portal users.
RE: SOAP Solution
We are successfully able to make SOAP calls from PL/SQL to the Web. I am unable to post the code here because the website thinks it is SPAM. If you would like a copy of the working code, please e-mail me and I will send it to you!