Oracle : Convert a column into row in Oracle

In Oracle 11g database new functionality UNPIVOT to convert the column into rows.

My answer on on the same with example.

Python Scripting : Get max Last Modified Date using os.walk

Below is the code that i have written to get the max Lsat Modified Date of the files in all folders that i can delete all the folders that are not not being used by me for a long time.

import os
import datetime
print “This is console module”
def comparefiletime(in_file,in_date) :
lastdate =datetime.datetime.fromtimestamp(os.path.getmtime(in_file))
if lastdate > in_date:
return lastdate
return in_date

def getmaxdatefolders(in_root_fldr):
for foldern, sfoldern, filen in os.walk(in_root_fldr):
maxdate = datetime.datetime(1900, 1, 1,00,00)
for fileo in filen:
maxdate = comparefiletime(os.path.join(foldern,fileo),maxdate);
printmax date for the folder %s is %s’ %(foldern, maxdate)


Good Post on Clean Code

Using SQL Statements as a String in Class file while working on Android or Java application can be such a pain.

I was checking in the net to find out whether there are any ways in which we can store the SQL Statments separately so that the code can be more readable.

I found some interesting stuff. Please find below the links for the same.

1. Clean Code

2. Java Practices : Keep SQL out of code.

3. Android : Store SQL Queries in an XML File (Stackoverflow answer)

I am working on a way to store all my SQL Statements in an XML File while working with Android.

Found this post on placing XML File with Data on Android in StackOverflow.


  • No subdirectorys are allowed under the specific resource-folders.
  • The R-class indexes all resources and provides simple access.
  • There are some simple methods which help reading files stored in the res-directory


  • Subdirectorys are allowed (as much as you like).
  • No indexing by the R-class
  • Reading resources stored in assets is done using the AssetManager.

Also found a cool post on Working with resources in Android : Link.


SQL Statements in the case of Android can be stored in the strings.xml under the resources–> values  folder.

In case you using a Database class which is a pure java class the this link will be able to help you on how to do the same.

In my case, I created a separate DAO Class called the DBClass which deals with the connecting and disconnecting with the database. I set the context of the DAO class so that the strings can be used here.

Working with date in SQLite for Android

Working with dates in Oracle for reporting is very easy ( Since Oracle recognizes the date as datatype). But SQLite does not recognize Date as a datatype, this causes an issue when when you have to query for records that fall in between a range in SQLite.


Oracle it is very easy, just use he to_date command –

Oracle query :

SELECT amount,purchase_date FROM table_z
WHERE purchase_date >= TO_DATE('21-MAY-2011’,’DD-MON-YYYY’)
  AND puchase_date  <  TO_DATE('21-JAN-2012','DD-MON-YYYY');

In SQLite date needs to be converted into appropriate format before storing it in the database.

While storing in the database the date can be converted into YYYYMMDD format and stored in the database as an integer. Storing in this format in the database will ensure that older dates will have lesser value than the later ones.

Eg: 21-MAY-2011 will have 20110521

      21-JAN-2012  will have 20120121

The integer 20110521 < 20120121

So the SQlite equivalent of the query will be

SQLite Query :

SELECT amount,purchase_date FROM table_z
WHERE purchase_date >= 20110521
  AND puchase_date  <  20120121

Source: StackOverflow

Create a Alert Dialog in Android

Dialogs are generally used to display the the intermediate data like Status Messages, Warning Messages or Error Messages.

In this post we will go through how to create a Dialog Box in Android App development –

  • Create a function will call the Alert Dialog (AddEx is the activity. You can use your activity name over here ).
  1: private void displayDialog(String str1)
  2: {
  3:   AlertDialog.Builder builder = new AlertDialog.Builder(AddEx.this);
  4:   builder.setMessage(str1);
  5:   builder.setCancelable(false);
  6:   builder.setTitle("Status");
  7:   builder.setPositiveButton("OK", new DialogInterface.OnClickListener() {
  8:          @Override
  9:          public void onClick(DialogInterface dialog, int id) {
 10:           AddEx.this.finish();
 11:          }
 12:        });
 13:   AlertDialog alert = builder.create();
 15: }

  • Call this function anywhere in your activity class.
  1:  if(status == true)
  2:  {
  3:   displayDialog("Completed Successfully");
  4:  }
  5:  else
  6:  {
  7:    displayDialog("Error inserting into Table");
  8:  }
  • In the Alert Dialog you can also implement builder.setNegativebutton() method to have “CANCEL” part included in the dialog. Implementation is similar to setPositiveButton.

How to compile JAVA Project into a single JAR File using NetBeans

I found an interesting article about compiling a NetBeans project into a single JAR file.

<?xml version="1.0" encoding="UTF-8"?>
<project name="build-OneJar1" default="all" basedir="..">
   <target name="package-for-store" depends="jar">

        <!– Change the value of this property to be the name of your JAR,
             minus the .jar extension. It should not have spaces.
             <property name="" value="MyJarName"/>
        <property name="" value="MyProject"/>

        <!– don’t edit below this line –>

        <property name="store.dir" value="store"/>
        <property name="store.jar" value="${store.dir}/${}.jar"/>

        <echo message="Packaging ${application.title} into a single JAR at ${store.jar}"/>

        <delete dir="${store.dir}"/>
        <mkdir dir="${store.dir}"/>

        <jar destfile="${store.dir}/temp_final.jar" filesetmanifest="skip">
            <zipgroupfileset dir="dist" includes="*.jar"/>
            <zipgroupfileset dir="dist/lib" includes="*.jar"/>

                <attribute name="Main-Class" value="${main.class}"/>

        <zip destfile="${store.jar}">
            <zipfileset src="${store.dir}/temp_final.jar"
            excludes="META-INF/*.SF, META-INF/*.DSA, META-INF/*.RSA"/>

        <delete file="${store.dir}/temp_final.jar"/>

  • Save the above code as build-OneJar.xml and save it in the NetBeans project folder.( I saved in the nbproject folder and also had the file as a ANT project file.).

  • In Build.xml put an import statement and import the build-onejar.xml file.

  • To build the NetBeans project into a single JAR file, it needs to be run using the specified parameters as TARGET. To do this Right Click build.xml in the Files TAB and select Run Target –> Other Targets –> Package-For-Store.

  • The JAR file will be built and stored in a folder called Store in the
  • Also remember to save the build-onejar.xml file as a template for future use. To do this go right click on the file in the Files TAB and select Save As Template….. select the appropriate folder and click OK.


  In this way a NetBeans project can be compiled into a single JAR file.


How to use SQLite with Java using NetBeans IDE

  • SQLite is an embedded database which will have all the tables stored in a db file. This saves the trouble of having to setup database servers everytime we develop a new programs. Android uses SQLite as a database.
  • I was browsing through the net on how to use SQLite for a small app I was developing. I came across this page which had very good directions on how to use SQLite with Java.

1. SQLite is serverless. With SQLiteJDBC driver, we can write programs to access SQLite using JAVA.
This tutorial will have step by step description about how to do it.
2. We will use Netbeans, but it will work in the same manner with eclipse too.
3. Download SQLiteJDBC driver jar, ‘sqlitejdbc-v056.jar’ from website:
4. Create a java project named ‘SQLite’ in Netbeans.(You can give any name
5. Create a class called ‘Test’ in that project.
6. Add ‘sqlitejdbc-v056.jar’ in the class path of ‘SQLite’ project in Netbeans.
7. Paste following code in Test class
import java.sql.*;
public class Test {
public static void main(String[] args) throws Exception {
Connection conn =
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists school;");
stat.executeUpdate("create table school (name, state);");
PreparedStatement prep = conn.prepareStatement(
"insert into school values (?, ?);");
prep.setString(1, "UTD");
prep.setString(2, "texas");
prep.setString(1, "USC");
prep.setString(2, "california");
prep.setString(1, "MIT");
prep.setString(2, "massachusetts");
ResultSet rs = stat.executeQuery("select * from school;");
while ( {
System.out.print("Name of School = " + rs.getString("name") + " ");
System.out.println("state = " + rs.getString("state"));
8. Run the project as java application.
9. It will create database file by name ‘Vinit’ in the Netbeans ‘SQLite’ project folder.
10. Here is in above code, we are creating sqlite database ‘Vinit’.

  • In NetBeans add libraries by going to Tools –> Libraries and Click ADD LIBRARY. An Add Library dialog will appear

  • Add the driver JAR file location into CLASSPATH tab

  • In the Projects window Right Click on Libraries folder and Select Add Library

Click on Add Library and select Sqlite3.