transferring data efficiently between two databases

[kevinherring]

Hi I would like to transfer data between identical two databases using oradirect. There isnt going to be that much data (maybe 10,000 rows at most) and this is just a fast and dirty solution. What is the best way to do it?

I was hoping to populate an OracleDataTable, set the RowState of all the rows to Added, change the connection of the OracleDataTable to point at the destination database and then call .Update() on the datatable. However there are two problems, one is that the row Rowstate property is readonly, the other is that it seems that even if you change the connection of the OracleDataTable and then call update, it still seems to want to update back to the original database.

I thought of something like this, which solves the first point but not the second:

Code: Select all

        Dim cmd As New OracleCommand
        Dim dtSource As OracleDataTable
        Dim row As DataRow
        Dim dtDest As OracleDataTable

            cmd.CommandText = "TABLE"
            cmd.CommandType = CommandType.TableDirect
            cmd.Connection = connSource
            dtSource = New OracleDataTable(cmd)
            dtSource.Active = True
            dtDest = New OracleDataTable
            dtDest = dtSource.Clone
            dtDest.Clear()
            dtDest.AcceptChanges()

            For Each row In dtSource.Rows
                dtDest.Rows.Add(row.ItemArray)
            Next
        dtDest.Connection = connDest
        dtDest.Update()

It needs to be able to handle varrays.

Any suggestions?
Thanks

[kevinherring]

Getting closer, I am nearly there:

Code: Select all

        Dim cmdSource As New OracleCommand
        Dim cmdDest As New OracleCommand
        Dim dtSource As OracleDataTable
        Dim row As DataRow
        Dim dtDest As OracleDataTable

            cmdSource.CommandText = "TABLE"
            cmdSource.CommandType = CommandType.TableDirect
            cmdSource.Connection = connSourceDB
            dtSource = New OracleDataTable(cmdSource)

            cmdDest.CommandText = "TABLE"
            cmdDest.CommandType = CommandType.TableDirect
            cmdDest.Connection = connDestDB
            dtDest = New OracleDataTable(cmdDest)


            dtSource.Active = True
            dtDest.Active = True

            dtDest.Clear()
            dtDest.AcceptChanges()

            For Each row In dtSource.Rows
                dtDest.Rows.Add(row.ItemArray)
            Next
            dtDest.Update()

but it would appear that it doesnt work with varrays (i get an invalid datatype on the first row when I call the .Update() command, even though when I examine the row object, they are both identical).

Also with data that doesnt contain arrays, the .Update() command is slow........ It seems to update at about 10 rows per second. hmmmm. Can anyone improve it?

[Paul]

The best way to copy records to Oracle database is OracleLoader component. It can write records to Oracle table. Please see OraDirect .NET documentation and Loader sample project for more details.
You can quickly read records from Oracle table using OracleDataReader.

[kevinherring]

thanks paul, but in the documentation, it says that it supports :
OracleDbType.VarChar
OracleDbType.Char
OracleDbType.Integer
OracleDbType.Double
OracleDbType.Date
OracleDbType.Number

but no varray

[kevinherring]

gosh, the dataloader is quite a bit faster (unsurprising I guess!), but it definately doesnt support varrays. I get the following error when i try to open the dataloader on a table containing a varray:

Additional information: Table contains field with data type that does not supported by OracleLoader.

Is this functionality due to be added anytime soon? It would be very useful for migrating data!

[Paul]

We plan to support other types in OracleLoader but we cannot specify when exactly