Calling a Java Webservice using PLSQL in Oracle

AlbertoFaenza

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

Tanzeel Mirza

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.

Thanks
Mirza Tanzeel

AlbertoFaenza

Hi,

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:

ACL privileges set (check ORACLE-BASE - Fine-Grained Access to Network Services in Oracle Database 11g Release 1)
dbms_java permission. If you come a cross a problem the error will say you exactly which grant you will have to give.

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.

Regards.

Alberto

AlbertoFaenza

Hi,

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;




RATE                                                                       
 



-------------
1.0886

Regards.

Alberto

John Stegeman

There could also be an issue with the HTTPS certificate not being accepted...

Tanzeel Mirza

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

PeterValencic

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;

Tanzeel Mirza

Hi Alberto

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!!

Regards

Mirza Tanzeel

1 person found this helpful

AlbertoFaenza

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';

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.

Regards.

Alberto

Tanzeel Mirza

Hi Alberto

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.

Regards

Mirza Tanzeel

Tanzeel Mirza

Hi Alberto

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

Mirza Tanzeel

AlbertoFaenza

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.

Are you sure all Java classes are valid under APPS schema?
Have you previously tried to install UTL_DWBS under SYS or SYSTEM schema?
Is the JVM properly installed in your database?

Please check if you have any "JAVA CLASS" object not valid under APPS schema or in other schema.

Regards.

Alberto

Tanzeel Mirza

Hi Alberto

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!!

Regards

Mirza Tanzeel

Tanzeel Mirza

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.

Thanks

Mirza Tanzeel

1 person found this helpful

Billy Verreynne

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.

Billy Verreynne

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.

Tanzeel Mirza

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

AlbertoFaenza

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

Hi Mizra,

I guess that message is self explaining:

ORA-24244: invalid host or port for access control list (ACL) assignment

Please check the link I sent you in my previous message explaining how to set up the ACL.

Regards.

Alberto

Tanzeel Mirza

Hi Alberto

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.

Thanks

Mirza Tanzeel

AlbertoFaenza

Hi Mizra,

"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.

Regards.

Alberto

Tanzeel Mirza

Hi Alberto

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.

Thanks

Mirza Tanzeel

1 person found this helpful

AlbertoFaenza

Hi Mizra,

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

Regards.

Alberto

1 person found this helpful

Tanzeel Mirza

Hi Alberto

I have used the same link and followed steps given there to export the certificate.

Thanks

Mirza Tanzeel

Billy Verreynne

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.

AlbertoFaenza

Hi Mizra,

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?

Regards.

Alberto

Tanzeel Mirza

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/';