VARCHAR constants, Oracle 10g, and Oracle JDBC driver

Every day I have the luck to write some JDBC code @ work, and sometimes, in very specific conditions I face very strange errors. This post is about one problem that encountered while running a Java application, that queried a view in Oracle 10g database using Oracle JDBC driver v9.

In order to work with a complex database schema, a view was created to be used as an interface for a simple JDBC application. According to some specific business requirements, the view has to contain character constants - say, "if a column value is NULL then a query to the view should return 'X', otherwise, return the column's value". For this purpose, the view was created using the CASE/WHEN statements:

CREATE OR REPLACE VIEW view_name AS SELECT
CASE
 WHEN column_name='X' THEN 'A'
 ELSE 'B'
END
FROM DUAL

So, running a query "SELECT * FROM SOME_VIEW" was working OK and once we deployed the application in production we started to get some strange values from database. Instead of getting 'X' as it was defined, some whitespaces where added to the values, so that 'X' became 'X ', with two extra whitespaces, i.e. length 3 instead of length 1.

After some research we discovered that the system administrator was using Oracle JDBC driver v9 instead of a new one. Probably there are some "religious" reasons, why the version 9 should be preferred over version 10 for Oracle JDBC driver, especially with Oracle database version 10g. But the strange behavior of the application and the extra whitespaces problem had to be resolved to make the application work with the older driver.

Trimming every value that would be received from the view sounds like a stupid idea. So the first step was now to find out why the view constant was equipped with extra whitespaces. AFIAK, in Oralce 8i JDBC applications it was the common case that a CHAR type values were fetched with the whitespaces untrimmed, thus the obvious decision was made to seek for the view column type definition.

The metadata for a column's type is written in USER_TAB_COLUMNS and USER_COL_COMMENTS .

SELECT *
    FROM user_tab_columns ut
      , user_col_comments uc
  WHERE ut.table_name = ? -- name of the created view
    AND ut.table_name = uc.table_name
    AND ut.column_name = uc.column_name
ORDER BY ut.column_id

So the column type was CHAR(3)! So the error could be fixed if the view could be created in such way that the problematic column could be VARCHAR2. A CASE/WHEN statement can be replaced with DECODE function, thus:

CREATE OR REPLACE VIEW view_name AS SELECT
DECODE(column_name, 'X', 'A', 'B')
FROM DUAL

The column type in the metadata table is now VARCHAR2(1)!

The strange thing is now actually why the driver version 10 was working correctly with the CHAR type. Correctly in sense of trimming the witespaces, but is it really correct behavior? (a rethorical question)

One special case was solved, but in some other views some constants were defined without any conditions:

 CREATE VIEW some_view AS
   SELECT
      .....
      'X'
      .....
   FROM table_name
 WHERE condition

The value was still defined as CHAR in the metadata table. I don't know why, but I decided to describe the constant using TO_CHAR function with a random parameter, like this: TO_CHAR('X', 1). Apparently, it worked (!). The column was defined as VARCHAR2 now, but with maximum length of 4000. Is this some sort of undocumented feature of TO_CHAR function?! So far I knew that TO_CHAR function can be applied to a NUMBER or DATE type argument...

CREATE OR REPLACE VIEW view_name 
  AS SELECT TO_CHAR('A',1) FROM DUAL

The type issue was solved but in fact reserving 4000 symbols for the one-character-long constant is somewhat an overkill. Defining the constant via DECODE function solves this problem as well: DECODE('X', 'X', 'X'). So the type was now VARCHAR2(1).

CREATE OR REPLACE VIEW view_name 
  AS SELECT DECODE('A','A','A') FROM DUAL

After changing the views definitions the application started to work correctly with Oracle JDBC driver version 9.

The Morale
There's several things I have learnt with this issue:
1) always verify the view definition metadata in Oracle.
2) Oracle JDBC drivers are broken and behave oddly.
3) Oracle TO_CHAR function has some undocumented behavior.