UTL_HTTP.request:The simplest way to call UTL_HTTP.request is to send a URL and get back the page.
As long as the page was 2,000 bytes or less, this would work well.
Syntax : UTL_HTTP.request(url,proxy,wallet_path,wallet_password);
DECLARE
v_pagecontent LONG;l_return LONG;_url VARCHAR2(2000);
p_param1 VARCHAR2(2000);
p_param2 VARCHAR2(2000);
iCnt NUMBER := 1;
BEGIN
v_url := 'http://YourWebsite.com/YourWebservice.asmx/WebserviceMethod';
--Add the parametersv_url := v_url || '?param1='|| p_param1 ||'¶m2='|| p_param2 ||'';
--Call the WebServicev_pagecontent := UTL_HTTP.request( v_url );
LOOP l_return := SUBSTR( v_pagecontent, iCnt, 255 )
DBMS_OUTPUT.put_line( l_return);
iCnt = iCnt + 255
EXIT WHEN l_return IS NULL OR l_return = ' ' ;
END LOOP ;
EXCEPTION
WHEN Utl_Http.request_failed THEN
DBMS_OUTPUT.put_line ('Request_Failed: ' || Utl_Http.get_detailed_sqlerrm);
WHEN Utl_Http.http_server_error THEN
DBMS_OUTPUT.put_line ('Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm);
END ;
UTL_HTTP.request_pieces
For larger pages, we need to use REQUEST_PIECES as follows which returns Array of UTL_HTTP.HTML_PIECES.
Syntax : UTL_HTTP.request_pieces(url,max_pieces,proxy,wallet_path,wallet_password)
DECLARE
pieces UTL_HTTP.HTML_PIECES;
v_url VARCHAR2(2000);
p_param1 VARCHAR2(2000);
p_param2 VARCHAR2(2000);
BEGIN
v_url := 'http://YourWebsite.com/YourWebservice.asmx/WebserviceMethod';--Add the parameters
v_url := v_url || '?param1='|| p_param1 ||'¶m2='|| p_param2 ||'';
--Call the WebService
pieces := UTL_HTTP.REQUEST_PIECES (v_url);
FOR I IN 1 .. pieces.COUNT
LOOPDBMS_OUTPUT.put_line(SUBSTR ('Value of pieces=' || pieces (I), 1, 255));END LOOP;
EXCEPTION
WHEN Utl_Http.request_failed THEN
DBMS_OUTPUT.put_line ('Request_Failed: ' || Utl_Http.get_detailed_sqlerrm);
WHEN Utl_Http.http_server_error THEN
DBMS_OUTPUT.put_line ('Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm);
END ;
UTL_HTTP.begin_request
DECLARE
v_url VARCHAR2(2000);
p_param1 VARCHAR2(2000);
p_param2 VARCHAR2(2000);
http_req UTL_HTTP.req;
http_resp UTL_HTTP.resp;
BEGIN
v_url := 'http://YourWebsite.com/YourWebservice.asmx/WebserviceMethod';
--Add the parameters
v_url := v_url || '?param1='|| p_param1 ||'¶m2='|| p_param2 ||'';
--Call the WebService
http_req := UTL_HTTP.begin_request(v_url, 'POST', UTL_HTTP.HTTP_VERSION_1_1);
UTL_HTTP.end_request(http_req);
--Response from WebService
http_resp := UTL_HTTP.get_response(http_req);
DBMS_OUTPUT.put_line('Response Received');
DBMS_OUTPUT.put_line('--------------------------');
DBMS_OUTPUT.put_line ( 'Status code: ' || http_resp.status_code );
DBMS_OUTPUT.put_line ( 'Reason phrase: ' || http_resp.reason_phrase );
UTL_HTTP.end_response(http_resp);
EXCEPTION
WHEN Utl_Http.request_failed THEN
DBMS_OUTPUT.put_line ('Request_Failed: ' || Utl_Http.get_detailed_sqlerrm);
WHEN Utl_Http.http_server_error THEN
DBMS_OUTPUT.put_line ('Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm);
END ;
---
Otherway of Consuming a Web Service in Oracle :
Source : http://www.oracle-base.com/articles/10g/utl_dbws10g.php
In Oracle 10g the
The function below uses the
UTL_DBWS
package is loaded by default. In Oracle9i the package must be loaded using the specification and body provided in the zip file.The function below uses the
UTL_DBWS
package to access a web services from PL/SQL. The URL of the WDSL file describing the web service is shown here (http://www.oracle-base.com/webservices/server.php?wsdl). The web service accepts two number parameters and returns the sum of those values.CREATE OR REPLACE FUNCTION add_numbers (p_int_1 IN NUMBER,
p_int_2 IN NUMBER)
RETURN NUMBER
AS
l_service UTL_DBWS.service;
l_call UTL_DBWS.call;
l_wsdl_url VARCHAR2(32767);
l_namespace VARCHAR2(32767);
l_service_qname UTL_DBWS.qname;
l_port_qname UTL_DBWS.qname;
l_operation_qname UTL_DBWS.qname;
l_xmltype_in SYS.XMLTYPE;
l_xmltype_out SYS.XMLTYPE;
l_return NUMBER;
BEGIN
l_wsdl_url := 'http://www.oracle-base.com/webservices/server.php?wsdl';
l_namespace := 'http://www.oracle-base.com/webservices/';
l_service_qname := UTL_DBWS.to_qname(l_namespace, 'Calculator');
l_port_qname := UTL_DBWS.to_qname(l_namespace, 'CalculatorPort');
l_operation_qname := UTL_DBWS.to_qname(l_namespace, 'ws_add');
l_service := UTL_DBWS.create_service (
wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),
service_name => l_service_qname);
l_call := UTL_DBWS.create_call (
service_handle => l_service,
port_name => l_port_qname,
operation_name => l_operation_qname);
l_xmltype_in := SYS.XMLTYPE('<?xml version="1.0" encoding="utf-8"?>
<ws_add xmlns="' || l_namespace || '">
<int1>' || p_int_1 || '</int1>
<int2>' || p_int_2 || '</int2>
</ws_add>');
l_xmltype_out := UTL_DBWS.invoke(call_Handle => l_call,
request => l_xmltype_in);
UTL_DBWS.release_call (call_handle => l_call);
UTL_DBWS.release_service (service_handle => l_service);
l_return := l_xmltype_out.extract('//return/text()').getNumberVal();
RETURN l_return;
END;
/
The output below shows the function in action.
SELECT add_numbers(1, 5) FROM dual;
ADD_NUMBERS(1,5)
----------------
6
SQL>
SELECT add_numbers(10, 15) FROM dual;
ADD_NUMBERS(10,15)
------------------
25
SQL>
No comments:
Post a Comment