For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
Hi All, Greetings !!!! Need to install Oracle database examples 12C (realease 12.1) but nowhere I found this software. Kindly help to find and download the software. urgently needed. Thanks in advance.
Hi,
I have been using Webservice calling in Oracle 9i and Oracle 11g. Password should not be an issue.
I think the best is to use UTL_DBWS which is the package to use for web services. Keep in mind that it is not installed by default in database and you should install it.
I recommend to install it in a user schema.
I suggest you to give more information about what you have t
ried, your oracle version and some information about the web service.
Here are some links that may be helpful to you:
153 UTL_DBWS
ORACLE-BASE - UTL_DBWS - Consuming Web Services in Oracle 10g Onward
SOA@Oracle SCA, BPEL, BPM & Service Bus: Calling a BPEL process with UTL_DBWS PLSQL package
https://tomkrueger.wordpress.com/2008/09/17/how-to-call-a-web-service-from-oracle-plsql/
Regards.
Alberto
Hi Alberto
Thank you for your quick response.
First of all the information related to Oracle Version Release 11.2.0.4.0.
Webservice is a 3rd party Web service that is Java based and is in SOA environment.
We are generating an event in PLSQL and passing it to SOA along with Input parameters to get the response.
We have already tried using UTL_DBWS package and it did not help. We installed it in SYS schema and using it in APPS schema.
It won't even submit the request to call the webservice.
Below is the code that we have. Please let us know in case any modification is needed.
CREATE OR REPLACE FUNCTION SAMPLE_WEBSERVICE RETURN VARCHAR2 AS service_ Jcall.utl_dbws.SERVICE; call_ Jcall.utl_dbws.CALL; service_qname Jcall.utl_dbws.QNAME; port_qname Jcall.utl_dbws.QNAME; response XMLTYPE; request XMLTYPE; BEGIN service_qname := Jcall.utl_dbws.to_qname(NULL, 'addrval'); service_ := Jcall.utl_dbws.create_service(service_qname); call_ := Jcall.utl_dbws.create_call(service_); Jcall.utl_dbws.set_target_endpoint_address(call_, 'https://XXXXX-dev.XXXXXX.XX.com/soa-infra/services/otd/XXXXXXXXXXXService/XXXXXXXXXXvalidateservicebpel_client_ep'); Jcall.utl_dbws.set_property( call_, 'OPERATION_STYLE', 'document'); request := sys.XMLTYPE('<ns1:ADDRESS_VALIDATION_REQUEST xmlns:ns1="http://xmlns.oracle.com/XXXXXXXXXXValidateService/XXXXXXXXXXValidation"> <ns1:ADDRESS_LINE1>2316 N Peachtree Court</ns1:ADDRESS_LINE1> <ns1:ADDRESS_LINE2></ns1:ADDRESS_LINE2> <ns1:ADDRESS_LINE3></ns1:ADDRESS_LINE3> <ns1:ADDRESS_LINE4></ns1:ADDRESS_LINE4> <ns1:CITY>Atlanta</ns1:CITY> <ns1:STATE>GA</ns1:STATE> <ns1:COUNTY></ns1:COUNTY> <ns1:POSTALCODE>326</ns1:POSTALCODE> <ns1:FILLER1></ns1:FILLER1> <ns1:FILLER2></ns1:FILLER2> <ns1:ID></ns1:ID> </ns1:ADDRESS_VALIDATION_REQUEST>'); response :=Jcall.utl_dbws.invoke(call_,request); LV_STRING:= response.getstringval(); RETURN response.getstringval();
DBMS_OUTPUT.PUT_LINE (LV_STRING); END SAMPLE_WEBSERVICE;
Please let us know in case you need any more information.
ThanksMirza Tanzeel
You did not mention the exact Oracle error you get if you try to execute this code.
Please post the result when you try to use your function (ORA error and description) since I cannot test it by connecting to your service.
There might be several issue involved. To use UTL_DBWS on 11g you need to at least to have:
Also if you are calling a SOAP service be sure you have all the information you need from WSDL and if possible make a test with SoapUI to check that your request is successfully processed.
this one calling a public SOAP service is working fine in my DB:
CREATE OR REPLACE FUNCTION conversionrate (fromcurrency VARCHAR2, tocurrency VARCHAR2) RETURN VARCHAR2 AS l_soap_url VARCHAR2(1024); l_service_qname UTL_DBWS.qname; l_service UTL_DBWS.service; l_call UTL_DBWS.call; l_wsdl_url VARCHAR2(1024); l_namespace VARCHAR2(1024); l_soap_action VARCHAR2(1024); l_port_qname UTL_DBWS.qname; l_operation_qname UTL_DBWS.qname; l_request XMLTYPE; l_response XMLTYPE; l_resp_result VARCHAR2(50); l_resp_result_desc VARCHAR2(512); BEGIN l_soap_url := 'http://www.webservicex.com/currencyconvertor.asmx'; l_wsdl_url := l_soap_url||'?wsdl'; l_namespace := 'http://www.webserviceX.NET/'; l_soap_action := 'http://www.webserviceX.NET/ConversionRate'; l_service_qname := UTL_DBWS.to_qname(l_namespace, 'CurrencyConvertor'); l_port_qname := UTL_DBWS.to_qname(l_namespace, 'CurrencyConvertorSoap'); l_operation_qname := UTL_DBWS.to_qname(l_namespace, 'ConversionRate'); 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 ); UTL_DBWS.set_target_endpoint_address(l_call, l_soap_url); UTL_DBWS.set_property( l_call, 'SOAPACTION_USE', 'TRUE'); UTL_DBWS.set_property( l_call, 'SOAPACTION_URI', l_soap_action); UTL_DBWS.set_property( l_call, 'OPERATION_STYLE', 'document'); l_request := XMLTYPE( '<web:ConversionRate xmlns:web="http://www.webserviceX.NET/">' || ' <web:FromCurrency>'||fromcurrency||'</web:FromCurrency>' || ' <web:ToCurrency>'||tocurrency||'</web:ToCurrency>' || '</web:ConversionRate>'); l_response := UTL_DBWS.invoke(l_call, l_request); RETURN l_response.EXTRACT ( '//ConversionRateResult/child::text()', 'xmlns="http://www.webserviceX.NET/"').getstringval (); END; / select conversionrate('EUR', 'USD') rate from dual;
CREATE OR REPLACE FUNCTION conversionrate (fromcurrency VARCHAR2, tocurrency VARCHAR2)
RETURN VARCHAR2
AS
l_soap_url VARCHAR2(1024);
l_service_qname UTL_DBWS.qname;
l_service UTL_DBWS.service;
l_call UTL_DBWS.call;
l_wsdl_url VARCHAR2(1024);
l_namespace VARCHAR2(1024);
l_soap_action VARCHAR2(1024);
l_port_qname UTL_DBWS.qname;
l_operation_qname UTL_DBWS.qname;
l_request XMLTYPE;
l_response XMLTYPE;
l_resp_result VARCHAR2(50);
l_resp_result_desc VARCHAR2(512);
BEGIN
l_soap_url := 'http://www.webservicex.com/currencyconvertor.asmx';
l_wsdl_url := l_soap_url||'?wsdl';
l_namespace := 'http://www.webserviceX.NET/';
l_soap_action := 'http://www.webserviceX.NET/ConversionRate';
l_service_qname := UTL_DBWS.to_qname(l_namespace, 'CurrencyConvertor');
l_port_qname := UTL_DBWS.to_qname(l_namespace, 'CurrencyConvertorSoap');
l_operation_qname := UTL_DBWS.to_qname(l_namespace, 'ConversionRate');
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
UTL_DBWS.set_target_endpoint_address(l_call, l_soap_url);
UTL_DBWS.set_property( l_call, 'SOAPACTION_USE', 'TRUE');
UTL_DBWS.set_property( l_call, 'SOAPACTION_URI', l_soap_action);
UTL_DBWS.set_property( l_call, 'OPERATION_STYLE', 'document');
l_request := XMLTYPE(
'<web:ConversionRate xmlns:web="http://www.webserviceX.NET/">'
|| ' <web:FromCurrency>'||fromcurrency||'</web:FromCurrency>'
|| ' <web:ToCurrency>'||tocurrency||'</web:ToCurrency>'
|| '</web:ConversionRate>');
l_response := UTL_DBWS.invoke(l_call, l_request);
RETURN l_response.EXTRACT ( '//ConversionRateResult/child::text()', 'xmlns="http://www.webserviceX.NET/"').getstringval ();
END;
/
select conversionrate('EUR', 'USD') rate from dual;
-------------
1.0886
There could also be an issue with the HTTPS certificate not being accepted...
Thank you very much Alberto and John.
Tomorrow morning this is the first thing, I am going to try.
I did figure out about HTTPS certificate today, however not able to compile the above code now in Database due to some warning.
I will check these things tomorrow and update.
Thank you once again in advance!!
Have a great time ahead!!
Regards
Mirza Tanzeel
You can also call a webservice with utl_http package...
Here is my example,, you can see soap envelope, header and response parsing..
To see complete HTTP request and response of webservice you can download a tool called SOAPUI (SoapUI - The Home of Functional Testing)
procedure p_cancel_ws_transaction(p_wallet_certificate in varchar2 default 'file:/oracle/orasw/Wallet/Certifikat/MegaPos', -- pot do certifikata p_wallet_password in varchar2, -- geslo certifikata p_store in varchar2 default g_shop_name, -- trgovina v megaposu p_txId in varchar2, -- enolična številka transakcije p_napaka out varchar2 -- v kolikor ni null gre za napako pri klicu webservisa ) as v_soap_request_text clob; v_request UTL_HTTP.REQ; v_response UTL_HTTP.RESP; v_buffer varchar2(32767); v_response_xml clob; l_soap_env xmltype; l_soap_body xmltype; l_napaka_opis varchar2(1000); l_napaka_koda varchar2(200); l_tmp varchar2(200); l_amount varchar2(15); begin --nastavimo certifikat in geslo utl_http.set_wallet(g_lokacija_certifikata, g_geslo); v_soap_request_text := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:meg="https://service.megapos.si/megapos2.0"> <soapenv:Header/> <soapenv:Body> <meg:cancelTx> <meg:store>'|| p_store ||'</meg:store> <meg:txId>'||p_txId||'</meg:txId> </meg:cancelTx> </soapenv:Body> </soapenv:Envelope>'; --print('soap request: ' || v_soap_request_text); v_request := utl_http.begin_request(url => 'https://service.megapos.si/ws/megapos2.0/wrapped/processor', method => 'POST'); utl_http.set_header(V_REQUEST, 'User-Agent', 'Mozilla/4.0'); v_request.method := 'POST'; utl_http.set_body_charset(v_request, 'UTF8'); utl_http.set_header (R => v_request, name => 'Content-Length', value => DBMS_LOB.GETLENGTH(v_soap_request_text)); utl_http.set_header (R => v_request, name => 'SOAPAction', value => ''); utl_http.write_text (R => v_request, data => v_soap_request_text); v_response := utl_http.get_response(v_request); if (v_response.status_code <> 200) then p_napaka := 'p_cancel_ws_transaction, response status code: ' || v_response.status_code || 'Reason: ' || v_response.reason_phrase; return; end if; DBMS_LOB.createtemporary (lob_loc => v_response_xml, cache => TRUE); begin loop utl_http.read_text(v_response, v_buffer, 32767); DBMS_LOB.writeappend (v_response_xml, LENGTH (v_buffer),v_buffer); end loop; exception when utl_http.end_of_body then utl_http.end_response(v_response); end; -- variable za parsanje xml-a l_soap_env := xmltype.createxml(v_response_xml); l_soap_body := l_soap_env.extract('/soapenv:Envelope/soapenv:Body/*','xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"'); -- sprostimo clob DBMS_LOB.freetemporary(v_response_xml); --Error check if (l_soap_body.extract('/cancelTxResponse/cancelTxReturn/code/text()','xmlns="https://service.megapos.si/megapos2.0"').getStringVal() <> '0') then l_napaka_opis := l_soap_body.extract('/cancelTxResponse/cancelTxReturn/msg/text()','xmlns="https://service.megapos.si/megapos2.0"').getStringVal(); l_napaka_koda := l_soap_body.extract('/cancelTxResponse/cancelTxReturn/code/text()','xmlns="https://service.megapos.si/megapos2.0"').getStringVal(); p_napaka := 'p_order_ws_transaction, koda: ' || l_napaka_koda ||'; opis: ' || l_napaka_opis; return; end if; p_napaka :=null; exception when others then print('exception block'); print( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); utl_http.end_response(v_response); print(sqlerrm); p_napaka := sqlerrm; end;
procedure p_cancel_ws_transaction(p_wallet_certificate in varchar2 default 'file:/oracle/orasw/Wallet/Certifikat/MegaPos', -- pot do certifikata
p_wallet_password in varchar2, -- geslo certifikata
p_store in varchar2 default g_shop_name, -- trgovina v megaposu
p_txId in varchar2, -- enolična številka transakcije
p_napaka out varchar2 -- v kolikor ni null gre za napako pri klicu webservisa
) as
v_soap_request_text clob;
v_request UTL_HTTP.REQ;
v_response UTL_HTTP.RESP;
v_buffer varchar2(32767);
v_response_xml clob;
l_soap_env xmltype;
l_soap_body xmltype;
l_napaka_opis varchar2(1000);
l_napaka_koda varchar2(200);
l_tmp varchar2(200);
l_amount varchar2(15);
begin
--nastavimo certifikat in geslo
utl_http.set_wallet(g_lokacija_certifikata, g_geslo);
v_soap_request_text := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:meg="https://service.megapos.si/megapos2.0">
<soapenv:Header/>
<soapenv:Body>
<meg:cancelTx>
<meg:store>'|| p_store ||'</meg:store>
<meg:txId>'||p_txId||'</meg:txId>
</meg:cancelTx>
</soapenv:Body>
</soapenv:Envelope>';
--print('soap request: ' || v_soap_request_text);
v_request := utl_http.begin_request(url => 'https://service.megapos.si/ws/megapos2.0/wrapped/processor', method => 'POST');
utl_http.set_header(V_REQUEST, 'User-Agent', 'Mozilla/4.0');
v_request.method := 'POST';
utl_http.set_body_charset(v_request, 'UTF8');
utl_http.set_header (R => v_request, name => 'Content-Length', value => DBMS_LOB.GETLENGTH(v_soap_request_text));
utl_http.set_header (R => v_request, name => 'SOAPAction', value => '');
utl_http.write_text (R => v_request, data => v_soap_request_text);
v_response := utl_http.get_response(v_request);
if (v_response.status_code <> 200) then
p_napaka := 'p_cancel_ws_transaction, response status code: ' || v_response.status_code || 'Reason: ' || v_response.reason_phrase;
return;
end if;
DBMS_LOB.createtemporary (lob_loc => v_response_xml, cache => TRUE);
loop
utl_http.read_text(v_response, v_buffer, 32767);
DBMS_LOB.writeappend (v_response_xml, LENGTH (v_buffer),v_buffer);
end loop;
exception when utl_http.end_of_body then
utl_http.end_response(v_response);
end;
-- variable za parsanje xml-a
l_soap_env := xmltype.createxml(v_response_xml);
l_soap_body := l_soap_env.extract('/soapenv:Envelope/soapenv:Body/*','xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"');
-- sprostimo clob
DBMS_LOB.freetemporary(v_response_xml);
--Error check
if (l_soap_body.extract('/cancelTxResponse/cancelTxReturn/code/text()','xmlns="https://service.megapos.si/megapos2.0"').getStringVal() <> '0') then
l_napaka_opis := l_soap_body.extract('/cancelTxResponse/cancelTxReturn/msg/text()','xmlns="https://service.megapos.si/megapos2.0"').getStringVal();
l_napaka_koda := l_soap_body.extract('/cancelTxResponse/cancelTxReturn/code/text()','xmlns="https://service.megapos.si/megapos2.0"').getStringVal();
p_napaka := 'p_order_ws_transaction, koda: ' || l_napaka_koda ||'; opis: ' || l_napaka_opis;
p_napaka :=null;
exception
when others then
print('exception block');
print( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
print(sqlerrm);
p_napaka := sqlerrm;
We are able to successfully compile the function, however when we are trying to execute then getting the below error:
ORA-29540: class oracle/jpub/runtime/dbws/DbwsProxy does not exist
So, I tried the code that you pasted above and was able to sucessfuly compile, however while executing the above error popped up.
I believe there are some DBWS files (e.g dbwsa.jar and dbwsclient.jar) missing in our instance.
Also, I queried the below and got no rows.
select status from all_objects where
dbms_java.longname(object_name)='oracle/jpub/runtime/dbws/DbwsProxy';
Please confirm if my understanding is correct and also suggest any action plan that I can follow to successfully execute it.
Tagging Priyanka: @"2898414"
Thanks in advance!!
It looks something is wrong in your UTL_DBWS installation.
I have all DBWS objects in a user schema and running the following statement:
select dbms_java.longname(object_name) java_longname , owner from all_objects ao where object_name like '%DbwsProxy%' and object_type = 'JAVA CLASS' and status='VALID';
select dbms_java.longname(object_name) java_longname
, owner
from all_objects ao
where object_name like '%DbwsProxy%'
and object_type = 'JAVA CLASS'
and status='VALID';
I got the following result (all UTL_DBWS objects are in user schema SCOTT in my test database, not in SYS or SYSTEM):
JAVA_LONGNAME OWNER -------------------------------------------------- ----- oracle/jpub/runtime/dbws/DbwsProxy$1 SCOTT oracle/jpub/runtime/dbws/DbwsProxy$CallProxy SCOTT oracle/jpub/runtime/dbws/DbwsProxy$ServiceProxy SCOTT oracle/jpub/runtime/dbws/DbwsProxy SCOTT 4 rows selected.
JAVA_LONGNAME OWNER
-------------------------------------------------- -----
oracle/jpub/runtime/dbws/DbwsProxy$1 SCOTT
oracle/jpub/runtime/dbws/DbwsProxy$CallProxy SCOTT
oracle/jpub/runtime/dbws/DbwsProxy$ServiceProxy SCOTT
oracle/jpub/runtime/dbws/DbwsProxy SCOTT
4 rows selected.
I have realized that there is some issue with DBWS in our DEV environment, so I have created an action plan to reach out to our Admin Team to get the environment properly set up with respect to DBWS.
Once the enviroment is ready, I will go ahead and start calling the webservice to verify if it works.
I will update on the progress or any issue happening down the line.
Thank you very much for your co-operation.
We followed the following document Doc ID 841183.1 to set up our environment. We installed it APPS Schema.
After the installation, we tried to run the Sample code to call webservice which is there in the same link.
Now, we are getting this error as shown in the image.
Thanks
Hi Mizra,
if you have run the code I have sent you which is working fine on my DB and it is not working on yours most probably there are still some installation issues on your DB.
From what I see it seems that there is one class missing "Java.lang.NoClassDefFoundError" but I cannot understand which class one from your message.
Please check if you have any "JAVA CLASS" object not valid under APPS schema or in other schema.
I am able to successfully run the sample code on the doc ID 841183.1 after passing the correct proxy so looks like UTL_DBWS is properly set up.
Now the problem that I am facing is that my code to call the web-service is not working seems the service is secured using password protection.
I will paste my code tomorrow in this one, probably you can help me more on this.
Thank you very much so far with your help.
Have a great day ahead!!
Figured out UTL_DBWS utility doesn't support the secure web-service security authentication.
It can only do basic authentication at the transport layer.
I am going ahead with UTL_HTTP now to achieve the requirement. I will let you know of the progress and issue if any.
UTL_DBWS is a piece of junk in my opinion. Am using UTL_HTTP extensively for communicating with web services.
Full example of the method used shown in
Remember that in 11g and later, you need to punch holes for SQL and PL/SQL code to access the outside world via network ACLs.
The fact that you pass the SQL exception code as an output parameter, turns what looks like well written code, into shoddy code.
There is no reason to subvert exception handling in PL/SQL, and replace that by passing error codes to the caller as output parameters.
Hi Billy
I am able to call an unsecured webservice using UTL_HTTP utility.
In order to call secured WS, I am trying to assign using ACL command it gives the following error:
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL( acl => 'www.xml',
host => 'https://XXXsoa-dev.XXXXXXX.XX.com');
ERROR at line 1:
ORA-24244: invalid host or port for access control list (ACL) assignment
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 168
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 491
ORA-06512: at line 1
Not sure what is the problem happening. Researching further.
Tanzeel Mirza wrote: Hi Billy I am able to call an unsecured webservice using UTL_HTTP utility. In order to call secured WS, I am trying to assign using ACL command it gives the following error: DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL( acl => 'www.xml', host => 'https://XXXsoa-dev.XXXXXXX.XX.com'); ERROR at line 1: ORA-24244: invalid host or port for access control list (ACL) assignment ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 168 ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 491 ORA-06512: at line 1 Not sure what is the problem happening. Researching further. Thanks Mirza Tanzeel
Tanzeel Mirza wrote:
I guess that message is self explaining:
Please check the link I sent you in my previous message explaining how to set up the ACL.
I am able to fix that part.
It doesnt need http / https part of the host.
Just by passing the host name XXXsoa......com. I am able to create the access list.
However, I am facing a challenge that after loading the certificate for the secured website, I am not able to call the website.
It works for few other secured website using the same wallet.
Confused.. what is happenng.
"I am not able to call the website" is a bit vague.
Can you post the exact error you are getting?
If you can connect to some other secured website then you had a certificate problem.
Is the correct certificate stored in your Oracle wallet?
Also check if you are able to connect via SoapUI to that SOAP service.
This is the error that I am getting eventhough I have the certificate imported in the server.
-29273ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1525
ORA-29261: bad argument
ORA-06512: at "APPS.SHOW_HTML_FROM_URL", line 23
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation failure
After going to this metalink document: Configuring Wallet Manager To Enable HTTPS Connections Via UTL_HTTP.REQUEST (Doc ID 169768.1)
what I can think is that something is related to the certificate.
So, I am going to copy the certificate now in some other format and import it on the server.
Will update on the result.
the validation of your certificate is failing.
You can follow the instruction here to add the certificate in your Oracle Wallet: ORACLE-BASE - UTL_HTTP and SSL (HTTPS) using Oracle Wallets
I have used the same link and followed steps given there to export the certificate.
Note that using SSL3 will likely result in a failure - TLS1 (SSL1) protocol is recommended. Make sure that TLS1 is indeed used.
Also, the Oracle Wallet needs to be configured for "auto login", have a path that is accessible to the local Oracle database server process, and needs valid credentials to be supplied.
The wallet needs to not only contain the web site's certificate, but also the root certificate(s) of the signing authority that signed that web site's certificate. The complete certificate chain is needed in the wallet.
Did you save the certificate of the server for each node in the Certification Path?
Did you save the certificate as "Base-64 encoded X.509 (.CER)"?
Do you see the certificates in your Oracle wallet (use command orapki wallet {yourwallet} display ...)
And again: are you able to connect by using SoapUI tool?
Hi @"AlbertoFaenza"
I am able to call the secured web-service successfully and able to get the response from the same.
However, there is one issue I am facing is that not able to extract the values from the response XML type.
This is the response that I get in CLOB and then I convert to XMLType using XMLType.createXML.
Here is the CLOB and after converting into XMLType, it looks the same.
</env:Header>
<env:Body>
<ADDRESS_VALIDATION_RESPONSE xmlns:ns1="http://xmlns.oracle.com/Group1AddressValidateService/AddressValidation" xmlns="http://xmlns.oracle.com/Group1AddressValidateService/AddressValidation">
<ns1:MESSAGE_LENGTH>00282</ns1:MESSAGE_LENGTH>
<ns1:MESSAGE_COUNT>00001</ns1:MESSAGE_COUNT>
<ns1:MESSAGE_TRANSACTION_TYPE/>
<ns1:FILLER/>
<ns1:ADDRESS_LINE1>6200 E Sam Houston Pkwy N </ns1:ADDRESS_LINE1>
<ns1:ADDRESS_LINE2>?</ns1:ADDRESS_LINE2>
<ns1:ADDRESS_LINE3/>
<ns1:ADDRESS_LINE4/>
<ns1:CITY>Houston </ns1:CITY>
<ns1:COUNTY>Harris </ns1:COUNTY>
<ns1:STATE>TX</ns1:STATE>
<ns1:POSTALCODE>770497260</ns1:POSTALCODE>
<ns1:LATITUDE/>
<ns1:LONGITUDE/>
<ns1:MINOR_ERROR>N</ns1:MINOR_ERROR>
<ns1:OVR_CORRECT>2</ns1:OVR_CORRECT>
<ns1:GENERAL/>
<ns1:SFX/>
<ns1:APT/>
<ns1:USPS_TYPE/>
<ns1:DIRECTION/>
<ns1:CITY_ANSWER>B</ns1:CITY_ANSWER>
<ns1:GROUP_ONE_CALL_STATUS>SUCCESS</ns1:GROUP_ONE_CALL_STATUS>
<ns1:GROUP_ONE_CALL_ERROR/>
</ADDRESS_VALIDATION_RESPONSE>
</env:Body>
</env:Envelope>
I am trying to extract the value for Address Fields, city,county etc. In order to do that I am using following code and it is not working, request you to please let me know in case I need to make any correction.
l_resp_xml is an XMLType Variabe.
l_NAMESPACE_SOAP VARCHAR2(512) := 'xmlns="http://schemas.xmlsoap.org/soap/envelope"';
IF(l_http_response.status_code = 200) THEN
-- Create XML type from response text
l_resp_xml := XMLType.createXML(l_clob_response);
--dbms_output.put_line ( l_resp_xml);
-- Clean SOAP header
SELECT EXTRACT(l_resp_xml, 'Envelope/Header/Body/ADDRESS_VALIDATION_RESPONSE', l_NAMESPACE_SOAP)
-- SELECT EXTRACT(l_resp_xml, 'Envelope/Header/Body/node()', l_NAMESPACE_SOAP)
INTO l_resp_xml
FROM dual; --
-- Extract Address1
l_result_XML_node := 'ADDRESS_VALIDATION_RESPONSE/';
SELECT EXTRACTVALUE(l_resp_xml, l_result_XML_node
|| 'ADDRESS_VALIDATION_RESPONSE[1]/ns1:ADDRESS_LINE1[1]', 'xmlns="http://xmlns.oracle.com/Group1AddressValidateService/AddressValidation/"')
INTO l_response_address1
FROM dual;
|| 'ADDRESS_LINE2[1]', 'xmlns="http://xmlns.oracle.com/Group1AddressValidateService/AddressValidation/"')
INTO l_response_address2
|| 'ADDRESS_LINE3[1]', 'xmlns="http://xmlns.oracle.com/Group1AddressValidateService/AddressValidation/"')
INTO l_response_address3
|| 'ADDRESS_LINE4[1]', 'xmlns="http://xmlns.oracle.com/Group1AddressValidateService/AddressValidation/"')
INTO l_response_address4
|| 'CITY[1]', 'xmlns="http://xmlns.oracle.com/Group1AddressValidateService/AddressValidation/"')
INTO l_response_city
|| 'State[1]', 'http://xmlns.oracle.com/Group1AddressValidateService/AddressValidation/"')
INTO l_response_state
|| 'COUNTY[1]', 'http://xmlns.oracle.com/Group1AddressValidateService/AddressValidation/"')
INTO l_response_county
END IF;
please check if you posted exactly what you are receiving.
The XML is not valid:
The character slash "/" in front of Header is wrong
The env namespace is not declared
the field env:Header is not closed
the field env:Envelope is not open.
Please be sure that you have a valid XML first and then we can check it.
Also note that EXTRACTVALUE is deprecated in 11gR2. Please check here: http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions061.htm#SQLRF06173
Thats was a pasting problem, here is the XML with
and this the name space : l_NAMESPACE_SOAP VARCHAR2(512) := 'xmlns="http://schemas.xmlsoap.org/soap/envelope"';
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing">
<env:Header>
<wsa:MessageID>urn:936AE390E76011E4BFC99D8059113E1A</wsa:MessageID>
<wsa:ReplyTo>
<wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address>
</wsa:ReplyTo>
<wsa:FaultTo>
</wsa:FaultTo>
don't make it too complicated.
To extract value from XML you can follow one of the 2 example below according to the DB version you have. If you have 11gR2 or greater then use the second way:
declare l_xml_response xmltype; l_xml_ns1 varchar2(200); l_response_address1 varchar2(200); l_response_address2 varchar2(200); l_response_address3 varchar2(200); l_response_address4 varchar2(200); l_response_city varchar2(200); l_response_state varchar2(200); l_response_county varchar2(200); begin l_xml_response := xmltype(' <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing"> <env:Header> <wsa:MessageID>urn:936AE390E76011E4BFC99D8059113E1A</wsa:MessageID> <wsa:ReplyTo> <wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address> </wsa:ReplyTo> <wsa:FaultTo> <wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address> </wsa:FaultTo> </env:Header> <env:Body> <ADDRESS_VALIDATION_RESPONSE xmlns:ns1="http://xmlns.oracle.com/Group1AddressValidateService/AddressValidation" xmlns="http://xmlns.oracle.com/Group1AddressValidateService/AddressValidation"> <ns1:MESSAGE_LENGTH>00282</ns1:MESSAGE_LENGTH> <ns1:MESSAGE_COUNT>00001</ns1:MESSAGE_COUNT> <ns1:MESSAGE_TRANSACTION_TYPE/> <ns1:FILLER/> <ns1:ADDRESS_LINE1>6200 E Sam Houston Pkwy N </ns1:ADDRESS_LINE1> <ns1:ADDRESS_LINE2>?</ns1:ADDRESS_LINE2> <ns1:ADDRESS_LINE3/> <ns1:ADDRESS_LINE4/> <ns1:CITY>Houston </ns1:CITY> <ns1:COUNTY>Harris </ns1:COUNTY> <ns1:STATE>TX</ns1:STATE> <ns1:POSTALCODE>770497260</ns1:POSTALCODE> <ns1:LATITUDE/> <ns1:LONGITUDE/> <ns1:MINOR_ERROR>N</ns1:MINOR_ERROR> <ns1:OVR_CORRECT>2</ns1:OVR_CORRECT> <ns1:GENERAL/> <ns1:SFX/> <ns1:APT/> <ns1:USPS_TYPE/> <ns1:DIRECTION/> <ns1:CITY_ANSWER>B</ns1:CITY_ANSWER> <ns1:GROUP_ONE_CALL_STATUS>SUCCESS</ns1:GROUP_ONE_CALL_STATUS> <ns1:GROUP_ONE_CALL_ERROR/> </ADDRESS_VALIDATION_RESPONSE> </env:Body> </env:Envelope>'); l_xml_ns1:= 'xmlns:ns1="http://xmlns.oracle.com/Group1AddressValidateService/AddressValidation"'; -- pre 11gR2 (using extractvalue) select extractvalue( l_xml_response, '//ns1:ADDRESS_LINE1', l_xml_ns1) as address1 , extractvalue( l_xml_response, '//ns1:ADDRESS_LINE2', l_xml_ns1) as address2 , extractvalue( l_xml_response, '//ns1:ADDRESS_LINE3', l_xml_ns1) as address3 , extractvalue( l_xml_response, '//ns1:ADDRESS_LINE4', l_xml_ns1) as address4 , extractvalue( l_xml_response, '//ns1:CITY' , l_xml_ns1) as city , extractvalue( l_xml_response, '//ns1:STATE' , l_xml_ns1) as state , extractvalue( l_xml_response, '//ns1:COUNTY' , l_xml_ns1) as county into l_response_address1 , l_response_address2 , l_response_address3 , l_response_address4 , l_response_city , l_response_state , l_response_county from dual; dbms_output.put_line('Pre 11gR2:'); dbms_output.put_line('Address1 :'||l_response_address1); dbms_output.put_line('Address2 :'||l_response_address2); dbms_output.put_line('Address3 :'||l_response_address3); dbms_output.put_line('Address4 :'||l_response_address4); dbms_output.put_line('City :'||l_response_city); dbms_output.put_line('State :'||l_response_state); dbms_output.put_line('County :'||l_response_county); -- 11gR2 onwards select x.address1 , x.address2 , x.address3 , x.address4 , x.city , x.state , x.county into l_response_address1 , l_response_address2 , l_response_address3 , l_response_address4 , l_response_city , l_response_state , l_response_county from xmltable( xmlnamespaces( default 'http://xmlns.oracle.com/Group1AddressValidateService/AddressValidation' , 'http://xmlns.oracle.com/Group1AddressValidateService/AddressValidation' as "ns1" ) , '//ADDRESS_VALIDATION_RESPONSE' passing l_xml_response columns "ADDRESS1" varchar2(200) path 'ns1:ADDRESS_LINE1' , "ADDRESS2" varchar2(200) path 'ns1:ADDRESS_LINE2' , "ADDRESS3" varchar2(200) path 'ns1:ADDRESS_LINE3' , "ADDRESS4" varchar2(200) path 'ns1:ADDRESS_LINE4' , "CITY" varchar2(200) path 'ns1:CITY' , "STATE" varchar2(200) path 'ns1:STATE' , "COUNTY" varchar2(200) path 'ns1:COUNTY' ) x; dbms_output.put_line('------------------'); dbms_output.put_line('11gR2 onwards:'); dbms_output.put_line('Address1 :'||l_response_address1); dbms_output.put_line('Address2 :'||l_response_address2); dbms_output.put_line('Address3 :'||l_response_address3); dbms_output.put_line('Address4 :'||l_response_address4); dbms_output.put_line('City :'||l_response_city); dbms_output.put_line('State :'||l_response_state); dbms_output.put_line('County :'||l_response_county); end; /
declare
l_xml_response xmltype;
l_xml_ns1 varchar2(200);
l_response_address1 varchar2(200);
l_response_address2 varchar2(200);
l_response_address3 varchar2(200);
l_response_address4 varchar2(200);
l_response_city varchar2(200);
l_response_state varchar2(200);
l_response_county varchar2(200);
l_xml_response := xmltype('
</env:Envelope>');
l_xml_ns1:= 'xmlns:ns1="http://xmlns.oracle.com/Group1AddressValidateService/AddressValidation"';
-- pre 11gR2 (using extractvalue)
select extractvalue( l_xml_response, '//ns1:ADDRESS_LINE1', l_xml_ns1) as address1
, extractvalue( l_xml_response, '//ns1:ADDRESS_LINE2', l_xml_ns1) as address2
, extractvalue( l_xml_response, '//ns1:ADDRESS_LINE3', l_xml_ns1) as address3
, extractvalue( l_xml_response, '//ns1:ADDRESS_LINE4', l_xml_ns1) as address4
, extractvalue( l_xml_response, '//ns1:CITY' , l_xml_ns1) as city
, extractvalue( l_xml_response, '//ns1:STATE' , l_xml_ns1) as state
, extractvalue( l_xml_response, '//ns1:COUNTY' , l_xml_ns1) as county
into l_response_address1
, l_response_address2
, l_response_address3
, l_response_address4
, l_response_city
, l_response_state
, l_response_county
from dual;
dbms_output.put_line('Pre 11gR2:');
dbms_output.put_line('Address1 :'||l_response_address1);
dbms_output.put_line('Address2 :'||l_response_address2);
dbms_output.put_line('Address3 :'||l_response_address3);
dbms_output.put_line('Address4 :'||l_response_address4);
dbms_output.put_line('City :'||l_response_city);
dbms_output.put_line('State :'||l_response_state);
dbms_output.put_line('County :'||l_response_county);
-- 11gR2 onwards
select x.address1
, x.address2
, x.address3
, x.address4
, x.city
, x.state
, x.county
from xmltable( xmlnamespaces( default 'http://xmlns.oracle.com/Group1AddressValidateService/AddressValidation'
, 'http://xmlns.oracle.com/Group1AddressValidateService/AddressValidation' as "ns1"
)
, '//ADDRESS_VALIDATION_RESPONSE'
passing l_xml_response
columns "ADDRESS1" varchar2(200) path 'ns1:ADDRESS_LINE1'
, "ADDRESS2" varchar2(200) path 'ns1:ADDRESS_LINE2'
, "ADDRESS3" varchar2(200) path 'ns1:ADDRESS_LINE3'
, "ADDRESS4" varchar2(200) path 'ns1:ADDRESS_LINE4'
, "CITY" varchar2(200) path 'ns1:CITY'
, "STATE" varchar2(200) path 'ns1:STATE'
, "COUNTY" varchar2(200) path 'ns1:COUNTY'
) x;
dbms_output.put_line('------------------');
dbms_output.put_line('11gR2 onwards:');
Here it the output of the script above:
Pre 11gR2: Address1 :6200 E Sam Houston Pkwy N Address2 : ? Address3 : Address4 : City :Houston State :TX County :Harris ------------------ 11gR2 onwards: Address1 :6200 E Sam Houston Pkwy N Address2 : ? Address3 : Address4 : City :Houston State :TX County :Harris
Pre 11gR2:
Address1 :6200 E Sam Houston Pkwy N
Address2 : ?
Address3 :
Address4 :
City :Houston
State :TX
County :Harris
------------------
11gR2 onwards:
I am able to make it working with my existing code which is identical to example 1.
Thank you very much for your help throughout.