How to Parse XML using DOM Parser in Oracle

SET DEFINE OFF;
SET SERVEROUTPUT ON;

DECLARE
   v_mds_xml_parser           DBMS_XMLPARSER.parser;
   v_mds_xml_doc              DBMS_XMLDOM.domdocument;
   v_Release_node             xmldom.domnode;
   node_not_exists            EXCEPTION;
   v_ShipToLocationRef_list   xmldom.domnodelist;
   v_ShipToLocationRef_node   xmldom.domnode;
   v_LocationRef_list         xmldom.domnodelist;
   v_LocationRef_node         xmldom.domnode;
   v_Location_list            xmldom.domnodelist;
   v_Location_node            xmldom.domnode;
   v_TransactionCode          VARCHAR2 (1000) := NULL;
   v_LocationGid_list         xmldom.domnodelist;
   v_LocationGid_node         xmldom.domnode;
   v_Gid_list                 xmldom.domnodelist;
   v_Gid_node                 xmldom.domnode;
   v_DomainName               VARCHAR2 (1000) := NULL;
   v_Xid                      VARCHAR2 (1000) := NULL;
   v_LocationName             VARCHAR2 (1000) := NULL;
   v_Address_list             xmldom.domnodelist;
   v_Address_node             xmldom.domnode;
   v_AddressLine1             VARCHAR2 (100) := NULL;
   v_AddressLine2             VARCHAR2 (100) := NULL;
   v_City                     VARCHAR2 (100) := NULL;
   v_Province                 VARCHAR2 (100) := NULL;
   v_PostalCode               VARCHAR2 (100) := NULL;
   v_CountryCode_list         xmldom.domnodelist;
   v_CountryCode_node         xmldom.domnode;
   v_CountryCode              VARCHAR2 (100) := NULL;

   reqxml                     CLOB
      := '<sample:Release xmlns:sample="http://xmlns.oracle.com/apps/sample/transmission/v6.4" xmlns:gtm="http://xmlns.oracle.com/apps/gtm/transmission/v6.4">
    <sample:ReleaseGid>
        <sample:Gid>
            <sample:DomainName>ABC</sample:DomainName>
            <sample:Xid>1234</sample:Xid>
        </sample:Gid>
    </sample:ReleaseGid>
    <sample:TransactionCode>CR</sample:TransactionCode>
    <sample:ShipToLocationRef>
        <sample:LocationRef>
            <sample:Location>
                <sample:TransactionCode>KK</sample:TransactionCode>
                <sample:LocationGid>
                    <sample:Gid>
                        <sample:DomainName>ABC</sample:DomainName>
                        <sample:Xid>87901111</sample:Xid>
                    </sample:Gid>
                </sample:LocationGid>
                <sample:LocationName>MADIWALA</sample:LocationName>
                <sample:Address>
                    <sample:AddressLine1>41, BTM LAYOUT</sample:AddressLine1>
                    <sample:AddressLine2/>
                    <sample:City>BANGALORE</sample:City>
                    <sample:Province/>
                    <sample:PostalCode>560076</sample:PostalCode>
                    <sample:CountryCode>
                        <sample:CountryCode2>KR</sample:CountryCode2>
                    </sample:CountryCode>
                </sample:Address>
                <sample:Contact>
                    <sample:ContactGid>
                        <sample:Gid>
                            <sample:DomainName>ABC</sample:DomainName>
                            <sample:Xid>87901111</sample:Xid>
                        </sample:Gid>
                    </sample:ContactGid>
                    <sample:IsPrimaryContact>Y</sample:IsPrimaryContact>
                </sample:Contact>
                <sample:LocationRole>
                    <sample:LocationRoleGid>
                        <sample:Gid>
                            <sample:DomainName>PUBLIC</sample:DomainName>
                            <sample:Xid>SHIPFROM/SHIPTO</sample:Xid>
                        </sample:Gid>
                    </sample:LocationRoleGid>
                </sample:LocationRole>
            </sample:Location>
        </sample:LocationRef>
    </sample:ShipToLocationRef>
</sample:Release>';

   FUNCTION f_verify_and_gettag_value (i_elem       xmldom.domelement,
                                       i_tagname    VARCHAR2)
      RETURN VARCHAR2
   IS
      pnodelist            xmldom.domnodelist;
      pnode                xmldom.domnode;
      pelement             xmldom.domelement;
      e_xmltag_notexists   EXCEPTION;
   BEGIN
      pnodelist := xmldom.getchildrenbytagname (i_elem, i_tagname);
      pnode := xmldom.item (pnodelist, 0);

      IF (xmldom.isnull (pnode))
      THEN
         RAISE e_xmltag_notexists;
      END IF;

      IF xmldom.haschildnodes (pnode)
      THEN
         IF (xmldom.getnodetype (pnode) = xmldom.element_node)
         THEN
            pnode := xmldom.getfirstchild (pnode);
            RETURN (xmldom.getnodevalue (pnode));
         END IF;
      END IF;

      RETURN NULL;
   EXCEPTION
      WHEN e_xmltag_notexists
      THEN
         RETURN NULL;
   END f_verify_and_gettag_value;

   PROCEDURE p_get_node_value (io_outval   IN OUT VARCHAR2,
                               i_tagnode          xmldom.domnode,
                               i_tagname          VARCHAR2)
   IS
      tagval               VARCHAR2 (30000);
      e_xmltag_notexists   EXCEPTION;
   BEGIN
      BEGIN
         tagval :=
            f_verify_and_gettag_value (xmldom.makeelement (i_tagnode),
                                       i_tagname);
         io_outval := tagval;
      EXCEPTION
         WHEN e_xmltag_notexists
         THEN
            io_outval := NULL;
            -- mr8328 on 12/01/2016
            -- do not set out val if already exists as tag itself not there
            NULL;
      END;
   END p_get_node_value;
BEGIN
   reqxml := REPLACE (reqxml, '&', CHR (ASCII ('&')) || 'amp;'); -- To handle &

   v_mds_xml_parser := DBMS_XMLPARSER.newparser;
   xmlparser.parseclob (v_mds_xml_parser, reqxml);
   v_mds_xml_doc := DBMS_XMLPARSER.getdocument (v_mds_xml_parser);


   /* Checking Parent Node is exists or Not and Raising Exception if not present*/
   v_Release_node :=
      xmldom.item (xmldom.getelementsbytagname (v_mds_xml_doc, 'Release'), 0);

   IF (xmldom.isnull (v_Release_node))
   THEN
      RAISE node_not_exists;
   END IF;

   /*ShipToLocationRef */
   v_ShipToLocationRef_list :=
      xmldom.getchildrenbytagname (xmldom.makeelement (v_Release_node),
                                   'ShipToLocationRef');
   v_ShipToLocationRef_node := xmldom.item (v_ShipToLocationRef_list, 0);

   /*LocationRef */
   v_LocationRef_list :=
      xmldom.getchildrenbytagname (
         xmldom.makeelement (v_ShipToLocationRef_node),
         'LocationRef');
   v_LocationRef_node := xmldom.item (v_LocationRef_list, 0);

   /*Location */
   v_Location_list :=
      xmldom.getchildrenbytagname (xmldom.makeelement (v_LocationRef_node),
                                   'Location');
   v_Location_node := xmldom.item (v_Location_list, 0);


   p_get_node_value (v_TransactionCode, v_Location_node, 'TransactionCode');
   DBMS_OUTPUT.PUT_LINE ('v_TransactionCode=' || v_TransactionCode);

   /*LocationGid */
   v_LocationGid_list :=
      xmldom.getchildrenbytagname (xmldom.makeelement (v_LocationRef_node),
                                   'LocationGid');
   v_LocationGid_node := xmldom.item (v_LocationGid_list, 0);

   /*Gid */
   v_Gid_list :=
      xmldom.getchildrenbytagname (xmldom.makeelement (v_LocationGid_node),
                                   'Gid');
   v_Gid_node := xmldom.item (v_Gid_list, 0);

   p_get_node_value (v_DomainName, v_Gid_node, 'DomainName');
   p_get_node_value (v_Xid, v_Gid_node, 'Xid');
   DBMS_OUTPUT.PUT_LINE ('v_DomainName=' || v_DomainName);
   DBMS_OUTPUT.PUT_LINE ('v_Xid=' || v_Xid);

   p_get_node_value (v_LocationName, v_Location_node, 'TransactionCode');
   DBMS_OUTPUT.PUT_LINE ('v_LocationName=' || v_LocationName);

   /*Address*/
   v_Address_list :=
      xmldom.getchildrenbytagname (xmldom.makeelement (v_Location_node),
                                   'Address');
   v_Address_node := xmldom.item (v_Address_list, 0);
   p_get_node_value (v_AddressLine1, v_Address_node, 'AddressLine1');
   p_get_node_value (v_AddressLine2, v_Address_node, 'AddressLine2');
   p_get_node_value (v_city, v_Address_node, 'City');
   p_get_node_value (v_Province, v_Address_node, 'Province');
   p_get_node_value (v_PostalCode, v_Address_node, 'PostalCode');

   DBMS_OUTPUT.PUT_LINE ('v_AddressLine1=' || v_AddressLine1);
   DBMS_OUTPUT.PUT_LINE ('v_AddressLine2=' || v_AddressLine2);
   DBMS_OUTPUT.PUT_LINE ('v_city=' || v_city);
   DBMS_OUTPUT.PUT_LINE ('v_Province=' || v_Province);
   DBMS_OUTPUT.PUT_LINE ('v_PostalCode=' || v_PostalCode);

   /*CountryCode*/
   v_CountryCode_list :=
      xmldom.getchildrenbytagname (xmldom.makeelement (v_Address_node),
                                   'Address');
   v_CountryCode_node := xmldom.item (v_Address_list, 0);

   p_get_node_value (v_CountryCode, v_CountryCode_node, 'CountryCode');
   DBMS_OUTPUT.PUT_LINE ('v_CountryCode=' || v_CountryCode);
EXCEPTION
   WHEN node_not_exists
   THEN
      DBMS_OUTPUT.put_line ('No node exists with the request XML');
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
            'Oracle Error Number:'
         || SQLCODE
         || 'Oracle Error Message:'
         || SQLERRM);
END;
/

v_TransactionCode=NP

v_DomainName=

v_Xid=

v_LocationName=NP

v_AddressLine1=189, GASAN DIGITAL 1-RO

v_AddressLine2=

v_city=GEUMCHEON-GU

v_Province=

v_PostalCode=08503

v_CountryCode=

PL/SQL procedure successfully completed.

SQL>

XML Parsing Example

SET DEFINE OFF;
SET SERVEROUT ON;

DECLARE
   v_mds_xml_parser        DBMS_XMLPARSER.parser;
   v_mds_xml_doc           DBMS_XMLDOM.domdocument;
   v_inq_entrp_ordr_node   xmldom.domnode;
   v_OrderList             xmldom.domnodelist;
   v_orderlist_node        xmldom.domnode;
   v_activityType_list     xmldom.domnodelist;
   v_actvity_node          xmldom.domnode;
   text_node               xmldom.domnode;
   v_activityType          VARCHAR2 (200) := NULL;
   i_reqxml                CLOB
      := '<?xml version="1.0" encoding="UTF-8"?>
<!--Sample XML file generated by XMLSpy v2013 rel. 2 sp2 (x64) (http://www.altova.com)-->
<InquirePendingEnterpriseOrderResponse xsi:schemaLocation="http://csi.cingular.com/CSI/Namespaces/Container/Public/InquirePendingEnterpriseOrderResponse.xsd InquirePendingEnterpriseOrderResponse.xsd" xmlns="http://csi.cingular.com/CSI/Namespaces/Container/Public/InquirePendingEnterpriseOrderResponse.xsd" xmlns:cdm="http://csi.att.com/CSI/Namespaces/GlobalIntegratedOrderManager/Types/Public/CommonDataModel.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <OrderList>
        <orderNumber>GIOM234</orderNumber>
        <orderType>MACDs</orderType>
        <activityType>GIOM234_1</activityType>
        <activityType>GIOM234_2</activityType>
    </OrderList>
    <OrderList>
        <orderNumber>GIOM789</orderNumber>
        <orderType>New Start</orderType>
        <activityType>GIOM789_1</activityType>
        <activityType>GIOM789_2</activityType>
    </OrderList>
    <Response>
        <cdm:code>String</cdm:code>
        <cdm:description>String</cdm:description>
    </Response>
</InquirePendingEnterpriseOrderResponse>
';

   V_actType_list          VARCHAR2 (1000) := NULL;
BEGIN
   v_mds_xml_parser := DBMS_XMLPARSER.newparser;
   xmlparser.parseclob (v_mds_xml_parser, i_reqxml);
   v_mds_xml_doc := DBMS_XMLPARSER.getdocument (v_mds_xml_parser);
   /* Application Details */
   v_inq_entrp_ordr_node :=
      xmldom.item (
         xmldom.getelementsbytagname (
            v_mds_xml_doc,
            'InquirePendingEnterpriseOrderResponse'),
         0);
   /*AccountOrganizationIdentifierContent */
   v_OrderList :=
      xmldom.getchildrenbytagname (
         xmldom.makeelement (v_inq_entrp_ordr_node),
         'OrderList');

   FOR l_index IN 0 .. (xmldom.getlength (v_OrderList)) - 1
   LOOP
      v_orderlist_node := xmldom.item (v_OrderList, l_index);
      v_activityType_list :=
         xmldom.getchildrenbytagname (xmldom.makeelement (v_orderlist_node),
                                      'activityType');
      DBMS_OUTPUT.put_line ('OrderList=' || l_index);

      FOR l_index IN 0 .. (xmldom.getlength (v_activityType_list)) - 1
      LOOP
         v_actvity_node := xmldom.item (v_activityType_list, l_index);
         text_node := DBMS_XMLDOM.getfirstchild (v_actvity_node);
         v_activityType := DBMS_XMLDOM.getnodevalue (text_node);
         V_actType_list := V_actType_list || '''' || v_activityType || ''',';
         DBMS_OUTPUT.put_line ('v_activityType=' || v_activityType);
      END LOOP;
   END LOOP;
END;
/

OrderList=0
v_activityType=GIOM234_1
v_activityType=GIOM234_2
OrderList=1
v_activityType=GIOM789_1
v_activityType=GIOM789_2

PL/SQL procedure successfully completed.

SQL>

Consuming SOAP Web Services using Oracle UTL_HTTP

SET DEFINE OFF;
SET SERVEROUTPUT ON;

DECLARE
   /* $Header: $ */
   -- ------------------------------------------------------------------------------------------------
   -- $URL: $
   -- $LastChangedRevision: $
   -- $LastChangedDate: $
   -- $LastChangedBy: $
   -- $Id: $
   -- ------------------------------------------------------------------------------------------------
   -- Date        Name           Comment
   -- ----------- -------------- ---------------------------------------------------------------------
   -- 03-Aug-2018 Rajasekhar M   - To test SOAP WebService connection
   ---------------------------------------------------------------------------------------------------


   i_endpoint_url   VARCHAR2 (100)   := 'https://wsctt.pearsonvue.com/cxfws2/services/Ping?wsdl';
 
   --i_endpoint_url   VARCHAR2 (100):= 'https://wsctt.pearsonvue.com/cxfws2/services/Ping';

   i_req_xml        XMLTYPE
      := xmltype (
            '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:sch="http://ws.pearsonvue.com/ping/schema">
    <soap:Header/>
<soap:Body>
       <sch:pingServiceRequest/>
    </soap:Body>
 </soap:Envelope>');


   o_status_code    NUMBER := NULL;
   o_status_desc    VARCHAR2 (500) := NULL;
   o_resp_xml       CLOB := NULL;

   PROCEDURE p_print (pnt_msg VARCHAR2)
   AS
   BEGIN
      IF 1 = 1
      THEN
         DBMS_OUTPUT.put_line (SYSTIMESTAMP || ': ' || pnt_msg);
         RETURN;
      END IF;
   END p_print;

   FUNCTION cloblengthb (p_clob IN CLOB)
      RETURN NUMBER
   AS
      v_temp_blob     BLOB;
      v_dest_offset   NUMBER := 1;
      v_src_offset    NUMBER := 1;
      v_amount        INTEGER := DBMS_LOB.lobmaxsize;
      v_blob_csid     NUMBER := DBMS_LOB.default_csid;
      v_lang_ctx      INTEGER := DBMS_LOB.default_lang_ctx;
      v_warning       INTEGER;
      v_total_size    NUMBER := 0;        -- Return total clob length in bytes
   BEGIN
      IF p_clob IS NOT NULL
      THEN
         DBMS_LOB.CREATETEMPORARY (lob_loc => v_temp_blob, cache => TRUE);
         DBMS_LOB.CONVERTTOBLOB (v_temp_blob,
                                 p_clob,
                                 v_amount,
                                 v_dest_offset,
                                 v_src_offset,
                                 v_blob_csid,
                                 v_lang_ctx,
                                 v_warning);
         v_total_size := DBMS_LOB.GETLENGTH (v_temp_blob);
         DBMS_LOB.FREETEMPORARY (v_temp_blob);
      ELSE
         v_total_size := NULL;
      END IF;

      RETURN v_total_size;
   END cloblengthb;

   PROCEDURE p_invoke_wsdl_over_https (i_endpoint_url   IN     VARCHAR2,
                                       i_req_xml        IN     CLOB,
                                       o_status_code       OUT NUMBER,
                                       o_status_desc       OUT VARCHAR2,
                                       o_resp_xml          OUT CLOB)
   AS
      v_wsdl_url             VARCHAR2 (1000) := NULL;
      v_soap_request         CLOB;
      v_soap_response        CLOB;
      l_http_request         UTL_HTTP.req;
      l_http_response        UTL_HTTP.resp;
      v_response_buffer      VARCHAR2 (32767) := NULL;
      v_status_code          NUMBER := 0;     -- Default value is 0 - Success;
      v_status_desc          VARCHAR2 (500) := 'Success'; -- Default value is Success';
      v_process_start_time   NUMBER;
      v_process_end_time     NUMBER;
      v_process_time_taken   NUMBER;
      v_host                 VARCHAR2 (100) := NULL;
      g_env                  VARCHAR2 (100)
         := UPPER (SYS_CONTEXT ('USERENV', 'DB_NAME', 15));
   BEGIN
      v_process_start_time := DBMS_UTILITY.GET_TIME;
      p_print ('Starting invoking webservice transaction process.');
      v_wsdl_url := i_endpoint_url;
      v_soap_request := i_req_xml;
      p_print ('v_wsdl_url:' || v_wsdl_url);

      /*Setting set_response_error_check to true will  raise an exception when the status code indicates an error.a status code in the 4xx or 5xx ranges Response error check is turned off by default*/
      UTL_HTTP.set_response_error_check (enable => TRUE);
      -- Sets the UTL_HTTP package to raise a detailed exception
      UTL_HTTP.set_detailed_excp_support (enable => TRUE);
      -- sets the proxy to be used for requests of the HTTP or other protocols
      UTL_HTTP.set_proxy (proxy => 'http://www-proxy-adcq7.us.oracle.com:80');
      -- sets the Oracle wallet used for all HTTP requests over Secured Socket Layer (SSL), namely HTTPS
      UTL_HTTP.SET_WALLET ('file:/u01/app/oracle/dba/rdbms/ouappprd/wsctt_102019', 'ou96ffd3482652ab94f2a45c6b61ef1cgbs');
      -- From Oracle 11gR2 onward, if you are using the -auto_login option on the wallet, you don't have to specify the wallet password. You just pass NULL instead of the password.
      p_print ('Wallet has been set correctly......');
      -- sets the default time out value (60) for all future HTTP requests
      UTL_HTTP.set_transfer_timeout (60);

      -- HTTP_VERSION_1_0 Denotes HTTP version 1.0 (HTTP/1.0)
      -- HTTP_VERSION_1 Denotes HTTP version 1.1 (HTTP/1.1)
      --  Begins a new HTTP request.UTL_HTTP establishes the network connection to the target Web server or the proxy server and sends the HTTP request line.

      l_http_request :=
         UTL_HTTP.begin_request (url            => v_wsdl_url,
                                 method         => 'POST',          --GET/POST
                                 http_version   => 'HTTP/1.2');

      p_print ('Connection has opened successfully......');
      UTL_HTTP.set_body_charset (l_http_request, 'UTF-8');
      -- Sets an HTTP request header. The request header is sent to the Web server as soon as it is set.
      UTL_HTTP.set_header (l_http_request,
                           'User-Agent',
                           'Mozilla/4.0 (compatible)');
      UTL_HTTP.set_header (l_http_request, 'Connection', 'Keep-Alive'); -- Keep-Alive/close
      -- SOAP 1.1
      /*UTL_HTTP.set_header (l_http_request,
                           'Content-Type',
                           'text/xml; charset=utf-8');*/
      -- SOAP 1.2
      UTL_HTTP.set_header (l_http_request,
                           'Content-Type',
                           'application/soap+xml; charset=utf-8');

      UTL_HTTP.set_header (l_http_request,
                           'content-length',
                           cloblengthb (v_soap_request));
      -- Sets HTTP authentication information in the HTTP request header. The Web server needs this information to authorize the request.
      --UTL_HTTP.set_authentication (req, v_organisation_token, '.');
      -- Writing soap request text data in the HTTP body.
      UTL_HTTP.write_text (l_http_request, v_soap_request);
      p_print ('v_soap_request: ' || v_soap_request);

      l_http_response := UTL_HTTP.get_response (l_http_request);
      p_print (
         'Response> status_code: "' || l_http_response.status_code || '"');
      p_print (
         'Response> reason_phrase: "' || l_http_response.reason_phrase || '"');
      p_print (
         'Response> http_version: "' || l_http_response.http_version || '"');

      DBMS_LOB.createtemporary (v_soap_response, TRUE);

      BEGIN
         LOOP
            p_print ('Read data.................');
            --  reads the HTTP response body in text form and returns the output in the caller-supplied buffer
            UTL_HTTP.read_text (l_http_response, v_response_buffer, 32767);
            p_print ('v_response_buffer ' || v_response_buffer);
            DBMS_LOB.writeappend (v_soap_response,
                                  -- LENGTHB (v_response_buffer),
                                  LENGTH (v_response_buffer),
                                  v_response_buffer);
         END LOOP;
      EXCEPTION
         -- The end_of_body exception will be raised if the end of the HTTP response body is reached
         WHEN UTL_HTTP.end_of_body
         THEN
            p_print ('end_of_body error -->' || SQLCODE || ':' || SQLERRM);
            -- ends the HTTP response. It completes the HTTP request and response
            UTL_HTTP.end_response (l_http_response);
         WHEN OTHERS
         THEN
            p_print (
               'Response process error -->' || SQLCODE || ':' || SQLERRM);
      END;

      p_print ('v_soap_response ' || v_soap_response);
      o_resp_xml := v_soap_response;
      o_status_code := v_status_code;
      o_status_desc := v_status_desc;
      v_process_end_time := DBMS_UTILITY.GET_TIME;
      v_process_time_taken :=
           MOD (
              ( (v_process_end_time - v_process_start_time) + POWER (2, 32)),
              POWER (2, 32))
         / 100;
      p_print (
            'invoking webservice transaction process took '
         || v_process_time_taken
         || ' Seconds');
      p_print ('Ending invoking webservice transaction process.');
   EXCEPTION
      WHEN UTL_HTTP.end_of_body
      THEN
         UTL_HTTP.end_response (l_http_response);
      WHEN UTL_HTTP.too_many_requests
      THEN
         UTL_HTTP.end_response (l_http_response);
         v_status_code := 1;
         v_status_desc := ('Failed, ' || 'Oracle exception - ' || SQLERRM);
         o_status_code := v_status_code;
         o_status_desc := v_status_desc;
      WHEN OTHERS
      THEN
         v_status_code := 1;
         v_status_desc := ('Failed, ' || 'Oracle exception - ' || SQLERRM);
         o_status_code := v_status_code;
         o_status_desc := v_status_desc;
         p_print ('Oracle Error code -->' || SQLCODE);
         p_print ('Oracle Error Message -->' || SQLERRM);
   END p_invoke_wsdl_over_https;
BEGIN
   p_invoke_wsdl_over_https (i_endpoint_url,
                             i_req_xml.getclobval (),
                             o_status_code,
                             o_status_desc,
                             o_resp_xml);
   DBMS_OUTPUT.put_line ('o_status_code=' || o_status_code);
   DBMS_OUTPUT.put_line ('o_status_desc=' || o_status_desc);
   DBMS_OUTPUT.put_line ('o_resp_xml=' || o_resp_xml);
END;
/

Response:

19-NOV-19 03.29.34.666236000 AM -07:00: Starting invoking webservice transaction process.

19-NOV-19 03.29.34.666360000 AM -07:00:

v_wsdl_url:https://wsctt.pearsonvue.com/cxfws2/services/Ping?wsdl

19-NOV-19 03.29.34.666467000 AM -07:00: Wallet has been set correctly......

19-NOV-19 03.29.34.973263000 AM -07:00: Connection has opened successfully......

19-NOV-19 03.29.34.974082000 AM -07:00: v_soap_request: <soap:Envelope

xmlns:soap="http://www.w3.org/2003/05/soap-envelope"

xmlns:sch="http://ws.pearsonvue.com/ping/schema">

    <soap:Header/>

    <soap:Body>

<sch:pingServiceRequest/>

    </soap:Body>

 </soap:Envelope>

19-NOV-19 03.29.35.281206000 AM -07:00: Response> status_code: "200"

19-NOV-19 03.29.35.281250000 AM -07:00: Response> reason_phrase: "200"

19-NOV-19 03.29.35.281264000 AM -07:00: Response> http_version: "HTTP/1.1"

19-NOV-19 03.29.35.281304000 AM -07:00: Read data.................

19-NOV-19 03.29.35.281359000 AM -07:00: v_response_buffer <soap:Envelope

xmlns:soap="http://www.w3.org/2003/05/soap-envelope"

xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><ping:pingResponse

xmlns:ping="http://ws.pearsonvue.com/ping/schema">

    <result

status="success"/>

</ping:pingResponse></soap:Body></soap:Envelope>

19-NOV-19 03.29.35.281543000 AM -07:00: Read data.................

19-NOV-19 03.29.35.281666000 AM -07:00: end_of_body error -->-29266:ORA-29266: end-of-body reached

19-NOV-19 03.29.35.282175000 AM -07:00: v_soap_response <soap:Envelope

xmlns:soap="http://www.w3.org/2003/05/soap-envelope"

xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><ping:pingResponse

xmlns:ping="http://ws.pearsonvue.com/ping/schema">

    <result

status="success"/>

</ping:pingResponse></soap:Body></soap:Envelope>

19-NOV-19 03.29.35.282235000 AM -07:00: invoking webservice transaction process took .62 Seconds

19-NOV-19 03.29.35.282250000 AM -07:00: Ending invoking webservice transaction process.

o_status_code=0

o_status_desc=Success

o_resp_xml=<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope"

xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><ping:pingResponse

xmlns:ping="http://ws.pearsonvue.com/ping/schema">

    <result

status="success"/>

</ping:pingResponse></soap:Body></soap:Envelope>

PL/SQL procedure successfully completed.

SQL>

Create a new Authentication Scheme in Oracle APEX

Step 1:

Shared Components --> Security --> Authentication Schemes --> Create --> Based on a pre-configured scheme from the gallery

Step 2: Enter below details

Name: 

Name: Oracle SSO
Schema Type: HTTP Heade Variable

Settings:

Action if Username is Empty : Redirect to Bulit-in URL
Verify Username : Each Request
Logout URL of SSO Server : https://login.oracle.com/sso/logout

Step 3: Click on Authentication Scheme

How to find leading and trailing spaces in a column

SQL> set lines 100
SQL> set pages 50
SQL> DROP TABLE MYTABLE;

Table dropped.

SQL> CREATE TABLE MYTABLE(MYCOL VARCHAR2(20));

Table created.

SQL> INSERT INTO MYTABLE VALUES('nospace');

1 row created.

SQL> INSERT INTO MYTABLE VALUES(' leading');

1 row created.

SQL> INSERT INTO MYTABLE VALUES('trailing ');

1 row created.

SQL> INSERT INTO MYTABLE VALUES(' both ');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM MYTABLE;

MYCOL
--------------------
nospace
 leading
trailing
 both

SQL> SELECT MYCOL FROM MYTABLE WHERE SUBSTR(MYCOL,1,1) = ' ' OR SUBSTR(MYCOL,-1,1) = ' ';

MYCOL
--------------------
 leading
trailing
 both

SQL> SELECT MYCOL FROM MYTABLE WHERE MYCOL LIKE ' %' OR MYCOL LIKE '% ';

MYCOL
--------------------
 leading
trailing
 both

SQL> SELECT MYCOL FROM MYTABLE WHERE REGEXP_LIKE(MYCOL,'(^ | $)');

MYCOL
--------------------
 leading
trailing
 both

SQL> SELECT MYCOL FROM MYTABLE WHERE REGEXP_LIKE(MYCOL,'([:space:]|[:space:]$)');

MYCOL
--------------------
nospace
 leading
trailing

SQL>