The peekaboo column type

Alright, I will have to admit that the misuse of xml is irritating me quite a bit. Xml is reaching a critical desalination point (sorry Quaid) and is set to freeze the world over. I sincerely hope that xml abuse will be the cause of next Internet meltdown. Disproportionate amount of control information thrown along with a tiny amount of data is going to upset someone over the wire, causing a data-strife and leading to abrupt strike by tcp services.

That was pretty much my dream when I struggled over what I thought was a simple solution to a really simple problem. I was working on a data-migration project, you know, the routine thingy, read data from a legacy database, convert to xml and stuff down the throat of another database. I picked up Grails to get it done, uh well, with a premise that I do not have to handwrite a single query and can simply map tables to domains and magically invoke “save()”.

I was short-circuiting Grails by using it only for GORM features, but running as a grails script, and not for any ui, scaffolding and many of it’s “traditional” strenghts.

1. GORM hates tables with no keys

The legacy tables I dealt with did not have any PKs at all. And GORM does not like them because of required implicit id. Yes composite keys work fine, but the legacy tables did not have any unique rows either. So the solution the was to simply create a view on the table:

select rownum as uniquekey, t.* from table t

I could care a damn for what the rownum was, as long as it was unique. I mapped the GORM id to the uniquekey and all was okay.

2. Grails Spring Context in a script environment

Grails does an amazing job of injecting Spring beans whereever you expect to. All’s well when running as a web app, but I was running this as a grails script, so the Spring services were not getting injected.


void eatPrayAndStuff() {
MyService service = new MyService()

class MyService {

void eatFromLegacyDb()
def query = sessionFactory.currentSession().createSQLQuery(sql)

The script is run as following:

# grails –stacktrace dev run-script MyScript.groovy

Obviously, the service object was not null, but I was promptly getting null on currentSession. I understood Spring cannot inject its singleton service into the script, but I just didn’t know how.

Thanks to a quick response in stackoverflow:

MyService service = ctx.MyService

instead of new MyService(). That’s one of the great strenghts of grails, the spring context is available throughout the script environment (not just Web and Unit Test) and its easy to experiment with. It just takes a bit of hunting, and asking for help.

3. XMLType – The Clown Prince of Column types

I ran the script and it ran well about a 1000 records and I almost declared success. Almost. And then came the bouncer.

Colleges and Universities teach a lot about how to do programming. Sadly there are no professors who take a class on “How to write BAD code”. And set a test on some really professional-industry-level bad code. The experience gained from doing wrong things is hardly equivalent to making the right choices. Yeah, you know what Edison said.

Oracle’s SQL Errors (and JDBC Sql Exceptions) will always be dear to my heart in realizing how to write bad code. So I got the error:

ORA-01461: can bind a LONG value only for insert into a LONG column

I had a few long values in my domain, so I scratched off one by one, like a senile lottery ticket buyer, and after some debugging found that all numbers are good (Damn lottery pun!). By process of elimination I realized it was the XMLType column that wasn’t behaving. The reason was the size of the xml data was 4100 bytes in length. And Oracle’s XMLType is an “XMLType” if its data size < 4kb, but if its above 4kb its a CLOB. Good deal! A dynamically self-morphing peekaboo data column type, FWIW.

Someone suggested to use a stored procedure instead of domain mapping, so I cajoled my co-worker to quickly write a wrapper storedproc for me. Try what ever I could not get past the next error:

ORA-06553: PLS-306: wrong number or types of arguments in call.

When things go wrong, switch to basics, so they say. So I went back to basics of counting numbers by my fingers, nails and toes: all arguments are correct, all types are correct. Yet Oracle thinks I pass wrong arguments.

I abandoned the stored procedure route and turned to using Oracle XDB for the XMLType. Oracle does not respect Maven community and somehow I located an xdb.jar and dropped into the grails lib directory. But STS Eclipse has issues with properly refreshing libraries. So I had to clean, close, sweep, brush the project from both STS and command line to get it included in the classpath. But surprise, xdb.jar has its own SAXParser classes which interfere with the Grails scripts.

Googling around, there was a suggestion to use xmlparserv2.jar in grails/lib. I again I hunted the jar down, but If I put it in the lib directory, grails wouldn’t even compile Then I put it in the classpath at runtime, but I simply got ClassNotFoundException.

# grails –stacktrace -cp lib/xmlparserv2.jar dev run-script MyScript.groovy

I guess the -cp option works fine if running as a run-app, but while invoke the gant scripts, the -cp is not honored (Grails 2.1.0). I realized I may have to tweak the Run-Script, but I didnt want to go that far, because the script will be run in environments I dont even know and didnt want to assume a lot.

I abandoned the XMLType route. Oracle’s half-cooked XMLType implementation and its support api isnt for someone who likes to be productive. I took a break, wondering whats the need for storing a 200 byte data – yes, thats what the actual datasize was, but ended up  as a 4100 byte xml with all kinds of tags. Thats the anguish about xml abuse and the internet meltdown that I fervently hope.

4. When in Rome, treat everybody like British

Finally I found a piece of code, which converted the XMLType into a Clob in a round-about way. Not sure why converting one data type to another needs a javax.sql.Connection parameter. This is like, if you want to pour water from one container to another container, you need to send it to a hydro-electric plant.

private CLOB getCLOB(String xmlData, OracleConnection oracleConnection) throws SQLException{
CLOB clob = null
try {
clob = CLOB.createTemporary(oracleConnection, true, CLOB.DURATION_SESSION)
Writer clobWriter = clob.getCharacterOutputStream()
} catch(SQLException sqlx){
} catch(Exception x){
return clob

Only to discover that the second parameter OracleConnection is not a javax.sql.Connectin per se, and certainly not what I have in the Hibernate’s sessionFactory.currentSession().connection() which is Proxy19 – a connection proxy. So how do I get an OracleConnection from a proxy connection?

5.Droid to the Rescue

Connection connection = sessionFactory.currentSession.connection()
PreparedStatement ps = connection.prepareStatement(SQL)
C3P0NativeJdbcExtractor cp30NativeJdbcExtractor = new C3P0NativeJdbcExtractor();
OracleConnection oc = (OracleConnection) cp30NativeJdbcExtractor.getNativeConnection(ps.getConnection());
CLOB c = getCLOB(xml, oc)
ps.setObject(3, c)

And so I finally after a long self-guided tour of google, oracle, stackoverflow and several more sites that even my browser history could not remember, got the xmltype working, but not before I scratched around to fix the sql statement itself:

String SQL = /insert into table1(?,?,?) values (?,?,xmltype(?))/

And so the conspiracy was settled – Grails, Hibernate, Script, Spring, Oracle, XMLType, CLOB, Connection Proxy – all concocted together to make a nightmare of a simple insert statement.


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: