sqldev-jdbc-proxy

sqldev-jdbc-proxy copied to clipboard

it looks like correct data types are not translated into SQL Developer when you are browsing a Snowflake table metadata in SQL Developer. For instance, we are getting TEXT instead of VARCHAR. In the picture below I have placed the same Snowflake table shown in SQL Developer vs Squirrel

columns are sorted and can be matched by position number. Squirrel posts it as VARCHAR (original data type ) as in Snowflake, while SQL Developer shows it as TEXT which is not Oracle neither Snowflake data type.

I can reproduce the issue by creating this table:

create table t (
  vc2_200 varchar2(200)
);

To populate the grid in SQL Developer, the following statement is executed:

SELECT column_name,
       ordinal_position,
       column_default,
       is_nullable,
       data_type,
       numeric_precision,
       numeric_scale,
       comment AS column_comment
  FROM information_schema.columns
 WHERE coalesce(?, ?, ?, 'x') IS NOT NULL
   AND table_name = ?
   AND table_schema = ?
   AND coalesce(?, ?, ?, ?, ?, 'x') IS NOT NULL
 ORDER BY ordinal_position

Why I execute this statement in a simplified in the worksheet the result looks like this:

So the data_type column in information_schema.columns provides TEXT. The DESCRIBE TABLE command provides the wanted data type.

It looks like the information_schema.columns provides the raw data and the wanted data_type as shown in the DESCRIBE TABLE command needs to be constructed manually. - I guess this can be fixed.