Oracle Database Discussions

We are using TG to make a number of tables/views available in an Oracle database. The tables in question contain a number of nvarchar fields which are expected to contain Unicode data. These fields cause a number of problems when accessed in Oracle. Firstly, queries returning any nvarchar(x) (for some x) fields hang when they are run in Oracle and it is necessary to reset the connection before running another query! Fields with type nvarchar(max) do come through, however they come through as type LONG, which is very difficult to do anything useful with in Oracle and cannot be searched on.

We are working around the problem by creating views in MS SQL on top of our original tables/views which cast nvarchar(x) to varchar(x). We are then able to query these new views in Oracle, search on text fields etc. However, MS SQL does not properly support UTF-8 in varchar fields and the conversion results in downgrading our data to the latin-1 character set. Note casting nvarchar(x) to nvarchar(max) in MS SQL does not work, the queries still hang. We also have a problem where the length of the nvarchar field in MS SQL is longer than the max supported varchar2 length in Oracle. In this case TG will again provide a LONG converted field.

We would like to know how to get full Unicode data to come across Transparent Gateway. We would be happy to get our data in the Oracle nvarchar2 type, or varchar2 with utf8 would also be fine, ideally with clob/nclob for over-length fields. Alternatively any solution which allows us to access full Unicode data would probably be acceptable. We only need to query the data from inside one ETL package, which will store the transformed data in Oracle, so a solution which involves a cast at this stage would also be acceptable, although obviously having the data show up correctly directly through TG would be better.