Home » SQL & PL/SQL » SQL & PL/SQL » consuming web service using utl_dbws
consuming web service using utl_dbws [message #303128] Thu, 28 February 2008 01:21 Go to next message
sangleweb
Messages: 10
Registered: March 2006
Junior Member
see attachment, I've included the xml sample that the web service expected. How can I call this within my PL/SQL? I saw some basic sample consuming basic but this consumption require to create an object since the web service is expecting those data. I don't have any preference on the language but it must be able to call it within PL/SQL, TIA.
Re: consuming web service using utl_dbws [message #303129 is a reply to message #303128] Thu, 28 February 2008 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what are the source (tables), its format (columns), the inputs, explain what you want, post a test case, post what you already did, why it does not fit your requirements, why you are stuck and so on.

Regards
Michel
Re: consuming web service using utl_dbws [message #303891 is a reply to message #303129] Mon, 03 March 2008 02:48 Go to previous messageGo to next message
sangleweb
Messages: 10
Registered: March 2006
Junior Member
OK, so I find an article of using util_http, I was able to post it successfully but I an running into the problem if the posting containing too much data, I am getting
ORA-06512: at "CONCUR_ADMIN.SLB_PEVIWS_FUNC", line 71
here is my code:
Thiw will work just fine if I only include 1 records with 2 or less detail line, more that that would throw the ORA-06512 error.

[CODE]
CREATE OR REPLACE FUNCTION CONCUR_ADMIN.SLB_PEVIWS_FUNC
(
p_target_sys IN VARCHAR2 DEFAULT 'LAWSON FIN'
)
RETURN CLOB
AS
CURSOR c_get_header IS
SELECT rpt_key, rpt_id, e_emp_id, e_first_name, e_last_name, e_bus_area_c1, rpt_submit_date, rpt_name, e_company_ou2,
e_cost_center_ou3, e_project_ou4, rpt_bus_purpose_c1, rpt_appr_date, rpt_curr_alpha_code, rpt_curr_num_code,
rpt_policy, rpt_ledger, RTP_ASSIGNMNT_C7, rpt_appr_amt
FROM slb_header_extract
WHERE target_sys = p_target_sys;-- and rpt_key in (626,659,633);
CURSOR c_get_detail (p_rpt_key ct_report.rpt_key%TYPE) IS
SELECT rpe_key, expense_type, entry_trans_date, entry_desc, entry_cc_ou3, entry_proj_ou4, entry_proj_ou4 AS int_order,
tax_amount, tax_code, gl_account_code, gl_sub_accnt, debit_credit, journal_amount, net_tax_amt, creditcard_num, payer_type,
payee_type
FROM slb_detail_extract
WHERE rpt_key=p_rpt_key;

soap_request CLOB;
soap_respond varchar2(30000);
http_req utl_http.req;
http_resp utl_http.resp;
resp XMLType;

v_batch_id NUMBER;
v_app_name VARCHAR2(10) := 'eClaims';
v_int_date VARCHAR(12) := to_char(sysdate,'DD-MON-RRRR');
v_header_xml CLOB := NULL;
v_line_xml CLOB := NULL;
begin

SELECT slb_batch_id_seq.nextval INTO v_batch_id FROM DUAL;

soap_request:= '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<SOAP-ENV:Body>
<s1:era_batch xmlns:s1="urn:sims">
<s1:app_name>' || v_app_name ||'</s1:app_name>
<s1:system>' || p_target_sys || '</s1:system>
<s1:batch_id>' || v_batch_id || '</s1:batch_id>
';

FOR hdr IN c_get_header LOOP
v_line_xml := NULL;
v_header_xml := '<s1:era_report>
<s1:header>
<s1:rpt_key>'|| hdr.rpt_key ||'</s1:rpt_key>
<s1:rpt_id>'|| hdr.rpt_id ||'</s1:rpt_id>
<s1:emp_id>'|| hdr.e_emp_id ||'</s1:emp_id>
<s1:first_name>'|| hdr.e_first_name ||'</s1:first_name>
<s1:last_name>'|| hdr.e_last_name ||'</s1:last_name>
<s1:bus_area>'|| hdr.e_bus_area_c1 ||'</s1:bus_area>
<s1:rpt_sub_date>'|| hdr.rpt_submit_date ||'</s1:rpt_sub_date>
<s1:rpt_name>'|| hdr.rpt_name ||'</s1:rpt_name>
<s1:company>'|| hdr.e_company_ou2 ||'</s1:company>
<s1:cc>'|| hdr.e_cost_center_ou3 ||'</s1:cc>
<s1:project>'|| hdr.e_project_ou4 ||'</s1:project>
<s1:rpt_desc>'|| hdr.rpt_bus_purpose_c1 ||'</s1:rpt_desc>
<s1:apprv_date>'|| hdr.rpt_appr_date ||'</s1:apprv_date>
<s1:curr_alpha>'|| hdr.rpt_curr_alpha_code ||'</s1:curr_alpha>
<s1:curr_num>'|| hdr.rpt_curr_num_code ||'</s1:curr_num>
<s1:rpt_policy>'|| REPLACE(hdr.rpt_policy,'&','&amp;') ||'</s1:rpt_policy>
<s1:ledger>'|| hdr.rpt_ledger ||'</s1:ledger>
<s1:assignment>'|| hdr.RTP_ASSIGNMNT_C7 ||'</s1:assignment>
<s1:total_apprv_amt>'|| hdr.rpt_appr_amt ||'</s1:total_apprv_amt>
<s1:interface_date>'|| v_int_date ||'</s1:interface_date>
</s1:header>
<s1:detail>';
/**************/
FOR det IN c_get_detail(hdr.rpt_key) LOOP
v_line_xml := v_line_xml || '
<s1:line_item>
<s1:rpe_key>' || det.rpe_key || '</s1:rpe_key>
<s1:exp_type>' || det.expense_type || '</s1:exp_type>
<s1:trans_date>' || det.entry_trans_date || '</s1:trans_date>
<s1:entry_desc>' || det.entry_desc || '</s1:entry_desc>
<s1:entry_cc>' || det.entry_cc_ou3 || '</s1:entry_cc>
<s1:entry_proj>' || det.entry_proj_ou4 || '</s1:entry_proj>
<s1:entry_intord>' || det.int_order || '</s1:entry_intord>
<s1:tax_amt>' || det.tax_amount || '</s1:tax_amt>
<s1:tax_code>' || det.tax_code || '</s1:tax_code>
<s1:accnt_code>' || det.gl_account_code || '</s1:accnt_code>
<s1:sub_accnt>' || det.gl_sub_accnt || '</s1:sub_accnt>
<s1:deb_credit>' || det.debit_credit || '</s1:deb_credit>
<s1:amount>' || det.journal_amount || '</s1:amount>
<s1:net_tax_amt>' || det.net_tax_amt || '</s1:net_tax_amt>
<s1:cc_num>' || det.creditcard_num || '</s1:cc_num>
<s1:payer>' || det.payer_type || '</s1:payer>
<s1:payee>' || det.payee_type || '</s1:payee>
</s1:line_item>';
END LOOP;
v_line_xml := v_line_xml || '
</s1:detail>
</s1:era_report>';
soap_request := soap_request || v_header_xml || v_line_xml;
END LOOP;


soap_request:= soap_request || '
</s1:era_batch>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>';
/***
http_req:= utl_http.begin_request
( 'http://sangle-l-ofs.nam.slb.com/webserv/eClaims_ws.asmx'
, 'POST'
, 'HTTP/1.1'
);
utl_http.set_header(http_req, 'Content-Type', 'text/xml; charset=utf-8');
utl_http.set_header(http_req, 'Content-Length', length(soap_request));
utl_http.set_header(http_req, 'SOAPAction', 'http://eclaims.slb.com/eGateStatus');
utl_http.write_text(http_req, soap_request);
http_resp:= utl_http.get_response(http_req);
utl_http.read_text(http_resp, soap_respond);
utl_http.end_response(http_resp);
-- Create an XMLType variable containing the Response XML
resp:= XMLType.createXML(soap_respond);
-- extract from the XMLType Resp the child-nodes of the <soap:Body> element
resp:= resp.extract('/soap:Envelope/soap:Body/child::node()'
, 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
);
-- extract from the XMLType Resp the text() nodes from the n:getRateResponse/Result element
resp:= resp.extract('era_batch/webserv_response/text()', 'xmlns="http://eclaims.slb.com/"');
***/
--return resp.getClobVal();

return soap_request;
end;
/
[CODE]
Re: consuming web service using utl_dbws [message #303901 is a reply to message #303128] Mon, 03 March 2008 03:29 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
you've tried to assign value more that it can handle.correct this error by redefining variable.

regards,
Re: consuming web service using utl_dbws [message #303973 is a reply to message #303901] Mon, 03 March 2008 09:33 Go to previous messageGo to next message
sangleweb
Messages: 10
Registered: March 2006
Junior Member
I thought when I set v_line_xml CLOB := NULL and CLOB is up to 4GB which I doubt it it reaches even 5MB. What variable is giving me the problem? According to the error, it's the v_line_xml that I am having the problem with. Thanks.

The exact error is:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "CONCUR_ADMIN.SLB_PEVIWS_FUNC", line 71
ORA-06512: at line 1


mshrkshl wrote on Mon, 03 March 2008 03:29
you've tried to assign value more that it can handle.correct this error by redefining variable.

regards,


[Updated on: Mon, 03 March 2008 09:40]

Report message to a moderator

Re: consuming web service using utl_dbws [message #304030 is a reply to message #303128] Mon, 03 March 2008 15:22 Go to previous messageGo to next message
sangleweb
Messages: 10
Registered: March 2006
Junior Member
I've figured it out, I cannot assign a string to CLOB so I modify it using dbms_lob.append. It works now but here is the problem, util_http.write_text() only take VARCHAR2, but I need to pass a CLOB, how can this be accomplish?
Re: consuming web service using utl_dbws [message #304062 is a reply to message #303128] Mon, 03 March 2008 23:00 Go to previous message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
The maximum length of the URL string is limited by the capacity of the PL/SQL VARCHAR2 variable used to pass it. The “POST” method is suitable for parameterizing the request with an arbitrarily large volume of data, especially for example as might be the case when the request is expressed as an XML document.

regards,
Previous Topic: REGEXP_REPLACE: How to replace a list of words to null
Next Topic: Moving Clustered tables from one tablespace to another
Goto Forum:
  


Current Time: Mon Dec 05 06:58:33 CST 2016

Total time taken to generate the page: 0.12385 seconds