Calling a Java Webservice using PLSQL in Oracle

Tanzeel Mirza · April 2015

Hello Experts

Greetings of the day!!

We are working on one reuirement in which we have to call a Java based webservice (it is in SOA environment) from EBS PLSQL environment to get the response based on certain input parameters.

This webservice is password protected, below are the challenges that we have been facing:

1) We are not able to set the username and password needed to connect to the service when called from PLSQL.

2) Even if we connect to the webservice, there is a challenge to store the response of the Webservice.

If any one has ever faced any such requirement or has got idea of how to implement, please let us know.

Any idea will be highly appreciated.

Thanks

Mirza Tanzeel

AlbertoFaenza · April 2015

Hi Mizra,

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

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

Regards.

Alberto

AlbertoFaenza · April 2015

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

[email protected] 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 · April 2015

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 · April 2015

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 · April 2015

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 · April 2015

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

Tanzeel Mirza · April 2015

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 · April 2015

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 · April 2015

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

AlbertoFaenza · April 2015

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 · April 2015

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 · April 2015

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 · April 2015

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 · April 2015

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 · April 2015

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

Billy Verreynne · April 2015

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 · April 2015

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 · April 2015

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 · April 2015

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 · April 2015

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 · April 2015

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 · April 2015

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

AlbertoFaenza · April 2015

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

Tanzeel Mirza · April 2015

Hi Alberto

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

Thanks

Mirza Tanzeel

Billy Verreynne · April 2015

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 · April 2015

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

Forum Stats

Howdy, Stranger!

Quick Links

Categories

Calling a Java Webservice using PLSQL in Oracle

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Howdy, Stranger!

Categories

Calling a Java Webservice using PLSQL in Oracle

Best Answer

Answers