locked
Importing from Oracle VARCHAR2 (4000) Byte to SQL Server VARCHAR(4000) RRS feed

  • Question

  • Can I import all data coming from an Oracle database with the column set as VARCHAR2 (4000) Byte into an SQL Server 2012 database table with the column set at VARCHAR(4000). Wondering if this is the correct mapping between the two.

    Thanks,

    Jeff

    Friday, July 1, 2016 12:59 PM

Answers

  • In SQL Server you always define the amount of chars to store. In Oracle you can define cahrs or amount of Bytes, and UTF requires 1-n Bytes; so yes, with NVARCHAR(4000) you are on the safe side and your data won't be truncated.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 1, 2016 2:14 PM

All replies

  • Yes you can. you need to create linked server which needs oracle client needs to be installed in SQL server.

    I think below link will help you to install oracle client

    Connecting SQL Server and Oracle Using Linked Servers

    Please Mark me as an answer if my post helps you.

    Regards

    ChetanV




    Friday, July 1, 2016 1:05 PM
  • Chetan,

    The way I am doing it is by filling a DataSet/DataTable importing from Oracle. Then I take the Datatable and bulk copy it to a table in SQL Server. Wanted to make sure that there was no differences that would cause an overflow on the bulk copy side to SQL Server.

                Dim Dta01 As New OleDb.OleDbDataAdapter(strQueryString, CnnETMSDB)
                Dta01.SelectCommand.CommandTimeout = 120
    
                Try
    
                    Dta01.Fill(dstImpImportETMSSysRefComments, "tblImpImportETMSSysRefComments")
                    tblImpImportETMSSysRefComments = dstImpImportETMSSysRefComments.Tables("tblImpImportETMSSysRefComments")
    
                    'Controller.DataGridView1.DataSource = tblImpImportETMSSysRefComments
    
                Catch ex As Exception
    
                    Dim strExMessage As String = ex.Message
                    Dim strStackTrace As String = ex.StackTrace
    
                    Dim strMsg32 As String = strExMessage & ControlChars.CrLf & ControlChars.Lf & strStackTrace & ControlChars.CrLf & ControlChars.Lf & " - Error while Importing data"
    
                    MailLogEvent.BLL__Process__MailAndLogEvent__AddLogEntry(strMARCLId, strLogonId, 107764, dteTime_Start, 0, strMsg32, strClassRoutine)
    
                End Try
    
    
    
                        Using DestinationServer As New SqlBulkCopy(CnnIMP)
                            DestinationServer.DestinationTableName = "tblIMPORT_ETMS_SYSREF_COMMENTS"
                            DestinationServer.ColumnMappings.Add("ID", "ID")
                            DestinationServer.ColumnMappings.Add("TKTNUM", "TKTNUM")
                            DestinationServer.ColumnMappings.Add("SEQNUM", "SEQNUM")
                            DestinationServer.ColumnMappings.Add("CMTSEQNUM", "CMTSEQNUM")
                            DestinationServer.ColumnMappings.Add("VOIDCOMMENTSFLAG", "VOIDCOMMENTSFLAG")
                            DestinationServer.ColumnMappings.Add("CREATEDTTM", "CREATEDTTM")
                            DestinationServer.ColumnMappings.Add("LASTUPDDTTM", "LASTUPDDTTM")
                            DestinationServer.ColumnMappings.Add("TOPGRP_NAME", "TOPGRP_NAME")
                            DestinationServer.ColumnMappings.Add("CREATEUSERNAME", "CREATEUSERNAME")
                            DestinationServer.ColumnMappings.Add("COMMENTS", "COMMENTS")
                            DestinationServer.WriteToServer(dstImpImportETMSSysRefComments.Tables("tblImpImportETMSSysRefComments"))
                            DestinationServer.Close()
                        End Using

    Thanks,

    Jeff

    Friday, July 1, 2016 1:15 PM
  • Hello,

    Oracle VARCHAR2 stores UTF/Unicode data, so if you don't want to loose data you should store it in SQL Server as typ Nvarchar for Unicode data.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 1, 2016 1:50 PM
  • Olaf,

    Sounds good, just to be sure... Oracle VARCHAR2 (4000) Byte to SQL Server NVARCHAR(4000) would be a direct  match between the two?

    Friday, July 1, 2016 1:55 PM
  • In SQL Server you always define the amount of chars to store. In Oracle you can define cahrs or amount of Bytes, and UTF requires 1-n Bytes; so yes, with NVARCHAR(4000) you are on the safe side and your data won't be truncated.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 1, 2016 2:14 PM
  • Thanks much Olaf!
    Friday, July 1, 2016 2:20 PM
  • This is one of the conversion issues where there's a "safe" option and an "optimal" option.

    In Oracle the characters in varchar2,char columns are determined by the "Database Character Set". See Database Globalization Support Guide - Choosing a Character Set.  If the Database Character Set uses  multi-byte characters then you must map char/varchar2 to nchar/nvarchar.  SQL Server only supports single-byte characters in char/varchar.

    If the Oracle databases uses a single-byte Character Set, then you can correctly use char/varchar in SQL Server, provided you choose a collation that supports all the same characters that Oracle uses.

    So the "safe" option is to map Oracle varchar2 to SQL NVARCHAR.  You get the best compatibility and simplest conversion. 

    However if Oracle was configures with a single-byte character set compatible with your SQL Server collation (which will be the case for lots of US/European implementations), then using NCHAR/NVARCHAR in SQL Server is not necessary.  And can cause some performance degredation.

    If you move from single-bye char/varchar on Oracle to SQL Server's NCHAR/NVARCHAR _and_ you don't use ROW COMPRESSION on SQL Server then the space requirements will increase.  ROW COMPRESSION and PAGE COMPRESSION both include Unicode compression which will store Unicode with one byte-per-character.   ROW COMPRESSION is recommended for any Oracle converted table with lots of date or numeric columns too, as Oracle uses variable-width storage for these types, as does SQL Server's ROW COMPRESSION.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, July 1, 2016 3:36 PM