ORA-01461 error with conversion from SqlSrv to oracle

I worte a conversion program which moves all data from SqlSrv to Oracle, using the OracleClient and SqlClient classes of .Net.

Now when a column has 2001 characters or more I get this error. I've read a lot of it, but I do not fully understand what is wrong, and more important, how it can be solved.

I know a lot about SqlServer, but very little about Oracle.

I am using a standard SqlDataAdapter, and a OracleDataAdapter. I am using the OracleCommandBuilder to generate the Oracle insertcommand. The conversion method is very straightforward and simple: I open a SqlDataAdapter with a 'Select * from Table' command, and an OracleDataAdapter with the same command. I fill two Datatables using this two adapters, which results in a filled datatable from the sqlserver db, and an empty datatable from the oracle db.
Then I simply move all data from the first table to the second, and then I execute the Update methode of the OracleDataAdpater. As said, this is working properly for 99,9% of my database. Only strings with a length of >2000 will fail with the Oracle ORA-01461 exception....:-(

Dim strSelect = "selectr * from test"
Dim sqlC as new SqlConnection(sqlConnectionString) Dim sqlDa as new SqlDataAdapter(strSelect, sqlC) Dim oraC as new OracleConnection(oraConnectionString) Dim oraDa as new OracleDataAdapter(strSelect, oraC) Dim oraCB as new OracleCommandBuilder(oraDa)

Dim sqlDt as new Datatable
sqlDa.Fill(sqlDt)
Dim oraDy as new DataTable
oraDa.Fill(oraDt)

Dim oraRow as Datarow
For Each sqlRow as Datarow in sqlDt.Rows

   oraRow = oraDt.NewRow
   For i as Integer = 0 To sqlDt.Columns.Count - 1

      oraRow(i) = sqlRow(i)
   next
   oraDt.Rows.Add(oraRow)
Next

oraDa.Update(oraDt)

Currently I have added some extra lines in the inner loop to handle string data (using sqlDt.Columns(i).DataType) and to substring too long strings to a length of 2000 maximum. That's only way I can have this routine error free....:-((

Can anyone help me?

Jan van Veldhuizen Received on Mon Jan 24 2005 - 16:34:33 CST