Resolution:
Description:
========
When using JDBC Query Activity, we see the error, "This is not a valid SQL type : -101" when one of the columns is of type "timestamp with time zone".


Environment:
=========
ALL

Symptoms:
========
When using JDBC Query with Oracle thin(OCI) driver, if one of the columns is defined as type "timestamp with time zone", on fetching or running directly, the error "This is not a valid SQL type : -101" will be thrown.

Cause:
=====
Timestamp with time zone is an Oracle specific type and it is not defined in the JDBC Specification. When fetching the output of the SQL statement in JDBC Query Activity, internally BW will use the following JDBC API:

ResultSet rset = stmt.executeQuery ("select ******* ");
ResultSetMetaData rsmd = rset.getMetaData();
System.out.println (rsmd.getColumnType(1));

When dealing with the timestamp with time zone type, rsmd.getColumnType() will throw the error due to the type if not recognized as a valid SQL type.

Resolution:
=======
1). You can use the TIBCO driver instead. The TIBCO driver will automatically treat this type as varchar2 .

2). Since timestamp with time zone is not a standard JDBC SQL type, we can convert it to a standard type. For example, we can use to_char() to convert it to a varchar type. If you want to get the time zone offset then you could use to_char(str,'TZR') :

select to_char(birthday)
, to_char(birthday,'TZD') time_zone_daylight_info
, to_char(birthday,'TZH') time_zone_hour
, to_char(birthday,'TZM') time_zone_minute
, to_char(birthday,'TZR') time_zone_region                  
from testts