Java Hair

Today I ran into an interesting problem. A portion of our software saves the text of database DML statements into an Oracle schema. We had been allocating 4000 bytes of a VARCHAR2 column in order to store that text. We’ve been using this software to analyze customer’s applications for about a year now and hadn’t run into any difficulties with this setup.

Today, while persisting some data about a customer’s form application, our software started to blow up, throw Oracle ORA-01461 errors. The text of the error is “can bind a LONG value only for insert into a LONG column.” At first glance it seemed obvious enough, we were trying to insert a LONG value into a column that wasn’t a LONG column.

The first thing to realize is that a LONG value in Oracle’s database world is NOT a Long value in Java. Since there were about 6 of these types of fields being inserted into a table, that was my first bad assumption, that one of these was wrong. Of course, they all matched up to the columns in the table correctly, but assuming that something strange was going on in the driver, I manually switched these statements to setting BigDecimals instead of long’s. Naturally, this didn’t help at all.

I did turn out to be correct on one count, however. Something strange was happening in the driver. After being puzzled for a bit longer, I called in a developer to help me talk through the problem, and as often happens, just talking about the problem often brings new insight into the situation.

I realized while explaining to this developer that a LONG does not equals a Long, that the problem must lie in the text fields. Duh. So, since I was debugging at that point already, I inspected the values I was trying to insert and realized that one of the text fields was actually pretty large. So large, in fact, that the eclipse debugger couldn’t show me the entire value. It did let me know that the String i was looking at was almost 15,000 characters in length, however.

Aha! So, checking the table structure showed me that the column was set up for VARCHAR2(4000). Apparently, instead of the driver throwing an exception that the value was too large for the column, because the String is over 4000 characters long, it automatically converted it to a LONG datatype and tried to insert that resulting in the ORA-01461 error I was seeing.

To test this theory, I put in some code to check the length of the String and truncate it to 4000 characters if it was longer than that. Voila, no more errors. Of course, I’ll have some work to do to change the column type, probably to a CLOB like the rest of our really long text fields, but that is going to mean changing some code around since CLOBs are not as easily read from the database as VARCHAR2’s are, but at least the problem is isolated.

In my search for information about this error I did not see anything that mentioned this, so I thought I post it to hopefully help some other poor sucker that’s butting his head against his keyboard in frustration due to a similar issue. Hope this helps!

Related posts:

1. Webinar: Client/Server Oracle Forms Modernization with Oracle ADF 11g You might thinnk I could have come up with a...
2. What’s in our (Oracle) Forms? Though this blog typically contains how-to’s and editorials and Java...
3. Mapping Oracle Forms to ADF 11g - An Overview  If you are looking to migrate your Oracle Forms applications...
4. Back from Oracle Open World 2008 I am back from Open World 2008 and been so...
5. Vote for my Oracle Open World Session The talk I presented at ODTUG this year went over...

You must be logged in to post a comment.