Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: ORA-01461 error with conversion from SqlSrv to oracle

Re: ORA-01461 error with conversion from SqlSrv to oracle

From: Dmitry Tolpeko <support_at_ispirer.com>
Date: Tue, 25 Jan 2005 18:13:41 +0200
Message-ID: <35n92sF4otdt3U1@individual.net>


Hello Jan,

SQL Server CHAR and VARCHAR columns can store up to 8000 characters, while Oracle CHAR stores up to 2000, VARCHAR2 stores up to 4000.

You seem to have CHAR columns greater than 2000, and you have to map them either to VARCHAR2 or CLOB. Use CLOB if you really have large character data since it has more limitations than VARCHAR2 (you will not be able to use CLOB in ORDER BY e.g.)

Best regards, Dmitry Tolpeko
SQLWays - Database conversion software for all major databases http://www.ispirer.com

"Jan van Veldhuizen" <jan_at_van-veldhuizen.nl> wrote in message news:41f577c2$0$11589$e4fe514c_at_news.xs4all.nl...
>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 Tue Jan 25 2005 - 10:13:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US