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