BYTE in Oracle VARCHAR2 Column definition

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 2, 2012 at 7:39 am

  #263106

  I have some Oracle Databases with Several tables that I need to load into SQL Server.

  The Database is proprietary but we own the data.

  When I script out tables using Oracle SQL Developer and all of the VARCHAR2 Columns have BYTES in the definition.

  I never saw this in any training class or Oracle Certification Exams.

  I'm creating Staging Tables in SQL Server and I was wondering what does a VARCHAR2(100 BYTE) or VARCHAR2(15 BYTE), etc translate to in SQL Server?

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Jo Pattyn

  SSC-Dedicated

  Points: 32432

  More actions

  July 2, 2012 at 12:16 pm

  #1507889

  You'll have to check the NLS-settings of the database[/url]

  In the earlier days in compares to varchar, but now the database can be fully in unicode so that varchar2 is also unicode. Safest bet is nvarchar

  To have the number of characters returned try ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  July 19, 2012 at 10:41 am

  #1514999

  Welsh Corgi (7/2/2012)

  ---

  I'm creating Staging Tables in SQL Server and I was wondering what does a VARCHAR2(100 BYTE) or VARCHAR2(15 BYTE), etc translate to in SQL Server?

  Ora11g allows to define the length of varchar2 columns either on BYTES or CHARACTERS, the slight difference is that a definition in CHARACTERS like varchar2(10 characters) would store 10 characters no matter how many bytes are needed for it, on the other hand a definition in BYTES means exactly that, the number of BYTES reserved for such a column.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.