Creating a Oracle Linked Server in Sql Server

1.    Make sure oracle client is installed on SQL Server machine
2.    How to test it is installed

image

3.    Make sure OraOLEDB.Oracle is listed under Server Objects\ Linked Servers\Provider

4.    Execute command EXEC sp_addlinkedserver ‘TEST_XYZ’, ‘Oracle’, ‘OraOLEDB.Oracle’, ‘XYZ_TCP’

a.    ‘TEST_XYZ’ – Name you give to the Linked Server
b.    ‘Oracle’ – Optional, just mention it
c.    ‘OraOLEDB.Oracle’ – Provider
d.    ‘XYZ_TCP’ is the name of the oracle database found in your tnsnames.ora  file

5.    After you create the linked server you need to associate a sql server login to a oracle login.

6.    Use EXEC sp_addlinkedsrvlogin ‘TEST_XYZ’, ‘FALSE’,’SqlServerLogin’, ‘OracleUserId’, ‘OraclePassword’
a.    ‘TEST_XYZ’ – Name of the Linked Server for which you want to create a remote login
b.    2nd Parameter I have mentioned as ‘FALSE’ since I did not want the current logged in user to be associated with the remote login, but wanted to have a different user in this case ‘SqlServerLogin’ to be associated. I had currently logged in using NT authentication which had amin privilages.
c.    ‘SqlServerLogin’ – SQL server login (Note: if you mention NULL then all sql server user will get access)
d.    ‘OracleUserId’ – Oracle UserId which is used to connect to oracle database
e.    ‘OraclePassword’ – Password for Oracle User

7.    How to query oracle database – select * from TEST_XYZ..OracleUserId.TABLE_NAME

8.    To disassociate the sql server user to a oracle user use EXEC sp_droplinkedsrvlogin ‘TEST_DDWT’, ‘SqlServerLogin’ (Local Login when you execute sp_helplinkedsrvlogin)

9.    To get a list of user access to linked server, use sp_helplinkedsrvlogin

Insert BLOB to Oracle and SQL server using ODP.net, system.data.oracleclient and system.data.sqlclient

Recently I had to write 3 test applications to insert a file (blob) into oracle and Sql server using ODP.net (Oracle.DataAccess.Client), Microsoft’s Oracle Client (System.Data.OracleClient) and Microsoft’s Sql Client (System.Data.SqlClient) the object was to do some performance testing. Below are the 3 classes I wrote

 Using ODP.Net

Imports Oracle.DataAccess.Client

Imports System.IO

Imports System.Configuration

 Public Class InsertBlobUsingOdpDotNet

     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim StartTime As DateTime = Now

        InsertBlob()

        Dim endTime As DateTime = Now

        StatusLabel.Text = “Start: ” & StartTime & ” End: ” & endTime

    End Sub

     Public Sub InsertBlob()

        Dim conn As New OracleConnection(“Data Source=” & ServerTextBox.Text & “;User ID=” & UidTestBox.Text & “;Password=” & PwdTextBox.Text)  

    Dim filePath As String

        Dim bigData As Byte() = Nothing

        Dim t As Date

         t = Now

         filePath = TerminatedApplicationFileLocation.Text

        If Not File.Exists(filePath) Then

            Throw New Exception(“file does not exist”)

        End If

        Dim fs As Stream = _

                    File.OpenRead(filePath)

        Dim tempBuff(fs.Length) As Byte

        fs.Read(tempBuff, 0, fs.Length)

        fs.Close()

        conn.Open()

        Dim tx As OracleTransaction

        tx = conn.BeginTransaction()

        Dim p1 As OracleParameter = New OracleParameter(“p_application_no_in”, OracleDbType.Int32, ApplicationNoTextBox.Text, ParameterDirection.Input)

        Dim p2 As OracleParameter = New OracleParameter(“p_FILE_BLOB_in”, OracleDbType.Blob, tempBuff.Length, tempBuff, ParameterDirection.Input)

        Dim cmd As New OracleCommand()

        cmd = conn.CreateCommand()

        cmd.Parameters.Clear()

        cmd.CommandText = “OOD.OOD_PKG.UPDATE_APPLICATION”

        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add(p1)

        cmd.Parameters.Add(p2)

        Try

            cmd.ExecuteNonQuery()

            tx.Commit()

        Catch myex As Exception

            tx.Rollback()

            MsgBox(myex.Message)

        End Try

    End Sub

End Class

Using Microsoft.Net OracleClient

Imports System.Data.OracleClient

Imports System.IO

Imports System.Configuration

 Public Class InsertUsingMicrosoftOracleClient

     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim StartTime As DateTime = Now

        InsertBlob()

        Dim endTime As DateTime = Now

        StatusLabel.Text = “Start: ” & StartTime & ” End: ” & endTime

    End Sub

     Public Sub InsertBlob()

        Dim conn As New OracleConnection(“server=” & ServerTextBox.Text & “;Uid=” & UidTestBox.Text & “;pwd=” & PwdTextBox.Text)

        Dim filePath As String

        Dim bigData As Byte() = Nothing

        Dim t As Date

         t = Now

         filePath = TerminatedApplicationFileLocation.Text

        If Not File.Exists(filePath) Then

            Throw New Exception(“file does not exist”)

        End If

         Dim fs As Stream = _

                    File.OpenRead(filePath)

        Dim tempBuff(fs.Length) As Byte

         fs.Read(tempBuff, 0, fs.Length)

        fs.Close()

        conn.Open()

         Dim tx As OracleTransaction

        tx = conn.BeginTransaction()

         Dim cmd As New OracleCommand()

        cmd = conn.CreateCommand()

         cmd.Transaction = tx

         cmd.CommandText = “declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;”

        cmd.Parameters.Add(New OracleParameter(“tempblob”, OracleType.Blob)).Direction = ParameterDirection.Output

        cmd.ExecuteNonQuery()

         Dim tempLob As OracleLob

        tempLob = cmd.Parameters(0).Value

        tempLob.BeginBatch(OracleLobOpenMode.ReadWrite)

        tempLob.Write(tempBuff, 0, tempBuff.Length)

        tempLob.EndBatch()

         cmd.Parameters.Clear()

        cmd.CommandText = “OOD.OOD_PKG.UPDATE_APPLICATION”

        cmd.CommandType = CommandType.StoredProcedure

        Dim parmAppNo = New OracleParameter(“p_application_no_in”, OracleType.Int32)

        parmAppNo.Direction = ParameterDirection.Input

        parmAppNo.Value = ApplicationNoTextBox.Text

        cmd.Parameters.Add(parmAppNo)

        Dim pBlob = New OracleParameter(“p_FILE_BLOB_in”, OracleType.Blob)

        pBlob.Value = tempLob

        cmd.Parameters.Add(pBlob)

        Try

            cmd.ExecuteNonQuery()

        Catch myex As Exception

            MsgBox(myex.Message)

        End Try

        tx.Commit()

     End Sub

End Class

Using SqlClient

Imports System.Data.SqlClient

Imports System.IO

 Public Class InsertBlobUsingSqlClient

     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim StartTime As DateTime = Now

        InsertBlob()

        Dim endTime As DateTime = Now

        StatusLabel.Text = “Start: ” & StartTime & ” End: ” & endTime

    End Sub

    Public Sub InsertBlob()

Dim conn As New SqlConnection(“Data Source=” & ServerTextBox.Text & “;Initial Catalog=” & DbTextBox.Text & “;Persist Security Info=True;User Id=” & UidTestBox.Text & “;Password=” & PwdTextBox.Text)

        Dim filePath As String

         Dim t As Date

         t = Now

         filePath = TerminatedApplicationFileLocation.Text

        If Not File.Exists(filePath) Then

            Throw New Exception(“file does not exist”)

        End If

         Dim fs As Stream = _

                    File.OpenRead(filePath)

        Dim tempBuff(fs.Length) As Byte

        fs.Read(tempBuff, 0, fs.Length)

        fs.Close()

        conn.Open()

         Dim tx As SqlTransaction

        tx = conn.BeginTransaction()

         Dim cmd As New SqlCommand()

        cmd = conn.CreateCommand()

         cmd.Transaction = tx

         cmd.Parameters.Clear()

        cmd.CommandText = “UPDATE_APPLICATION”

        cmd.CommandType = CommandType.StoredProcedure

        Dim parmAppNo = New SqlParameter(“@p_application_no_in”, SqlDbType.BigInt)

        parmAppNo.Direction = ParameterDirection.Input

        parmAppNo.Value = ApplicationNoTextBox.Text

        cmd.Parameters.Add(parmAppNo)

        Dim pBlob = New SqlParameter(“@p_FILE_BLOB_in”, SqlDbType.VarBinary)

        pBlob.Value = tempBuff

        cmd.Parameters.Add(pBlob)

        Try

            cmd.ExecuteNonQuery()

        Catch myex As Exception

            MsgBox(myex.Message)

        End Try

        tx.Commit()

     End Sub

End Class

 I found Blob insert to Oracle was really slow, So I had to write a program to speed up the process, I found by chunking up the buffer It really speeded up the process (Using ODP.Net)

 Public Sub ChunkInsertBlob()

     Dim conn As New OracleConnection(“Data Source=” & ServerTextBox.Text & “;User ID=” & UidTestBox.Text & “;Password=” & PwdTextBox.Text)  

      Dim filePath As String

        Dim bigData As Byte() = Nothing

        Dim t As Date

        Dim chunkSize As Integer = BufferTextBox.Text

         t = Now

         filePath = TerminatedApplicationFileLocation.Text

         If Not File.Exists(filePath) Then

            Throw New Exception(“file does not exist”)

        End If

         Dim fs As Stream = _

                    File.OpenRead(filePath)

        Dim tempBuff(fs.Length) As Byte

         fs.Read(tempBuff, 0, fs.Length)

        fs.Close()

        conn.Open()

         Dim tx As OracleTransaction

        tx = conn.BeginTransaction()

         Dim p1 As OracleParameter = New OracleParameter(“p_application_no_in”, OracleDbType.Int32, ApplicationNoTextBox.Text, ParameterDirection.Input)

        Dim p2 As OracleParameter = New OracleParameter(“p_FILE_BLOB_in”, OracleDbType.Blob, ParameterDirection.Input)

         Dim myBlob As New OracleBlob(conn)

         Dim startOffset As Integer = 0

        Dim writeBytes As Integer = 0

         ‘ loading the buffer in small chunks to Oracle

        myBlob.BeginChunkWrite()

        Do

            writeBytes = If(startOffset + chunkSize > tempBuff.Length, tempBuff.Length – startOffset, chunkSize)

            myBlob.Write(tempBuff, startOffset, writeBytes)

            startOffset += writeBytes

        Loop While startOffset < tempBuff.Length

        myBlob.EndChunkWrite()

        p2.Value = myBlob

        Dim cmd As New OracleCommand()

        cmd = conn.CreateCommand()

        cmd.Parameters.Clear()

        cmd.CommandText = ” OOD.OOD_PKG.UPDATE_APPLICATION ”

        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add(p1)

        cmd.Parameters.Add(p2)

        Try

            cmd.ExecuteNonQuery()

            tx.Commit()

        Catch myex As Exception

            tx.Rollback()

            MsgBox(myex.Message)

        End Try

    End Sub