ORA-01461 while moving data from sqlserver to oracle

[Jan van Veldhuizen]

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

 

[Guest]

I would look at DTS for movement, as it is more robust than rolling your own
ETL. There are cases where I would disagree with this advice, but I do not
reinvent the wheel lightly.
---
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************
Think outside the box!
************************************

 

[Eric]

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.

Which version of Oracle? I think older versions had a limit of 2000
bytes in a VarChar2 if I'm not mistaken.

You can always use a ?LOB field for larger data, but there's limits of
how you can query the data.

That limit is higher is newer versions.

By the way, the auto-generated SQL isn't very efficient.

Eric

 

[Jan van Veldhuizen]

Which version of Oracle? I think older versions had a limit of 2000
bytes in a VarChar2 if I'm not mistaken.

Version 9

You can always use a ?LOB field for larger data, but there's limits of
how you can query the data.

That limit is higher is newer versions.

By the way, the auto-generated SQL isn't very efficient.

No problem. A conversion program is not run daily. Only once or twice per
customer, so inefficiency is not so bad in that case.