A command to remove all Windows mapped drives

If you work in a Corp env and having a Laptop, you will likely go home with it still running and it must has all the network drives still mapped. And then later when you are at home and try to use your Laptop, you will see considerable delay. The Windows Explore and many Windows application that access File Browser dialog will try relentlessly in reconnecting those drives without sucess. This also affect your Cygwin shell too.

Here is a DOS command to remove them all.

C:> net use * /delete /y

If you are in Cygwin bash, then you must quote the '*'.

Intellij IDEA is pretty awesome!

Did you know IntelliJ IDEA has a free Community Edition you may download? I've been test driving it, and I will post few things I learned so far. Here are few things that I usually change from out of the default installed settings.

1. File > Settings > Editor => Uncheck "Allow placement of caret after end of line" -- This is the most annoying thing for me in IDE. It's just not for me.
2. File > Settings > Editor > Appearance => Check "Show line numbers" -- I tend to relate errors and navigate content better with line number displayed.
3. File > Settings > Appearance > Theme => Select "Darcula" -- This is latest version 12.0 feature. Dark screen is pretty eye pleasing compare to WHITE.
4. File > Settings > Compiler => Check "Make project automatically" -- Another new 12.0 feature that would perform incremental compile.

Now the IDE is ready for me to work on any Java projects. If you work with Maven, then you will be happy to know that IDEA supports it out of box. You can get an existing project started with following steps:

1. File > Import Project => Browse to your project pom.xml. Even if you have multi Maven modules, you only need to pick the top level pom.xml and it will generate the multi IDEA modules for you automatically. Then click "Ok" button.
2. In "Import project from external model" select "Maven", and then "Next".
3. You can accept all default settings on this prompt, and simply go "Next"
4. Check all maven project to import
5. Next screen is selecting a SDK for this project (meaning pick a JDK) to use. If this is your first time setup, then create a new one by click on the Grean plus icon, then browse to your JAVA_HOME directory and click OK. Click "Next" to go next step.
6. Simply click "Next" to accept default project name. Then you are at last prompt. Simply click "Finish" button and you should have your project ready to go.

It might seems like a lot of steps to import a project, but IDEA is really nice. After your project has completely imported and compiled, you should able open any of your Java class (use CTRL+N) that has main method or any JUnit classes. Right click the content editor and select "Run ..." to execute it (or CTRL+SHIFT+F10 to run any single unit test method).

I have tried a decent size Java projects (few thousand source files) and it handles it very gracefully. The UI such as Menu items, Control buttons and tool bars are very user friendly and intuitive to use. Also, right click on most places should give you a "Context" menu with related options. Like any IDE, it supports the CTRL+Space that auto complete code as you type. There is the ALT+ENTER would suggest hints on how to fix things when you got an error with red squiggle line. Pressing CTRL+B will jump into methods or variable declarations.

Another tips I have for you is the IDEA uses many icon symbols to represent many things. These give you quick visual status on files, progress and states of your Classes etc. It's helpful to see what they mean using this http://www.jetbrains.com/idea/webhelp/symbols.html

So overall, I think IntelliJ IDEA is pretty good and awesome in many way.

Checking DB Connection using Java

For complete sake, here is a Java version of the Groovy post to test your Oracle Database connection.

package atest;
import java.sql.*;
/**
 * Run arguments sample:
 * jdbc:oracle:thin:@localhost:1521:XE system mypassword123 oracle.jdbc.driver.OracleDriver
 */
public class DbConn {
    public static void main(String[] args) throws Exception {
        String url = args[0];
        String username = args[1];
        String password = args[2];
        String driver = args[3];

        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, username, password);
        try {
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery("SELECT SYSDATE FROM DUAL");
            while(rs.next()) {
                System.out.println(rs.getObject(1));
            }
        } finally {
            conn.close();
        }
    }
}

Creating Oracle Stored Procedure using Java

Did you know you can write Oracle database stored procedure in Java? Give this a try in your sqlplus prompt.

sql> create or replace and compile java source named "MyJavaDbProcedure" as
sql> public class MyJavaDbProcedure {
sql>   public static String upcase(String text) {
sql>     return text.toUpperCase();
sql>   }
sql> };
sql> /
sql> 
sql> create or replace function upcase (s in varchar2)
sql>   return varchar2
sql> as language java
sql>   name 'MyJavaDbProcedure.upcase(java.lang.String) return java.lang.String';
sql> /
sql> 
sql> select upcase('hello') from dual;
sql> /

I let the database compile a Java source directly, but there is also the java class PL/SQL that you can load Java binary .class file as well. I am sure your DBA will fight all their might to prevent you doing stuff like this. But it's cool to see that this option is available.

Checking DB connection using Groovy

Here is a simple Groovy script to verify Oracle database connection using JDBC.

@GrabConfig(systemClassLoader=true)
@Grab('com.oracle:ojdbc6:11g')
url= "jdbc:oracle:thin:@localhost:1521:XE"
username = "system"
password = "mypassword123"
driver = "oracle.jdbc.driver.OracleDriver"

// Groovy Sql connection test
import groovy.sql.*
sql = Sql.newInstance(url, username, password, driver)
try {
  sql.eachRow('select sysdate from dual'){ row ->
    println row
  }
} finally {
  sql.close()
}

This script should let you test connection and perform any quick ad hoc queries programmatically. However, when you first run it, it would likely failed without finding the Maven dependency for JDBC driver jar. In this case, you would need to first install the Oracle JDBC jar into maven local repository. This is due to Oracle has not publish their JDBC jar into any public Maven repository. So we are left with manually steps by installing it. Here are the onetime setup steps:

1. Download Oracle JDBC jar from their site: http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html.

2. Unzip the file into C:/ojdbc directory.

3. Now you can install the jar file into Maven local repository using Cygwin.

bash> cd /cygdrive/c/ojdbc
bash> mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11g -Dpackaging=jar -Dfile=ojdbc6-11g.jar

That should make your script run successfully. The Groovy way of using Sql has many sugarcoated methods that you let you quickly query and see data on screens. You can see more Groovy feature by studying their API doc.

Note that you would need systemClassLoader=true to make Groovy load the JDBC jar into classpath and use it properly.

Oh, BTW, if you are using Oracle DB production, you will likely using a RAC configuration. The JDBC url connection string for that should look something like this:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MY_DB)))

---

Update: 12/07/2012

It appears that the groovy.sql.Sql class has a static withInstance method. This let you run onetime DB work without writing try/finally block. See this example:

@GrabConfig(systemClassLoader=true)
@Grab('com.oracle:ojdbc6:11g')
url= "jdbc:oracle:thin:@localhost:1521:XE"
username = "system"
password = "mypassword123"
driver = "oracle.jdbc.driver.OracleDriver"

import groovy.sql.*
Sql.withInstance(url, username, password, driver) { sql ->
  sql.eachRow('select sysdate from dual'){ row ->
    println row
  }
}

It's much more compact. But be aware of performance if you run it multiple times, because you will open and close the a java.sql.Connection per each call!

---

I have also collected couple other popular databases connection test examples. These should have their driver jars already in Maven central, so Groovy Grab should able to grab them just fine.

// MySQL database test
@GrabConfig(systemClassLoader=true)
@Grab('mysql:mysql-connector-java:5.1.22')
import groovy.sql.*
Sql.withInstance("jdbc:mysql://localhost:3306/mysql", "root", "mypassword123", "com.mysql.jdbc.Driver"){ sql -> 
  sql.eachRow('SELECT * FROM USER'){ row ->
    println row
  }
}

// H2Database
@GrabConfig(systemClassLoader=true)
@Grab('com.h2database:h2:1.3.170')
import groovy.sql.*
Sql.withInstance("jdbc:h2:~/test", "sa", "", "org.h2.Driver"){ sql -> 
  sql.eachRow('SELECT * FROM INFORMATION_SCHEMA.TABLES'){ row ->
    println row
  }
}

Changing Oracle XE apex webapp port

If you use Oracle XE for development, then you know it has a simple web interface through http://localhost:8080/apex. Now port 8080 is a common port used by Tomcat server, so letting Oracle XE permanently have taken it is not convenient. But you can change it like this:

 sql> select dbms_xdb.gethttpport from dual;
 sql> exec dbms_xdb.sethttpport('8081');
 sql> commit;

 bash> open http://localhost:8081/apex

Also, if you ever deleted the HR sample database on the XE installation, you can restore it like this:

 bash> cd /c/oraclexe/app/oracle/product/10.2.0/server/demo/schema/human_resources
 bash> sqlplus system < hr_main.sql

Inspecting your Oracle database

Some quick tips on how to inspect what you have in your Oracle database.

-- show all schemas
select distinct owner from dba_segments where owner in
     (select username from dba_users where default_tablespace not in ('SYSTEM','SYSAUX'));

-- show all tables from a schema/owner
select * from all_tables where owner = 'HR';

-- show table description
desc HR.EMPLOYEES;

-- show all users
select * from all_users;
select username, * from dba_users;


-- See who is taking up a DB lock
select c.*, b.* from v$lock a, dba_locks b, v$session c 
  where a.id1 = b.lock_id1 and b.session_id = c.sid

-- See internal SQL id
select * from v$sql

-- See Oracle latches:
select * from v$latch

-- See Library Cache of SGA (System Global Area) like buffer cache size:
select * from v$sgastat

-- See tx locks (TX) and DML locks (TM):
select * from v$lock where type in ('TX', 'TM')
select * from dba_locks where lock_type in ('Transaction', 'DML')