SQL & PL/SQL

Hi,

I am receiving this error response XML when trying to make a SOAP call from a PLSQL function using UTL_HTTP utility. The SOAP fault string response that I am getting is as follows:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Header></soapenv:Header><soapenv:Body><soapenv:Fault xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"><faultcode>wsse:FailedAuthentication</faultcode><faultstring>Access Denied</faultstring><detail><wsse:ProblemSecurityHeader>wsse:UsernameToken</wsse:ProblemSecurityHeader></detail></soapenv:Fault></soapenv:Body></soapenv:Envelope>

My code is as follows:

create or replace FUNCTION call_webser1
RETURN XMLTYPE
IS
soap_url VARCHAR2 (1000) := 'Some URL';
soap_envelope VARCHAR2 (32767)
:= '<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header>
<ns7:ClientInfoHeader xmlns:ns7="urn:messages.ws.xyz.com/v1_2" soapenv:mustUnderstand="0">
<ns7:AppID>Basic Get</ns7:AppID>
</ns7:ClientInfoHeader>
<wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" mustUnderstand="1">
<wsse:UsernameToken>
<wsse:Username>xyz</wsse:Username>
<wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">xyz</wsse:Password>
</wsse:UsernameToken>
</wsse:Security>
</soapenv:Header>
<soapenv:Body>
<ns7:Get xmlns:ns7="urn:messages.ws.xyz.com/v1_2">
<ns7:RNObjects xmlns:ns4="urn:objects.ws.xyz.com/v1_2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns4:Contact">
<ID xmlns="urn:base.ws.xyz.com/v1_2" id="571"/>
</ns7:RNObjects>
<ns7:ProcessingOptions>
<ns7:FetchAllNames>true</ns7:FetchAllNames>
</ns7:ProcessingOptions>
</ns7:Get>
</soapenv:Body>
</soapenv:Envelope>';
-- our local variables
proxyurl VARCHAR2 (4000);
request UTL_HTTP.req;
response UTL_HTTP.resp;
buffer VARCHAR2 (32767);
httpdata CLOB;
eof BOOLEAN;
soapaction VARCHAR2 (100) := 'Some URL';
xml XMLTYPE;
l_buffer_size NUMBER (10) := 512;
l_line_size NUMBER (10) := 50;
l_lines_count NUMBER (10) := 20;
l_line VARCHAR2 (128);
l_substring_msg VARCHAR2 (512);
l_raw_data RAW (512);
l_clob_response CLOB;
-------------------
req_length binary_integer;
response_body CLOB;
resp_length binary_integer;
l_buffer_new varchar2 (200);
amount pls_integer := 200;
offset pls_integer := 1;
my_scheme VARCHAR2(256);
my_realm VARCHAR2(256);
my_proxy BOOLEAN;


BEGIN
-- formatting the soap envelope
-- our "browser" settings
DBMS_OUTPUT.PUT_LINE('Begin');
UTL_HTTP.set_response_error_check (FALSE);
UTL_HTTP.set_detailed_excp_support (TRUE);
UTL_HTTP.set_cookie_support (TRUE);
UTL_HTTP.set_transfer_timeout (30);
UTL_HTTP.set_follow_redirect (3);
UTL_HTTP.set_persistent_conn_support (FALSE);
-- setting our proxy (only needed when a web proxy is needed for net access)
proxyurl := 'Some Proxy';
UTL_HTTP.set_proxy (proxyurl, NULL);
DBMS_OUTPUT.PUT_LINE('After settings');
-- we perform a HTTP post (sending the SOAP envelope to the server)
DBMS_OUTPUT.PUT_LINE('Before Request');
request := UTL_HTTP.begin_request (soap_url, 'POST', 'HTTP/1.1');
DBMS_OUTPUT.PUT_LINE('After Request');
-- UTL_HTTP.set_header (request, 'User-Agent', 'Mozilla/4.0 (compatible)');
UTL_HTTP.set_header (request, 'Content-Type', 'text/xml; charset=utf-8');
UTL_HTTP.set_header (request, 'Content-Length', LENGTH (soap_envelope));
UTL_HTTP.set_header (request, 'Transfer-Encoding', 'chunked');
UTL_HTTP.set_header (request, 'SOAPAction', '"Some URL"');
DBMS_OUTPUT.PUT_LINE('After Setting Headers');

DBMS_OUTPUT.PUT_LINE(LENGTH (soap_envelope));

req_length := LENGTH (soap_envelope);

DBMS_OUTPUT.PUT_LINE('Soap request Length :'|| req_length);
DBMS_OUTPUT.PUT_LINE(soap_envelope);
if req_length <= 200
then

-- UTL_HTTP.set_header (request, 'Content-Length', req_length);
UTL_HTTP.write_text (request, soap_envelope);

elsif req_length>200
then

-- UTL_HTTP.set_header (request, 'Transfer-Encoding', 'chunked');

WHILE (offset < req_length)
LOOP
DBMS_LOB.read (soap_envelope,
amount,
offset,
l_buffer_new );

DBMS_OUTPUT.PUT_LINE(l_buffer_new );
UTL_HTTP.write_text (request, l_buffer_new );
offset := offset + amount;

END LOOP;

end if;


-- we receive the XML response from the web service (saving it as a CLOB)
DBMS_OUTPUT.put_line('Before response');
response := UTL_HTTP.get_response (request);
DBMS_OUTPUT.put_line('Response> status_code: "' || response.status_code || '"');
DBMS_OUTPUT.put_line('Response> reason_phrase: "' ||response.reason_phrase || '"');
DBMS_OUTPUT.put_line('Response> http_version: "' || response.http_version || '"');

DBMS_LOB.createtemporary (httpdata, TRUE);
eof := FALSE;

LOOP
EXIT WHEN eof;

BEGIN
UTL_HTTP.read_text (response, buffer, 32767);

IF (buffer IS NOT NULL) AND LENGTH (buffer) > 0
THEN
DBMS_LOB.writeappend (httpdata, LENGTH (buffer), buffer);
END IF;
EXCEPTION
WHEN UTL_HTTP.end_of_body
THEN
eof := TRUE;
END;
END LOOP;

UTL_HTTP.end_response (response);
xml := XMLTYPE (httpdata);
DBMS_LOB.freetemporary (httpdata);
RETURN (xml);
EXCEPTION
WHEN OTHERS
THEN
UTL_HTTP.end_response (response);

IF httpdata IS NOT NULL
THEN
DBMS_LOB.freetemporary (httpdata);
END IF;

DBMS_OUTPUT.PUT_LINE(SQLERRM);

RAISE;
END;

I have double checked on the username and password that I am using, not sure where I am going wrong. Any help would be appreciated.

Regards,
Pradeep

Edited by: Pradeep Bharadwaj on Aug 12, 2012 10:23 PM