What causes an Invalid column type error with Oracle

This error is a bit misleading. What it is trying to tell you is that your jdbc driver does not know how to set one of your columns to null. In the following section I will show you what causes this error and how to correct it.

First lets start with a simple POJO (Plain Old Java Object).

Address.java

import java.util.Date;

public class Address {
	private String line1;
	private String line2;
	private String city;
	private String state;
	private String postalCode;
	private Date purchaseDate;
	private Date soldDate;

	...  Standard mutators (getters setters) ...
}

Here is the example table that we will be using.

Address.sql

Address {
		line1			varchar2(100),
		line2			varchar2(100),
		city			varchar2(100),
		state			varchar2(100),
		postal_code	varchar2(25),
		purchase_date	date,
		sold_date		date
	}

With this in place we can create the sqlmaps to work with it. With this error only your insert and update statements will be of concern. I will use an insert both the solution works for both.

Address.xml

<typeAlias alias="Address" type="com.abc.domain.Address"/>

<insert id="insertAddress" parameterClass="Address">
        INSERT INTO 
            ADDRESS (
		line1,
		line2,
		city,
		state,
		postal_code,
		purchase_date,
		sold_date	
                )
            values (
                #line1#,
		#line2#,
		#city#,
		#state#,
		#postal_code#,
		#purchase_date#,
		#sold_date#	
            )
</insert>

So far everything looks good and will work part of the time. In the following example the classic invalid column type error will be thrown.

Example.java

Address address = new Address();
	address.setLine1("123 Anywhere Street");
	address.setCity("Somewhere over the Rainbow");
	address.setState("Never never land");
	address.setPostalCode("01234");
	address.setPurcahseDate(purcahseDate);  //purchaseDate defined somewhere above this code

Notice in the code above that I leave the line2 and soldDate properties as null. This is where the problem creeps in. When the insert sqlmap come to the line2 column it will throw an error. There are two solutions for this problem.

The first is to use dynamic conditions.

Address.xml

<typeAlias alias="Address" type="com.abc.domain.Address"/>

<insert id="insertAddress" parameterClass="Address">
        INSERT INTO 
            ADDRESS (
		line1,
		line2,
		city,
		state,
		postal_code,
		purchase_date,
		sold_date	
                )
            values (
                #line1#,
		<isNull property="line2">
			null,
		</isNull>
		<isNotNull property="line2">
			#line2#,
		</isNotNull>
		#city#,
		#state#,
		#postal_code#,
		#purchase_date#,
		#sold_date#	
            )
</insert>

As you can see this can become a time consuming process to make sure that every column that can be null has these tags. A second and cleaner option is to define what the jdbc type is.

Address.xml

<typeAlias alias="Address" type="com.abc.domain.Address"/>

<insert id="insertAddress" parameterClass="Address">
        INSERT INTO 
            ADDRESS (
		line1,
		line2,
		city,
		state,
		postal_code,
		purchase_date,
		sold_date	
                )
            values (
                	#line1#,
		#line2:VARCHAR#,
		#city#,
		#state#,
		#postal_code#,
		#purchase_date#,
		#sold_date:DATE#	
            )
</insert>

Just do this only any column that can be null! Now you may be asking yourself what are the valid jdbc types. Look no further they are posted in the Java API documents java.sql.Types.

Page Operations

Browse Space