{"id":2623,"date":"2012-11-27T11:25:45","date_gmt":"2012-11-27T16:25:45","guid":{"rendered":"http:\/\/appcrawler.com\/wordpress\/?p=2623"},"modified":"2012-11-27T11:25:45","modified_gmt":"2012-11-27T16:25:45","slug":"schema-compare-command-line-tool","status":"publish","type":"post","link":"http:\/\/appcrawler.com\/wordpress\/2012\/11\/27\/schema-compare-command-line-tool\/","title":{"rendered":"Schema compare command line tool"},"content":{"rendered":"<p>I use what is below to quickly compare two Oracle database schema&#8217;s tables.  It would be trivial to add indexes to the mix.<\/p>\n<pre lang=\"java\" line=\"1\">\r\nimport java.sql.*;\r\nimport java.util.*;\r\n\r\npublic class schemaDiffer {\r\n  public static void main(String args[]) {\r\n    try {\r\n      Class.forName(\"oracle.jdbc.driver.OracleDriver\");\r\n      Connection conn1 = DriverManager.getConnection(\"jdbc:oracle:thin:\" + args[0]);\r\n      Connection conn2 = DriverManager.getConnection(\"jdbc:oracle:thin:\" + args[1]);\r\n      PreparedStatement pst1 = conn1.prepareStatement(\"select table_name,column_name,data_type,data_length,data_default from all_tab_columns where owner = upper(?)\");\r\n      PreparedStatement pst2 = conn2.prepareStatement(\"select table_name,column_name,data_type,data_length,data_default from all_tab_columns where owner = upper(?)\");\r\n      pst1.setString(1,args[2]);\r\n      pst2.setString(1,args[3]);\r\n      HashMap hm1 = new HashMap();\r\n      HashMap hm2 = new HashMap();\r\n      ResultSet rst1 = pst1.executeQuery();\r\n      while (rst1.next()) {\r\n        String[] s = {rst1.getString(3),rst1.getString(4),rst1.getString(5)};\r\n        hm1.put(rst1.getString(1) + \"~\" + rst1.getString(2), s);\r\n      }\r\n      ResultSet rst2 = pst2.executeQuery();\r\n      while (rst2.next()) {\r\n        String[] s = {rst2.getString(3),rst2.getString(4),rst2.getString(5)};\r\n        hm2.put(rst2.getString(1) + \"~\" + rst2.getString(2), s);\r\n      }\r\n      Iterator e = hm1.keySet().iterator();\r\n\t  while (e.hasNext()) {\r\n        String key = (String)e.next();\r\n        String[] value = (String[])hm1.get(key);\r\n\t    if(hm2.containsKey(key)) {\r\n          String[] value2 = (String[])hm2.get(key);\r\n\t      for (int j = 0; j < value2.length; j++) {\r\n\t        if (value[j] == null &#038;&#038; value2[j] == null) {\r\n\t          \/\/don't compare nulls\r\n\t        }\r\n\t        else if (value[j] == null || value2[j] == null) {\r\n\t          System.out.println(key + \" differs on \" + j + \", \" + value[j] + \" \" + value2[j]);\r\n\t        }\r\n\t        else if (! value2[j].equals(value[j])) {\r\n\t          System.out.println(key + \" differs on \" + j + \", \" + value[j] + \" \" + value2[j]);\r\n\t        }\r\n\t      }\r\n\t    }\r\n\t    else {\r\n\t      System.out.println(\"no match for \" + key);\r\n\t    }\r\n      }\r\n    }\r\n    catch(Exception e) {\r\n      e.printStackTrace();\r\n    }\r\n  }\r\n}\r\n<\/pre>\n<p>Below is a sample invocation in a DOS window...<\/p>\n<pre lang=\"text\">\r\nC:\\Users\\showard>java schemaDiffer system\/foobar@some_server1:1521\/db1 ^\r\nMore? system\/foobar@seom-server2:1521\/db2 ^\r\nMore? qa ^\r\nMore? prod\r\nLEGACY_USER_ORDERS~EMAIL_ADDRESS differs on 1, 255 40\r\nDPS_CONTACT_INFO~CITY differs on 1, 255 30\r\nDPS_CONTACT_INFO~COMPANY_NAME differs on 1, 255 40\r\nMAIL_INFO~ADDRESS1 differs on 1, 255 40\r\nMAIL_INFO~ADDRESS2 differs on 1, 255 40\r\nDPS_USER~LOGIN differs on 1, 255 40\r\nMAIL_INFO~CITY differs on 1, 255 40\r\nDPS_CONTACT_INFO~ADDRESS1 differs on 1, 255 50\r\nDPS_CONTACT_INFO~ADDRESS2 differs on 1, 255 50\r\nMAIL_INFO~EMAIL differs on 1, 255 40\r\nDPS_CONTACT_INFO~STATE differs on 1, 255 20\r\nDPS_USER~PASSWORD differs on 1, 255 50\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>I use what is below to quickly compare two Oracle database schema&#8217;s tables. It would be trivial to add indexes to the mix. import java.sql.*; import java.util.*; public class schemaDiffer { public static void main(String args[]) { try { Class.forName(&#8220;oracle.jdbc.driver.OracleDriver&#8221;);&hellip;<\/p>\n<p class=\"more-link-p\"><a class=\"more-link\" href=\"http:\/\/appcrawler.com\/wordpress\/2012\/11\/27\/schema-compare-command-line-tool\/\">Read more &rarr;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"footnotes":""},"categories":[19,25,22],"tags":[],"_links":{"self":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/2623"}],"collection":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/comments?post=2623"}],"version-history":[{"count":6,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/2623\/revisions"}],"predecessor-version":[{"id":2629,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/2623\/revisions\/2629"}],"wp:attachment":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/media?parent=2623"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/categories?post=2623"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/tags?post=2623"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}