Create table in Oracle as select from SQL Server - varchar size

Question

I am trying to create table in Oracle 11g as select from a table in SQL Server 2012 via database link.

The table in SQL Server contains column: hslakkis varchar(3)

In Oracle it created the table with the same column but different size: hslakkis varchar2(9)

This behavior causes me a lot of problems with the columns size in Oracle. How can I prevent Oracle from increasing the column size?

• Philᵀᴹ: What are the character sets on the two databases? That'll be the reason. (UTF antics)
• alonk: The Oracle use AL32UTF8 character set while the Sql Server uses cp1255 character set.

Answer 1

This is a feature that's controllable by a parameter. From the documentation:

Multi-byte Character Sets Ratio Suppression This feature optionally suppresses the ratio expansion from SQL Server database to Oracle database involving multi-byte character set. By default, Oracle gateways assume the worst ratio to prevent data being truncated or insufficient buffer size situation. However, if you have specific knowledge of your SQL Server database and do not want the expansion to occur, you can specify HS_KEEP_REMOTE_COLUMN_SIZE parameter to suppress the expansion.

Basically, Oracle is assuming that all of the data in the source table may take up 3 bytes per character, due to characterset conversion.

• Nicolas de Fontenay: Yikes. Better be aware of that and apply the parameter!