Calling BPEL/ESB Webservice from PL/SQL

DECLARE
--
  v_bookmark          NUMBER:=0;
  soap_request        CLOB;
  soap_respond        CLOB;
  resp                XMLTYPE;
  web_service_failure EXCEPTION;
  v_request_url       VARCHAR2(1000) := 'http://12.12.12.12:7777/orabpel/operations/WarehousePreadvice/1.0';
  -- operation name in webservice
  v_operation_name    VARCHAR2(100):='createWhPreAdvice';
  -- namespace name
  v_xmlns_url         VARCHAR2(1000) := 'xmlns:ns1="http://xmlns.hdnl.com/EnterpriseObject/Core/WarehousePreadviceRequest%22';
  v_error_code        VARCHAR2(1000);
  v_error_description VARCHAR2(1000);
  v_error_status      VARCHAR2(1000);
  soap_request_len    NUMBER:=0;
  http_req            UTL_HTTP.REQ;
  http_resp           UTL_HTTP.RESP;
  resp                XMLTYPE;
  i                   INTEGER;
  p_errorcode         VARCHAR2(1000);
  p_errordescription  VARCHAR2(1000);
  p_errorstatus       VARCHAR2(1000);
  v_index             NUMBER:=1;
--
--
BEGIN
--
  DBMS_OUTPUT.PUT_LINE('Program Begins '||TO_CHAR(SYSDATE,'dd-mon-yyyy hh24:mi:ss'));
--
    
      soap_request:='<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsdl="http://WarehousePreadvice/wsdl" xmlns:ns1="http://xmlns.hdnl.com/EnterpriseObject/Core/WarehousePreadvice" xmlns:ns2="http://xmlns.hdnl.com/EnterpriseObject/Core/WarehousePreadviceRequest">
                      <soapenv:Header/>
                       <soapenv:Body>
                         <ns:createWhPreAdvice xmlns:ns="http://WarehousePreadvice/wsdl">
                          <payload xmlns:ns1="http://xmlns.hdnl.com/EnterpriseObject/Core/WarehousePreadvice">
                           <ns1:WhPreAdviceRequest xmlns:ns2="http://xmlns.hdnl.com/EnterpriseObject/Core/WarehousePreadviceRequest">
                            <ns2:PreadviceHeader>
                                 <ns2:ClientId>'||idx.clientId||'</ns2:ClientId>
                                 <ns2:OwnerId>'||idx.owner||'</ns2:OwnerId>
                                 <ns2:PreAdviceId>'||idx.PreAdviceId||'</ns2:PreAdviceId>
                                 <ns2:ClientCustomerReference>'||idx.ClientCustomerReference||'</ns2:ClientCustomerReference>
                                 <ns2:SiteId/>
                                 <ns2:SupplierId>'||idx.SupplierId||'</ns2:SupplierId>
                                 <ns2:CollectionReqd>'||idx.CollectionFlag||'</ns2:CollectionReqd>
                                 <ns2:ReturnFlag>'||idx.ReturnFlag||'</ns2:ReturnFlag>
                                 <ns2:Name>'||idx.Customer_Name||'</ns2:Name>
                                 <ns2:Address1>'||idx.AddressLine1||'</ns2:Address1>
                                 <ns2:Address2>'||idx.AddressLine2||'</ns2:Address2>
                                 <ns2:Town>'||idx.Town||'</ns2:Town>
                                 <ns2:County>'||idx.County||'</ns2:County>
                                 <ns2:Postcode>'||idx.PostCode||'</ns2:Postcode>
                                 <ns2:Country>'||idx.CountryCode||'</ns2:Country>
                                 <ns2:ContactPhone>'||idx.ContactPhone||'</ns2:ContactPhone>
                                 <ns2:Status/>
                                 <ns2:ActionType/>
                                 <ns2:Consignment>'||idx.Consignment||'</ns2:Consignment>
                                 <ns2:PreadviceLines>
                                     <ns2:PreadviceLine>
                                         <ns2:ClientId>'||idx.Line_clientId||'</ns2:ClientId>
                                         <ns2:PreAdviceId>'||idx.Line_PreAdviceId||'</ns2:PreAdviceId>
                                         <ns2:QtyDue>'||idx.QtyDue||'</ns2:QtyDue>
                                         <ns2:ConfigId/>
                                         <ns2:LineId>'||idx.LineID||'</ns2:LineId>
                                         <ns2:TagId>'||idx.TagId||'</ns2:TagId>
                                         <ns2:SkuId>'||idx.SkuId||'</ns2:SkuId>
                                         <ns2:UserDefDate1/>
                                         <ns2:UserDefType1>'||idx.UserDefType1||'</ns2:UserDefType1>
                                         <ns2:UserDefType2/>
                                         <ns2:UserDefType3>'||idx.UserDefType3||'</ns2:UserDefType3>
                                         <ns2:UserDefType4/>
                                         <ns2:UserDefType5/>
                                         <ns2:UserDefType6/>
                                         <ns2:ActionType/>
                                         <ns2:ConditionId/>
                                     </ns2:PreadviceLine>
                                 </ns2:PreadviceLines>
                            </ns2:PreadviceHeader>
                           </ns1:WhPreAdviceRequest>
                          </payload>
                         </ns:createWhPreAdvice>
                       </soapenv:Body>
                      </soapenv:Envelope>';
    --
    -- Set up transfer protocols
    --
      DBMS_OUTPUT.PUT_LINE('Soap Request Populated... ');
      DBMS_OUTPUT.PUT_LINE('Setting Transfer Protocols... ');
    --
      UTL_HTTP.SET_TRANSFER_TIMEOUT(500);
      http_req := UTL_HTTP.BEGIN_REQUEST(v_request_url, 'POST', 'HTTP/1.1');
      UTL_HTTP.SET_HEADER(http_req, 'Content-Type', 'text/xml');     -- since we are dealing with plain text in XML documents
      UTL_HTTP.SET_HEADER(http_req, 'Content-Length', LENGTH(soap_request));
      UTL_HTTP.SET_HEADER(http_req, 'SOAPAction', v_operation_name); -- required to specify this is a SOAP communication
    -- Check length of package - if > 32767 need to chop up
      soap_request_len:=LENGTH(soap_request);
    --
      DBMS_OUTPUT.PUT_LINE('Setting Request Length is '||soap_request_len);
    --
      IF LENGTH(soap_request) <='32767'
      THEN
         UTL_HTTP.WRITE_TEXT(http_req, soap_request);
      ELSE
        --Need to loop around clob
          WHILE v_index <= soap_request_len
          LOOP
              UTL_HTTP.WRITE_TEXT(http_req, SUBSTR(soap_request, v_index, 32000));
              v_index := v_index + 32000;
          END LOOP;
        --
      END IF;
    --
      DBMS_OUTPUT.PUT_LINE('Request= '||soap_request);
      http_resp:= UTL_HTTP.GET_RESPONSE(http_req);
      DBMS_OUTPUT.PUT_LINE('HTTP Response status code  : ' || http_resp.status_code);
      DBMS_OUTPUT.PUT_LINE('HTTP Response reason phrase: ' || http_resp.reason_phrase);
      DBMS_OUTPUT.PUT_LINE('HTTP Response http version : ' || http_resp.http_version);
      UTL_HTTP.READ_LINE(http_resp, soap_respond);
      UTL_HTTP.END_RESPONSE(http_resp);
      DBMS_OUTPUT.PUT_LINE('Response  '||soap_respond);
      DBMS_OUTPUT.PUT_LINE('Response Received');
      DBMS_OUTPUT.PUT_LINE('--------------------------');
      DBMS_OUTPUT.PUT_LINE ( 'Status code: ' || http_resp.status_code );
    --
--
EXCEPTION
--
  WHEN web_service_failure THEN
  DBMS_OUTPUT.PUT_LINE('Web Service Failure ');
--
  WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('Exception Block No DATA Found ');
--
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Exception Block others '||SQLERRM);
--
END;
/

Google Language translation for large Text

Create a below function and Apply the function to the text or fields which need to be translated to the destination language

googleTranslateTool.class.php
<?php
class Google_Translate_API {
 function translate($text, $from = '', $to = 'en') {
  $url = 'http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&q='.rawurlencode($text).'&langpair='.rawurlencode($from.'|'.$to);
  $response = file_get_contents(
   $url,
   null,
   stream_context_create(
    array(
     'http'=>array(
     'method'=>"GET",
     'header'=>"Referer: http://%22.$_server['http_host'].%22//r/n"
     )
    )
   )
  );
  if (preg_match("/{\"translatedText\":\"([^\"]+)\"/i", $response, $matches)) {
   return self::_unescapeUTF8EscapeSeq($matches[1]);
  }
  return false;
 }

 function _unescapeUTF8EscapeSeq($str) {
  return preg_replace_callback("/\\\u([0-9a-f]{4})/i", create_function('$matches', 'return html_entity_decode(\'&#x\'.$matches[1].\';\', ENT_NOQUOTES, \'UTF-8\');'), $str);
 }
}
?>


Create the page which need to be translated

<?php
require_once('googleTranslateTool.class.php');
$text = 'Hello I am testing translation Hello I am testing translation Hello I am testing translation Hello I am testing translation Hello I am testing translation Hello I am testing translation Hello I am testing translation Hello I am testing translation Hello I am testing translation';
$strlen = strlen($text);
if($strlen<=500){
  $ttext = $text;
}else{
 
  $mi = ceil($strlen/1500);
  for($i=0; $i<$mi; $i++)
  {
    $ttext = substr($text,1500*i,1499);
    $trans_text = Google_Translate_API::translate($ttext, '', 'en');
    if ($trans_text !== false) {
   $trn_text=$trn_text.$trans_text;
   }else{
     $trn_text=$trn_text.$text;
   }
  }
}
echo $trn_text;
?>

Multiple Address Marker on Google Map Fetching address from DB

The objective of this is to mark the address on the google map which can be fetched from database and connect the location using google maps polyline API

Before creating the procedure calculate the coordinates for the center location around which you need to show the markers

-- The coordinate in this line need to be updated as per your geography
   htp.print('map.setCenter(new GLatLng(51.600423,-0.338442), 7);'); 

Step 1 - Create a procedure for setting the marker

 create or replace procedure xxtest_show_markers(p_points in varchar2) is
 cursor c_klt is
   select regexp_replace (suppliername||', '||addressline1||', '||addressline2||', '||addressline3||', '||addressline4||', '||addressline5||', '||county||', '||postcode||', '||country,'(, )\1{1,}', '\1')  geoloc from supplier where rownum < 25 order by id; 
 l_t number(3) := 0; 
 --l_address varchar2(1000);
 l_geoloc varchar2(1000);
l_address varchar2(4000);
l_api_key varchar2(4000) := 'ABQIAAAAtPAfpd_OogwbQkJcM603nBTPeIpK1BLsYPp4-g4MsBDuj8I5YhT9s6k5GHvv156Mb6qt2YiKldX5LQ';
l_url varchar2(32000);
l_response varchar2(3200);
l_points varchar2(32000);
 begin
   htp.print('<script src="http://maps.google.com/maps?file=api&amp;v=2&amp;sensor=true&amp;key=ABQIAAAAtPAfpd_OogwbQkJcM603nBTPeIpK1BLsYPp4-g4MsBDuj8I5YhT9s6k5GHvv156Mb6qt2YiKldX5LQ" dirty="false"></script><script>'); 
   htp.print('function initialize() {');
  -- htp.print('alert(window.location.host);');
   htp.print('var map = null;'); 
   htp.print('var geocoder = null;'); 
   htp.print('if (GBrowserIsCompatible()) {'); 
   htp.print('map = new GMap2(document.getElementById("map_canvas"));'); 
   htp.print('map.setCenter(new GLatLng(51.600423,-0.338442), 7);'); 
   htp.print('map.addControl(new GLargeMapControl());'); 
   htp.print('map.addControl(new GMapTypeControl());'); 
   htp.print('geocoder = new GClientGeocoder();'); 
   for r_klt in c_klt 
   loop 
     htp.print('geocoder.getLatLng('); 
     htp.print(''''||r_klt.geoloc||''''||','); 
     htp.print('function(point) {'); 
     htp.print('var baseIcon = new GIcon(G_DEFAULT_ICON);'); 
     htp.print('baseIcon.shadow = "http://www.google.com/mapfiles/shadow50.png%22;'); 
   --htp.print('baseIcon.shadow = "/i/pdf.png";'); 
     htp.print('baseIcon.iconSize = new GSize(20, 34);'); 
     htp.print('baseIcon.shadowSize = new GSize(37, 34);'); 
     htp.print('baseIcon.iconAnchor = new GPoint(9, 34);'); 
     htp.print('baseIcon.infoWindowAnchor = new GPoint(9, 2);'); 
     htp.print('var letteredIcon = new GIcon(baseIcon);'); 
     l_address := r_klt.geoloc;
     htp.print('letteredIcon.image = "http://www.google.com/mapfiles/marker'%7C%7Cchr(65+l_t)%7C%7C'.png%22;');   
     htp.print('markerOptions = { icon:letteredIcon, title:'''||l_address||'''};'); 
     htp.print('var marker = new GMarker(point,markerOptions);'); 
     htp.print('var html = "<h1>'||r_klt.geoloc||'</h1>";');
     htp.print('GEvent.addListener(marker, "mouseover", function() {  marker.openInfoWindowHtml(html);  });');
     htp.print('map.addOverlay(marker);'); 
     htp.print('}'); 
     htp.print(');'); 
     l_t := l_t + 1; 
   end loop; 
   htp.print('var points = ['||p_points||'];');  
   htp.print('var polyline = new GPolyline(points, ''#ff0000'', 3, 0.5);');  
   htp.print('map.addOverlay(polyline);');
   htp.print('}'); 
   htp.print('}'); 
   htp.print('</script>'); 
   htp.print('  <body onload="initialize()" style="font-family: Arial;border: 0 none;">'); 
   htp.print('    <div id="map_canvas" style="width: 1200px; height: 600px"></div>'); 
   htp.print(' </body>'); 
 end; 

Step 2 - Create a PL/SQL Dynamic Region and copy the below code (note modify the query as per your requiremnt)

declare
l_geoloc varchar2(1000);
l_address varchar2(4000);
l_api_key varchar2(4000);
l_url varchar2(32000);
l_response varchar2(3200);
l_points varchar2(3200);
begin
for i in (select regexp_replace (suppliername||', '||addressline1||', '||addressline2||', '||addressline3||', '||addressline4||', '||addressline5||', '||county||', '||postcode||', '||country,'(, )\1{1,}', '\1')  geoloc from supplier where rownum < 25 order by id)
loop
l_address := replace(i.geoloc,' ','+');
l_url := 'http://maps.google.com/maps/geo?q='||l_address||'&'||'output=csv'||'&'||'key='||l_api_key;
l_response := utl_http.request(l_url, APEX_APPLICATION.G_PROXY_SERVER);
--htp.p(l_response);
--htp.p('</br></br>');
l_points := l_points||'new GLatLng('||substr(l_response,instr(l_response,',',1,2)+1)||'), ';
end loop;
l_points := substr(l_points, 1, length(l_points)-2);
xxtest_show_markers(l_points);
end;

Step 3 - Run the page your should be able to see the markers on the google map

Oracle APEX Progress Bar Implmentation

Oracle APEX Progress Bar Implementation for long running process.

Step 1 - Copy the Progress Bar image gif to images folder
Step 2 - For Processing Butting add the below code in the button target window
             javascript:html_Submit_Progress(this); 

Step 3 - Add below code to Page HTML footer Attribute

<style> #AjaxLoading{padding:5px;font-size:18px;width:200px;text-align:center;left:50%;top:50%;position:absolute;border:2px solid #666;background-color:#FFF;}
</style>

<div id="AjaxLoading" style="display:none;">..Loading..<br /><img src="/i/carl/theme_200/processing3.gif" id="wait" /></div>

Step 4 - Add below code to Page HTML header Attribute

<script type="text/javascript">
<!--
function html_Submit_Progress(pThis){
$x_Show('AjaxLoading');
window.setTimeout('$s("AjaxLoading",$x("AjaxLoading").innerHTML)', 100);
doSubmit('APPLY_CHANGES');
}
function submit_HideAll(pThis){
$x_Hide('wwvFlowForm');
doSubmit('APPLY_CHANGES');
}
function submit_ButtonRegion(pThis){
$x_Hide('button_region');
doSubmit('APPLY_CHANGES');
}

//-->
</script>

Step 5 - doSubmit('APPLY_CHANGES') where APPLY_CHANGES is the processing button name

Apply the changes and you are ready to view the Progress bar when page is processing
 

Oracle APEX Theme Switcher Application

Oracle APEX Theme Switcher Application

Step 1 – Download the theme series folder Delta, Alpha etc.

Step 2 – Unzip the theme downloaded and copy under apex image folder

Step 3 – Login to Apex instance -> Application -> Shared Components

Step 4 – Import the theme sql file available in unzipped folder

Step 5 – Switch theme to the new theme Delta Series

Step 6 – Navigate to Shared Component and click Edit Definition

Step 7 – Create 2 substitution Variable  THEME_PATH and assign the path of the theme folder

                For ex – if you copied delta themes under apex images folder the assign /i/delta/

Step 8 – Create Variable UI_THEME and set the default theme name Example – black-tie

Step 9 – Create page zero for the application if it not available in the application

Step 10 – Create a region with no template and add a select list of theme items for switching the themes

Step 11 – Make select list as submit type on change

Step 12 – Run the Application and test the theme switcher by selecting different themes  from the select list

Contact me on raghu.j.prasad@gmail.com if you stuck with theme switcher

Reading XML Document from Oracle SQL

Query for Reading XML Document from Oracle SQL

select extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/ID') SID
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/SWDHDRID') SWDHDRID
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/HOSTNAME') HOSTNAME
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/HOSTIP') HOSTIP
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/HOSTMAC') HOSTMAC
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/RESOLVEDHOSTID') RESOLVEDHOSTID
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/MANUFACTURER') MANUFACTURER
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/PRODUCT') PRODUCT
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/EDITION') EDITION
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/SOFTWAREVERSION') SOFTWAREVERSION
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/PRODUCTOPTION') PRODUCTOPTION
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/QUANTITY') QUANTITY
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/TOOLPATH') TOOLPATH
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/TOOLFILENAME') TOOLFILENAME
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/TOOLPRODUCT') TOOLPRODUCT
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/TOOLPRIMARY') TOOLPRIMARY
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/TOOLSECONDARY') TOOLSECONDARY
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/TOOLTERTIARY') TOOLTERTIARY
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/PRODUCTID') PRODUCTID
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/MANUFACTURERID') MANUFACTURERID
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/EDITIONID') EDITIONID
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/APPLICATION') APPLICATION
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/SOURCE') SOURCE
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/PARTOFPRODUCT') PARTOFPRODUCT
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/PARTOFEDITION') PARTOFEDITION
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/PARTOFOPTION') PARTOFOPTION
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/CUSTOMISED') CUSTOMISED
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/BUSINESSAPPLICATION') BUSINESSAPPLICATION
from table (xmlsequence (extract (b_xmldoc, '//MODEL_SOFTWARE_ROW'))) t2;

Oracle 10g Regular Expression Replace Function

Replace Repeating Characters with Single Character

Ex: RAAGHHUUUUUUUUUUUUUUU  with RAGHU

SELECT regexp_replace ('RAAGHHUUUUUUUUUUUUUUU', '([A-Z])\1{1,}', '\1') from dual

Output::- RAGHU

The \1 refers to the previous character and check {1,} 1 or more times and replace with the same character. This is called Backward reference.

Remove any Special Charaters other than Alphabets and Number

Ex:- RAJ33*#$%+_HU

select regexp_replace (p_str, '([^A-Z0-9])', '') from dual

Ouput - RAJ33HU

Removes all other characters expect number and Alphabets