Oracle Dynamic SQL

You can create SQL statement dynamically and make the script more generic to make it work with any table.

  DECLARE
    TYPE COL_TYPE IS TABLE OF VARCHAR2(40);
    COL_NAME COL_TYPE;
    COL_DATA_TYPE COL_TYPE;
    TAB1_NAME VARCHAR2(30) :='A_USERS'; 
  BEGIN
      SQL_STMT := 'SELECT COLUMN_NAME,DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = '''|| TAB1_NAME ||'''';
       EXECUTE IMMEDIATE SQL_STMT   BULK COLLECT INTO COL_NAME ,COL_DATA_TYPE;
  END;
 /

Oracle 'EXECUTE IMMEDIATE' will help you to run dynamic sql. If   sql command return data  you can store it in a variable. Above example, SQL statement will return  columns name and data type of a table and it will be store in COL_NAME ,COL_DATA_TYPE variable. You can access value from COL_NAME array in following way.

FOR INDX IN 1 .. COL_NAME.COUNT 
   LOOP   
       DBMS_OUTPUT.PUT_LINE ('COL_NAME: ' || COL_NAME(index));
  END LOOP;

Also if you can create separate code block and pass bind variable   P_REC and IN T_REC  value while executing the statement and output will be store in MISS_MATCH variable.

 
 DECLARE
 P_REC  A_USERS%ROWTYPE;
  T_REC  A_USERS%ROWTYPE;
 BEGIN
               SQL_STMT :=  'DECLARE 
                          P_REC  '|| TAB1_NAME ||'%ROWTYPE; 
                          T_REC  '|| TAB2_NAME ||'%ROWTYPE;  
                       BEGIN    P_REC := :P_REC;  T_REC := :T_REC;   
                       SELECT DECODE(P_REC.' || COL_NAME(INDX) || ',T_REC.' || COL_NAME(INDX) || ',0,1)
INTO :MISS_MATCH FROM DUAL;                       
        
           EXECUTE IMMEDIATE SQL_STMT USING IN  P_REC , IN T_REC , OUT MISS_MATCH;
  END;
/

Replication data compare for oracle golden gate

For high availability  we need database replication, so during fail-over we can switch to standby database. Oracle golden gate provide data replication between data-centers. Replication is mainly depend on last updated time stamp. Oracle golden gate will check transaction time-stamp and replay it in another data-center. But sometime replication doesn't happen properly because of  application' defect on updating time stamp.


One or more applications can use same database and they may be locate in different time-zone. If one application is updating data using one time-zone in a active database and another application is updating data with different  time-zone in another database,  such case replication between these two database  will make database in-consistent state.


To find out data in-consistency we can compare one site data table with another site same table. For example  we have table name 'customer' in database and data is replicating for this table on two active database A and B . If we want to compare data for this table, we can export customer table from A and B into a new database schema, so data comparison job will not impact the running database.

If the table contain only some configuration data, number of records might be less. But customer table can contain millions of record, therefore comparing data become very time consuming.
We can overcome this problem using oracle 'DBMS_PARALLEL_EXECUTE' package. This will allow you to logically split table using table rowid and create job for each partition and run them parallel. You can partition table using different columns as well.

Below snapshot is to create job using oracle 'DBMS_PARALLEL_EXECUTE' package.

 
 DECLARE 
  TASK_NAME VARCHAR2(40) :='A_B_DATA_COMPARE';
  UPDATE_STATEMENT CONSTANT VARCHAR2 (200)
      :=  'BEGIN DATA_COMP(:start_id, :end_id); END;';

 BEGIN

         DBMS_PARALLEL_EXECUTE.CREATE_TASK (TASK_NAME);       
         DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID (TASK_NAME => TASK_NAME, TABLE_OWNER => USER , TABLE_NAME => TAB1_NAME , BY_ROW => TRUE , CHUNK_SIZE => 800000  );
         DBMS_PARALLEL_EXECUTE.RUN_TASK (TASK_NAME => TASK_NAME , SQL_STMT => C_UPDATE_STATEMENT  , LANGUAGE_FLAG => DBMS_SQL.NATIVE  , PARALLEL_LEVEL =>64  );
       EXCEPTION
              WHEN OTHERS  THEN 
                  DBMS_OUTPUT.PUT_LINE('FAIL TO RUN  JOBS, ERROR CODE:' ||SQLCODE);
                  RETURN;
 
    

         
 END;

You can pass number concurrent jobs, partition size and more importantly which procedure or function it will call with start rowid and end rowid.  In the above example DBMS_PARALLEL_EXECUTE will partition customer table by rowid and each partition will contain 800000 record. Then it will call DATA_COMP procedure with a partition start rowid and end rowid. You can select record using rowid and loop through them using oracle cursor.

 
      DECLARE 
   TASK_NAME VARCHAR2(40) :='A_B_DATA_COMPARE';
  UPDATE_STATEMENT CONSTANT VARCHAR2 (200)
      :=  'BEGIN DATA_COMP(:start_id, :end_id); END;';
     BEGIN
         DBMS_PARALLEL_EXECUTE.CREATE_TASK (TASK_NAME);       
         DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID (TASK_NAME => TASK_NAME, TABLE_OWNER => USER , TABLE_NAME => TAB1_NAME , BY_ROW => TRUE , CHUNK_SIZE => 800000  );
         DBMS_PARALLEL_EXECUTE.RUN_TASK (TASK_NAME => TASK_NAME , SQL_STMT => C_UPDATE_STATEMENT  , LANGUAGE_FLAG => DBMS_SQL.NATIVE  , PARALLEL_LEVEL =>64  );
       EXCEPTION
              WHEN OTHERS  THEN 
                  DBMS_OUTPUT.PUT_LINE('FAIL TO RUN  JOBS, ERROR CODE:' ||SQLCODE);
                  RETURN;
       END;
    
    

This way you compare data more faster way, since it will parallel.   Inside the cursor loop you can pull record from another table using primary key and compare the record by each column of A and B database customer table.

You can can get complete oracle  PL/SQL example in Github. I make the script dynamic, so only you need to pass compare  two tables  and report  table name.

Load balancer using Apache mod_proxy

First install Apache server. You can download it from http://httpd.apache.org/. For  this example  I have used  httpd-2.2.25-win32-x86-openssl-0.9.8. You can get it from Apache archive.


After  Apache installation complete, you might need to change the port 80 to some other port e.g 8000  if 80 port is block. You can change the port in httpd.conf file which located under   conf  folder.

Listen 8000

Bellow is an example how we can use mod_proxy_balancer to provide load balancing for three back-end server. You need to add following snippet at the bottom of the htttpd.conf. Then restart the server.

 
<Proxy balancer://mycluster>
BalancerMember http://localhost:9001
BalancerMember http://10.83.57.247:7001
BalancerMember http://wabothdk0544042:7001
</Proxy>
ProxyPass / balancer://mycluster/
ProxyPassReverse / balancer://mycluster/ 

Now if you open the URL http://localhost:8000 in browser, request will be forward to one of the back-end server. You can test it by looking server logs.

Create Proxy Service for secure web service call using Mule ESB

Mule is open source Enterprise Service Bus. You can create proxy service of a web-service easily with mule. You can transform request and response payload using Mule XSLT transformation.

Following figure is showing diagram of Mule proxy service and XSLT transformation.

Below is configuration XML.

To set password following code snippet is used for password callback

package lr.mule.security;

import java.io.IOException;

import javax.security.auth.callback.Callback;
import javax.security.auth.callback.CallbackHandler;
import javax.security.auth.callback.UnsupportedCallbackException;

import org.apache.ws.security.WSPasswordCallback;

public class PasswordCallback implements CallbackHandler
{
    public void handle(Callback[] callbacks) throws IOException, UnsupportedCallbackException
    {
        WSPasswordCallback pc = (WSPasswordCallback) callbacks[0];

       
         
            pc.setPassword("password");
       
       
    }
}

How to resolve Java SSL certificate error PKIX path building failed

If you found following error while connecting a secure server

"org.apache.axis2.AxisFault: Connection has been shutdown: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target"

follow the below step to resolve the issue.

Step 1. Go to your Mozilla Firefox and browse the server URL and follow the highlighted steps.

Step 2. Download the certificate by following highlighted steps and save it to location direction with file extension ".crt", e.g: example.crt.

       

Step 3. Now open command prompt with "run as administrator" and run the following command

keytool -import -trustcacerts -file F:\qatoes002.unix.gsm1900.org.crt -alias CA_ALIAS -keystore "%JAVA_HOME%/jre/lib/security/cacerts"

it might ask password if needed then ask server administrator.

Certificate will be store in JDK home. If you are using eclipse IDE for development, make sure you are using same JDK home.

Using Unix Command:


echo -n | openssl s_client -connect HOST:PORT | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > /tmp/$SERVERNAME.cert

echo -n | openssl s_client -connect localhost:5116 | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > /tmp/$SERVERNAME.cert


 /opt/msdp/local/jdk1.8.0_51/bin/keytool -importcert -file /tmp/$SERVERNAME.cert  -keystore keystore1.jks -alias "Alias1"

Reference:
https://www.digitalocean.com/community/tutorials/java-keytool-essentials-working-with-java-keystores

Selenium webdriver Junit test example

Selenium is one of the best tool for automating web application testing. If number of pages of web application increase gradually, it is really hard for application tester to test every page after new deployment.

Selenium provide web driver for almost browser including firefox, IE, Google Chrome etc. Even you can integrate selenium web driver with Junit, TestNG or plan Java, .Net code.

Selenium provide two type of web drivers. Browser base driver like FirefoxDriver will open the Firefox browser and perform the test. In this case, Selenium will fill up all fields and click on necessary places, just like an automated robot. But if you want to write test cases that does't require browser then you can use  HtmlUnitDriver. It will create instance of in-memory browser. But browser base driver is important when you want to test your application whether its compatible with all modern browser like Google chrome, Firefox and IE.

You can download Selenium library from  http://docs.seleniumhq.org/download/.

However, you can add it in your project as maven dependency.

   
  
   org.seleniumhq.selenium
   selenium-java
   2.39.0
   
    
     org.seleniumhq.selenium
     selenium-iphone-driver
    
    
     xml-apis
     xml-apis
    
   
  

Following snippet  is showing initialization of Firefox web driver and HtmlUnitDriver.

Create a new instance of the html unit driver
private WebDriver driver= new HtmlUnitDriver();

 // Create a new instance of the Firefox driver
private WebDriver driver= new FirefoxDriver();


Here is an example showing how to perform login before test any secure page. In Junit @BeforeClass annotation method will be called before run any test case.

@BeforeClass
 public static void setUpBeforeClass() throws Exception {

  entity = new Users("moin", "123", "abc@gmail.com", "xyz",
    "");
         // Create a new instance of the html unit driver
  driver = new HtmlUnitDriver();
              
                // And now use this to visit your login URL. e.g 
  driver.get(serverBaseUrl + "/login");
                // set the user name in user id text field
  driver.findElement(By.name("j_username"))
    .sendKeys(entity.getUserName());
                 // set the password in user password text field
  driver.findElement(By.name("j_password"))
    .sendKeys(entity.getPassword());

  driver.findElement(By.className("pull-right")).click();
  System.out.println(driver.getPageSource());
  if (driver.findElement(By.cssSelector(".jumbotron")).getText()
    .contains("Welcome to ScrapperX")) {

   System.out.println("Login Success");
  } else {
   System.out.println("Login fail");
   throw new Exception("Login Require");

  }

  Thread.sleep(1000);
 }

After login you can test any secure page in the same session. Below snippet is showing how to test "update user profile page"

 
       @Test
 public void testUpdateProfile() {
  driver.get(serverBaseUrl + "/users/editprofile");
                // clear the exist first name or else sendKeys will append new text
  driver.findElement(By.name("firstName")).clear();
  driver.findElement(By.name("firstName"))
    .sendKeys(entity.getFirstName());
  driver.findElement(By.name("lastName")).clear();
  driver.findElement(By.name("lastName")).sendKeys(entity.getLastName());
  driver.findElement(By.name("password")).clear();
  driver.findElement(By.name("password")).sendKeys(entity.getPassword());
  driver.findElement(By.name("c_password")).clear();
  driver.findElement(By.name("c_password"))
    .sendKeys(entity.getPassword());
    // in several way you can select a checkbox
                 /*
   * List checkbox =
   * driver.findElements(By.name("paymentActive")); ((WebElement)
   * checkbox.get(0)).click();
   */
                // select paymentActive checkbox
  WebElement checkbox = driver.findElement(By.name("paymentActive"));
             
  if (checkbox.isSelected() == false) {
   checkbox.click();
  }

  driver.findElement(By.className("btn")).click();
  System.out.println(driver.findElement(By.cssSelector(".alert"))
    .getText());
  assertTrue("Update user profile fail",
    driver.findElement(By.cssSelector(".alert")).getText()
      .contains("Update Complete"));
 }

You can access any field or html element by css class name, id and field name. Following example is showing how to select a drop-down option.

   Select cardTypeDropDown = new Select(driver.findElement(By
    .name("cardType")));
   cardTypeDropDown.selectByValue("visa");

Here is an working example of testing Google search.

package com.scrapperx.web.controller;

import org.junit.Test;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;

public class GoogleSearchTest {

 @Test
 public void testGoogleSearch() throws InterruptedException {
  // Create a new instance of the html unit driver
  // Notice that the remainder of the code relies on the interface,
  // not the implementation.
  System.out.println("http://www.google.com");
  WebDriver driver = new FirefoxDriver();

  // And now use this to visit Google
  driver.get("http://www.google.com");

  // Find the text input element by its name
  WebElement element = driver.findElement(By.name("q"));

  // Enter something to search for
  element.sendKeys("tometo");

  // Now submit the form. WebDriver will find the form for us from the
  // element
  element.submit();

  // Check the title of the page
  System.out.println("Page title is: " + driver.getTitle());
        Thread.sleep(10000);
  driver.quit();

 }
}

Bootstrap autocomplete using Typeahead JS

Typeahead provide very good solution for auto complete suggestion. Here is an example showing auto suggestion based on user query. When user type a keyword, a json request will be send to server side and response will be appeared right bottom of search field.