Forum Stats

  • 3,733,252 Users
  • 2,246,738 Discussions
  • 7,856,634 Comments

Discussions

Calling a Java Webservice using PLSQL in Oracle

Tanzeel Mirza
Tanzeel Mirza Member Posts: 84 Gold Badge
edited April 2015 in SQL & PL/SQL

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

Krisanth Kumar2898414PeterValencic

Best Answer

  • AlbertoFaenza
    AlbertoFaenza Member Posts: 2,047 Silver Trophy
    edited April 2015 Accepted Answer

    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

«1

Answers

  • AlbertoFaenza
    AlbertoFaenza Member Posts: 2,047 Silver Trophy
    edited 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
    Tanzeel Mirza Member Posts: 84 Gold Badge
    edited 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
    AlbertoFaenza Member Posts: 2,047 Silver Trophy
    edited 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:

    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
    AlbertoFaenza Member Posts: 2,047 Silver Trophy
    edited 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
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited April 2015

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

  • Tanzeel Mirza
    Tanzeel Mirza Member Posts: 84 Gold Badge
    edited 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
    PeterValencic Member Posts: 522 Red Ribbon
    edited 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
    Tanzeel Mirza Member Posts: 84 Gold Badge
    edited 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

    2898414
  • AlbertoFaenza
    AlbertoFaenza Member Posts: 2,047 Silver Trophy
    edited 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
    Tanzeel Mirza Member Posts: 84 Gold Badge
    edited 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
    Tanzeel Mirza Member Posts: 84 Gold Badge
    edited 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.

    NoClassDefError.jpg

    Thanks

    Mirza Tanzeel

  • AlbertoFaenza
    AlbertoFaenza Member Posts: 2,047 Silver Trophy
    edited 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
    Tanzeel Mirza Member Posts: 84 Gold Badge
    edited 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
    Tanzeel Mirza Member Posts: 84 Gold Badge
    edited 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

    PeterValencic
  • Billy Verreynne
    Billy Verreynne Member Posts: 28,259 Red Diamond
    edited 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
    Billy Verreynne Member Posts: 28,259 Red Diamond
    edited 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
    Tanzeel Mirza Member Posts: 84 Gold Badge
    edited 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
    AlbertoFaenza Member Posts: 2,047 Silver Trophy
    edited 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
    Tanzeel Mirza Member Posts: 84 Gold Badge
    edited 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
    AlbertoFaenza Member Posts: 2,047 Silver Trophy
    edited 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
    Tanzeel Mirza Member Posts: 84 Gold Badge
    edited 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:

    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

    Tanzeel Mirza
  • AlbertoFaenza
    AlbertoFaenza Member Posts: 2,047 Silver Trophy
    edited 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
  • Tanzeel Mirza
    Tanzeel Mirza Member Posts: 84 Gold Badge
    edited April 2015

    Hi Alberto

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

    Thanks

    Mirza Tanzeel

  • Billy Verreynne
    Billy Verreynne Member Posts: 28,259 Red Diamond
    edited 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
    AlbertoFaenza Member Posts: 2,047 Silver Trophy
    edited 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

This discussion has been closed.