• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

Data transfer from SQL Server to Oracle database

Hi,
Can someone provide me with the advice\or post url what is the best way to transfer data
from SQL Server to Oracle database using VB.NET.
I have instaled Oracle Client 11.x which supports OracleBulkCopy but for some reason I face with the problem when i try to write data to server . The error is as follows:
"ORA-01738 missing IN keyword".
It breaks in line "copy.WriteToServer(dt)".
Now, since I could not resolve my problem I am trying to workarround the same and find different solution if possibe (even solution for the current problem where OracleBulkCopy is used would be great )
By the way I have two same tables I try to move date both on SQL Server and Oracle side.
Thank you...


(see vb code below)
Public Sub Retrieve_Records()
        Dim conn1 As New SqlConnection(My.Settings.MyCon)
        conn1.Open()

        Dim cmd1 As New System.Data.SqlClient.SqlCommand()
        cmd1.Connection = conn1
        cmd1.CommandType = CommandType.Text
        cmd1.CommandText = "select partija, sektor, saldo from IFRS_partije"
        cmd1.Parameters.Add("@datum_val", SqlDbType.NVarChar)
        cmd1.Parameters("@datum_val").Value = Me.DateTimePicker1.Value.ToString("yyyy.mm.dd")
        cmd1.CommandTimeout = 0
        Dim rdr As SqlDataReader = cmd1.ExecuteReader
        Dim dt As New DataTable("IFRS_partije")
        dt.Load(rdr)
        rdr.Close()
        conn1.Close()

        Using cn As New OracleConnection(connectStr)
            cn.Open()
            Dim cmd As New OracleCommand
            cmd.Connection = cn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "DELETE FROM POMOCNA_IFRS_NOVA"
            cmd.ExecuteNonQuery()

            Using copy As New OracleBulkCopy(connectStr)
                copy.DestinationTableName = "POMOCNA_IFRS_NOVA"
                copy.WriteToServer(dt)
            End Using
        End Using
        MessageBox.Show("Successfully inserted on Oracle!", "Status", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)
    End Sub

Open in new window

0
alsam
Asked:
alsam
  • 2
  • 2
1 Solution
 
Bob LearnedCommented:
When moving data between two different data sources, I prefer to use SSIS to do that job.  If you don't understand SSIS, there would be a learning curve involved.  That syntax error doesn't make an sense.  
0
 
alsamAuthor Commented:
thank you for your reply...
I have no exiprience with SSIS but definitelly I will go in deep after I searched the web about this topic.
Can I ask you what did you mean by telling that syntax error does not make any sense?
Do you see any "unlogic" in my vb code....For some reason I can not find out why this error message is returned....Have you ever before move data from SQL Server to Oracle using bulkcopy ... do you have any useful advice for me how maybe to solve the problem....
Anyway Thank you for your time and effort
0
 
Bob LearnedCommented:
The "IN" keyword is usually for something like this:

SELECT * FROM Production.Product WHERE ProductID IN (1, 2, 3, 4)

I don't see how that relates to the work that the OracleBulkCopy would need to do.
0
 
alsamAuthor Commented:
Ok,
I have make this working prefectly.... I had problem with grants in oracle db....
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now