oracle Posts

[Java] org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (ORA-00923: FROM keyword not found where expected

Had this weird error, when connecting to Oracle Database

### The error occurred while executing a query
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; 
nested exception is org.apache.commons.dbcp.SQLNestedException: 
Cannot create PoolableConnectionFactory (ORA-00923: FROM keyword not found where expected
)

Somehow it never happens on my MySql Configuration. Here is my configuration,

<bean id="dataSourceOracle" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
          p:driverClassName="oracle.jdbc.driver.OracleDriver" p:url="${db.url.oracle}"
          p:username="${db.username.oracle}" p:password="${db.password.oracle}"
          p:initialSize="2"
          p:maxActive="30"
          p:maxIdle="10"
          p:minIdle="3"
          p:maxWait="30000"
          p:removeAbandoned="true"
          p:removeAbandonedTimeout="30"
          p:validationQuery="SELECT 1" />

After investigating for a while, i found out that it happens because i use MySql’s specific validationQuery, “SELECT 1″. Changing it into “SELECT 1 FROM DUAL” make the error go away. :-P

Google+

Menghapus Ratusan Juta Record dari Oracle Table

Baru kemarin ketemu case yang lumayan menarik, aplikasi yang dibuat tahun lalu kena timeout ketika delete seluruh isi table yang berisi paling tidak 200juta record. Berikut error lengkapnya, fyi framework yang dipakai adalah MyBatis dan Spring Framework dan database yang dipakai adalah Oracle,

<2016-10-31 02:15:01,723>,[http-/0.0.0.0:8080-16]>>[INFO]start deleting TABLE_NAME Data
<2016-10-31 02:18:02,426>,[http-/0.0.0.0:8080-16]>>[ERROR]
### Error updating database.  Cause: java.sql.SQLException: ORA-01013: user requested cancel of current operation

### The error may involve id.edwin.service.delete-Inline
### The error occurred while setting parameters
### SQL: delete from TABLE_NAME
### Cause: java.sql.SQLException: ORA-01013: user requested cancel of current operation

; uncategorized SQLException for SQL []; SQL state [72000]; error code [1013]; ORA-01013: user requested cancel of current operation
; nested exception is java.sql.SQLException: ORA-01013: user requested cancel of current operation

sepertinya sudah timeout duluan (3menit timeout), padahal belum semua data terhapus. :-(

Setelah googling bentar, sepertinya ada dua solusi yaitu menggunakan TRUNCATE dan CTAS (CREATE TABLE AS SELECT). Setelah diskusi panjang lebar dengan kuncen (admin) Database, opsi terakhir (sepertinya) jauh lebih cepat, drop table tersebut kemudian di re-create ulang. Berikut adalah query-nya

CREATE TABLE TABLE_NAME_NEW AS
        SELECT * FROM TABLE_NAME WHERE ROWNUM = 1 ;
Rename TABLE_NAME to TABLE_NAME_OLD ;
Rename TABLE_NAME_NEW to TABLE_NAME;
drop table TABLE_NAME_OLD ;

Setelah itu baru didelete isi table TABLE_NAME, lebih cepat karena isi datanya hanya 1 row. Satu-satunya kekurangan adalah, tidak bisa replicate Primary Key dan Index dari table yang sebelumnya di drop, yang mana itu bukanlah masalah bagi saya :-D

Google+

Weird MyBatis Error, “Error setting null for parameter [number] with JdbcType OTHER”

I had a very weird error today, when connecting MyBatis to Oracle Database, the complete error is like this,

Error setting null for parameter #2 with JdbcType OTHER . 
Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. 
Cause: java.sql.SQLException: Invalid column type: 1111

After researching for a while, i found out that it happen because i miss to fill a parameter on mybatis xml query file. This is my xml file,

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="mybatistesting.MyMapper" >    
    <select resultType="java.util.Map" id="select" parameterType="java.util.Map">
        SELECT
            *
        FROM
            RISALAH
        WHERE UUID = #{UUID} AND CONTENT = #{CONTENT}
    </select>
</mapper>

And this is my main java file,

package mybatistesting;

import java.util.HashMap;
import java.util.Map;
import mybatistesting.config.MyBatisSqlSessionFactory;
import org.apache.ibatis.session.SqlSession;

public class MyBatisTesting {

    public static void main(String[] args) throws Exception {
        SqlSession sqlSession = MyBatisSqlSessionFactory.getSqlSessionFactory().openSession(true);
        MyMapper mapper = sqlSession.getMapper(MyMapper.class);
        Map map1 = new HashMap();
        map1.put("UUID", "1");
        for (Map map : mapper.select(map1)) {
            System.out.println("map "+map);
        }
    }
}

As you can see, i only set UUID as query parameter on my java file, despite that i need at least 2 parameter on my xml file. Here is how to fix it,

package mybatistesting;

import java.util.HashMap;
import java.util.Map;
import mybatistesting.config.MyBatisSqlSessionFactory;
import org.apache.ibatis.session.SqlSession;

public class MyBatisTesting {

    public static void main(String[] args) throws Exception {
        SqlSession sqlSession = MyBatisSqlSessionFactory.getSqlSessionFactory().openSession(true);
        MyMapper mapper = sqlSession.getMapper(MyMapper.class);
        Map map1 = new HashMap();
        map1.put("UUID", "1");
        map1.put("CONTENT", "1");
        for (Map map : mapper.select(map1)) {
            System.out.println("map "+map);
        }
    }
}

Hope it helps others, cheers ;)

Google+

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+

[Oracle] How to Truncate and Clean All Your Database’s Tables

I worked with Oracle for this project, and sometime i need to truncate and clean all my oracle database’s content. So instead of truncate the tables one by one, i found a simple query to generate a simple truncate query.

select 'truncate table ' || table_name || ';' from user_tables

Hope it helped others :)

Google+