Solved

Data transfer from SQL Server to Oracle database

Posted on 2010-09-09
4
511 Views
Last Modified: 2012-05-10
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
Comment
Question by:alsam
  • 2
  • 2
4 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 33643171
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
 
LVL 1

Author Comment

by:alsam
ID: 33644281
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
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 33646613
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
 
LVL 1

Author Closing Comment

by:alsam
ID: 33700065
Ok,
I have make this working prefectly.... I had problem with grants in oracle db....
Thank you
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now