utility Posts

How to Simulate and Finding Connection Leak in Oracle

It’s been a while since the last time i’ve write tutorials in my blog, but i found a very interesting case that perhaps helped a lot of people. Today im trying to create a simple test case to simulate a connection leak and how to search for it.

First is creating a simple unclosed connection query using java,

package com.edw;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Main {

    public static void main(String[] args) throws Exception {
        System.out.println("starting --------");
        
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "USERNAME", "password");
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT * FROM INVALID"); // example table name is INVALID
        
        while(resultSet.next()) {
            System.out.println(resultSet.getString(1));
            System.out.println(resultSet.getString(2));
            System.out.println(resultSet.getString(3));
            System.out.println("=============");
        }
        
        System.out.println("ending --------");
        
        while(true) {
            Thread.sleep(10000);
            System.out.println("testing --------");
        }
    
    }
}

It will create an unclosed query that will hanging on my oracle connection.
And next, is my oracle query to find the hanging queries,

select LAST_CALL_ET, SQL_TEXT, username, machine, to_char(logon_time, 'ddMon hh24:mi') as login, 
    SQL_HASH_VALUE, PREV_HASH_VALUE, status
from v$session, v$sql 
where username='USERNAME' and HASH_VALUE =  PREV_HASH_VALUE
order by last_call_et desc;

it will show result like this,

LAST_CALL_ET SQL_TEXT                USERNAME     MACHINE        LOGIN                SQL_HASH_VALUE PREV_HASH_VALUE STATUS 
------------ ---------------------- ------------- -------------- -------------------- -------------- --------------- --------
          91 SELECT * FROM INVALID   USERNAME     edw      	     03Mar 11:53                       0       621168917 INACTIVE 

As you can see the result on the first column, “SELECT * FROM INVALID” query has run for more than 90 seconds.
So i can easily find the java class culprit who run that query.

FYI, this is my Oracle version,

select * from v$version;

BANNER                                                                         
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production              
PL/SQL Release 11.2.0.2.0 - Production                                           
CORE	11.2.0.2.0	Production                                                         
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production                          
NLSRTL Version 11.2.0.2.0 - Production   

I Hope this tutorial can help others, have fun :)

Google+

java.sql.SQLException: Numeric Overflow When Connecting to Oracle using MyBatis

Im using mybatis framework, and it was working very nice untuil today ive found a very weird error. A very weird error, because im only doing select queries and not insert anything.

This is the complete stacktrace,

Cause: java.sql.SQLException: Numeric Overflow
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:111)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102)
	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:119)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
	at com.sun.proxy.$Proxy190.selectByExample(Unknown Source)
Caused by: java.sql.SQLException: Numeric Overflow
	at oracle.jdbc.driver.NumberCommonAccessor.throwOverflow(NumberCommonAccessor.java:4381)
	at oracle.jdbc.driver.NumberCommonAccessor.getShort(NumberCommonAccessor.java:353)
	at oracle.jdbc.driver.OracleResultSetImpl.getShort(OracleResultSetImpl.java:1118)
	at oracle.jdbc.driver.OracleResultSet.getShort(OracleResultSet.java:418)
	at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getShort(DelegatingResultSet.java:272)
	at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getShort(DelegatingResultSet.java:272)
	at sun.reflect.GeneratedMethodAccessor198.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

This is my java bean,

public class TblInvalid implements Serializable {
    private short id;

    private String attribute7;

    // other setter and getter
}

My Oracle DDL looks like this,

CREATE TABLE TBL_INVALID
    (
        ID NUMBER,
        ATTRIBUTE7 VARCHAR2(255)
 )

The workaround is actually so simple, changing from Short into BigDecimal makes the problem goes away.

public class TblInvalid implements Serializable {
    private BigDecimal id;

    private String attribute7;

    // other setter and getter
}
Google+

[Weird Javascript Error] Leading Zero on Integer Variable Become Octal on Old Browser

Ive met a very weird error, that somehow never occurs to me on my browser. I found this error only happen on old mozilla browsers, and only happen on javascript’s parseInt method. Here is a simple example,

alert(parseInt("09")-1);

When im running it on my browser (Mozilla version 34), this is what happen,
mozillanew

But when running on old browsers (Mozilla version 3.6), this is what happen,
mozillaold

I finally found out that that on old browsers, leading zeros are treated as octal values instead of plain integers. So this is my workaround,

alert(parseInt("09".replace(/^[0]+/g,""))-1);

I removed the leading zero before parsing it into int.

Hope it can help others :)

Google+

Accessing JBoss Administration Console (Port 9990) From Network

Today, i have a very weird condition, im installing Jboss EAP 6.1 but i cannot access its administration console from outside ip. Somehow, everytime i run netstat -aon command, i can see that port 9990 binded only with 127.0.0.1.

The workaround is actually very simple, i only add -bmanagement on JBoss start command,

./standalone.sh -bmanagement=0.0.0.0

And now port 9990 is accessible by other network.

Google+

Regular Expression to Check Indonesian MobilePhone Provider

These are simple regex to check Indonesia’s Mobile phone provider based on its number,

telkomsel.regex = ^(\\+62|\\+0|0|62)8(1[123]|52|53|21|22|23)[0-9]{5,9}$

simpati.regex = ^(\\+62|\\+0|0|62)8(1[123]|2[12])[0-9]{5,9}$

as.regex = ^(\\+62|\\+0|0|62)8(52|53|23)[0-9]{5,9}$

indosat.regex= ^(\\+62815|0815|62815|\\+0815|\\+62816|0816|62816|\\+0816|\\+62858|0858|62858|\\+0814|\\+62814|0814|62814|\\+0814)[0-9]{5,9}$

im3.regex = ^(\\+62855|0855|62855|\\+0855|\\+62856|0856|62856|\\+0856|\\+62857|0857|62857|\\+0857)[0-9]{5,9}$

xl.regex = ^(\\+62817|0817|62817|\\+0817|\\+62818|0818|62818|\\+0818|\\+62819|0819|62819|\\+0819|\\+62859|0859|62859|\\+0859|\\+0878|\\+62878|0878|62878|\\+0877|\\+62877|0877|62877)[0-9]{5,9}$

Hope it can help others, ;-)

Google+