Call SOA Service from PL/SQL

Call SOA Service from PL/SQL:

· Can use SOAPUI software to call SOA service

· Helpful to Identify SOA Service Components from URL , i.e. helpful for our PLSQL Call

Can Test Run Also from Same

Steps Follow below navigation

Put Service URL and Click OK

Click on request

Give Parameter in SOA call in XML format ,same call need to copy while calling from PLSQL.

Click on Execute icon on the Top left Corner

Click on Exec Output will come in next tab in XML , same we will get while calling from PLSQL,

we can parse it to get the desired result.

Below is the Sample SOA Service call

Declare

lv_ws_xmlinput sys.xmltype default null;

lv_ws_xmloutput sys.xmltype default null;

lv_ws_url varchar2 (1000);

lv_soa_ip varchar2(500);

lv_soap_operation varchar2 (200) := 'Myoperation';

lv_callbpel_errbuf varchar2 (2000);

lv_callbpel_retcode varchar2 (100);

ln_log_id number;

param1 varchar2;

begin

---Give complete Service Path ending with ?WSDL ---

lv_ws_url :=lv_soa_ip||'.....?WSDL';

----Format Input String can take same as its in SOAP UI ----

lv_ws_xmlinput :=

sys.xmltype ('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:emp="http://xmlns.oracle.com/myserviceclasspath">

<soapenv:Header/>

<soapenv:Body>

<emp:process>

<emp:input>'||dbms_xmlgen.convert(param1)||'</emp:input>

</emp:process>

</soapenv:Body>

</soapenv:Envelope>');

---Insert Log Logging is importan

insert_log(ln_log_id,lv_ws_url,lv_soap_operation,lv_ws_xmlinput);

xx_call_webservice.call_http_ws (

p_url => lv_ws_url,

p_soap_operation => lv_soap_operation,

p_soap_xml_load => lv_ws_xmlinput,

p_soap_xml_resp => lv_ws_xmloutput,

errbuf => lv_callbpel_errbuf,

retcode => lv_callbpel_retcode);

update_log(ln_log_id,lv_ws_xmloutput,sysdate);

end;

Call Service Detail Package

PROCEDURE CALL_HTTP_WS (P_URL IN VARCHAR2,

P_SOAP_OPERATION IN VARCHAR2,

P_SOAP_XML_LOAD IN SYS.XMLTYPE,

P_SOAP_XML_RESP OUT SYS.XMLTYPE,

ERRBUF OUT VARCHAR2,

RETCODE OUT VARCHAR2)

lv_soap_response_clob CLOB;

lv_soap_response_text VARCHAR2 (30000);

lv_soap_response_xml SYS.XMLTYPE;

lv_soap_request_clob CLOB;

http_req UTL_HTTP.REQ;

http_resp UTL_HTTP.RESP;

lv_soap_operation VARCHAR2 (255) := P_SOAP_OPERATION;

lv_endpoint VARCHAR2 (4000) := P_URL;

nStart NUMBER := 1;

nEnd NUMBER := 30000;

nClobLength NUMBER;

vChunkData VARCHAR2 (30000);

nLength NUMBER := 30000;

BEGIN

lv_soap_request_clob := P_SOAP_XML_LOAD.getClobVal ();

DBMS_OUTPUT.put_line ('lv_soap_request_clob'||lv_soap_request_clob);

BEGIN

UTL_HTTP.set_transfer_timeout (3000);

http_req := UTL_HTTP.begin_request (lv_endpoint, 'POST', 'HTTP/1.1');

UTL_HTTP.set_header (http_req, 'Content-Type', 'text/xml'); -- dealing with plain text in XML documents

UTL_HTTP.set_header (http_req,

'Content-Length',

LENGTH (lv_soap_request_clob));

UTL_HTTP.set_header (http_req, 'SOAPAction', lv_soap_operation); -- required to specify a SOAP communication

--utl_http.write_text(http_req, soap_request);

-- Since the input is huge write in as chunks

nClobLength := LENGTH (lv_soap_request_clob);

LOOP

IF nEnd >= nClobLength

THEN

nEnd := nClobLength;

nLength := nEnd - nStart + 1;

END IF;

vChunkData := NULL;

vChunkData := DBMS_LOB.SUBSTR (lv_soap_request_clob, nLength, nStart);

UTL_HTTP.write_text (http_req, vChunkData);

IF nEnd = nClobLength

THEN

EXIT;

END IF;

nStart := nEnd + 1;

--nEnd := nStart + 30000;

IF (nStart + nLength - 1) >= nClobLength

THEN

nEnd := nClobLength;

ELSE

nEnd := nStart + nLength - 1;

END IF;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

RETCODE := '1';

ERRBUF := SQLERRM;

-- Free the CLOB object memory and close the response

DBMS_LOB.FREETEMPORARY (lv_soap_request_clob);

UTL_HTTP.end_request (http_req);

END;

DBMS_OUTPUT.put_line ('Calling the BPEL Process');

http_resp := UTL_HTTP.get_response (http_req);

IF http_resp.status_code = 200

THEN

DBMS_OUTPUT.put_line ('Getting the Response');

-- Read Response by parts and store it in CLOB

DBMS_LOB.CREATETEMPORARY (lv_soap_request_clob, FALSE);

BEGIN

LOOP

UTL_HTTP.read_text (http_resp, lv_soap_response_text, 30000);

DBMS_LOB.WRITEAPPEND (lv_soap_request_clob,

LENGTH (lv_soap_response_text),

lv_soap_response_text);

END LOOP;

EXCEPTION

WHEN UTL_HTTP.end_of_body

THEN

UTL_HTTP.end_response (http_resp);

END;

DBMS_OUTPUT.put_line ('Creating XML from the soap response');

lv_soap_response_xml := SYS.XMLType (lv_soap_request_clob);

-- Free the CLOB object memory

DBMS_LOB.FREETEMPORARY (lv_soap_request_clob);

DBMS_OUTPUT.put_line (

'Remove unwanted namespaces and tags from SOAP response');

lv_soap_response_xml :=

lv_soap_response_xml.EXTRACT (

'/soap:Envelope/soap:Body/child::node()',

'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"');

P_SOAP_XML_RESP := lv_soap_response_xml;

-- dbms_output.put_line('Output XML: '||P_SOAP_XML_RESP.getStringVal());

DBMS_OUTPUT.put_line (' end of call: ' || P_URL || ' Service.');

ELSE

RETCODE := '2';

DBMS_OUTPUT.put_line (' end of call: ' || P_URL || ' Service.');

END IF; --http_resp.status_code

EXCEPTION

WHEN OTHERS

THEN

RETCODE := '1';

ERRBUF := SQLERRM;

DBMS_OUTPUT.put_line ('EXCEE'||ERRBUF);

-- Free the CLOB object memory and close the response

DBMS_LOB.FREETEMPORARY (lv_soap_request_clob);

UTL_HTTP.end_response (http_resp);

END CALL_HTTP_WS;

Oracle Applications Technical

Search This Blog

Call SOA Service from PL/SQL

Comments

Post a Comment