Problem with binding Oracle-BLOB to SQL-Parameters

[Mirco]

Hi,

First of all, I am a newbie to .NET and Oracle.
I would appreciate any help or hints which lead me in the right
direction.

I am building a Database converter. Basically what it should do, is
take a table (schema and content) from MS-SQL server and transfer it
to an Oracle 10g Database.
Creating a table in Oracle is not a problem.
In MS-SQL I have a column of type "TEXT" which I map to a "BLOB" on
the Oracle side.
Now in .NET I use parameterized SQL queries to insert data in Oracle
like this:
statement = "INSERT INTO scott.test1 (" +
"DWDOCID, " +
"DWPAGECOUNT, " +
"DWSTOREDATE, " +
"DWSTORETIME, " +
"KEYWORDS, " +
"COMMENT" +
") VALUES (" +
"WDOCID, " +
"WPAGECOUNT, " +
"WSTOREDATE, " +
"WSTORETIME, " +
":KEYWORDS, " +
":COMMENT" +
")";

A reader reads through the source table (MS-SQL), gets a row and
populates the SQL-Parameters for each column as follows:
while(reader.Read())
{
OracleCommand cmd = new OracleCommand(statement, connection);
cmd.Parameters.Add("DWDOCID",OracleDbType.Varchar2); cmd.Parameters.Add("DWPAGECOUNT",OracleDbType.Int32); cmd.Parameters.Add("DWSTOREDATE",OracleDbType.Varchar2); cmd.Parameters.Add("DWSTORETIME",OracleDbType.Varchar2); cmd.Parameters.Add("KEYWORDS",OracleDbType.Varchar2);
cmd.Parameters.Add("COMMENT",OracleDbType.Blob);
cmd.Parameters["DWDOCID"].Value = reader["DWDOCID"];
cmd.Parameters["DWPAGECOUNT"].Value = reader["DWPAGECOUNT"];
string date = reader["DWSTOREDATE"].ToString();
Console.WriteLine("date: " + date);
cmd.Parameters["DWSTOREDATE"].Value = "16.05.2004";
cmd.Parameters["DWSTORETIME"].Value = reader["DWSTORETIME"];
cmd.Parameters["KEYWORDS"].Value = reader["KEYWORDS"];
cmd.Parameters["COMMENT"].Value = reader["COMMENT"];
cmd.ExecuteNonQuery();
}
Now, all the simple datataypes like Varchar2 and Int32 work perfectly.
The thing which makes problem is the columns of type
"OracleDbType.Blob".
When the line "cmd.ExecuteNonQuery();" is processed I get the error

Unhandled Exception: System.ArgumentException: Ungültiges
Parameter-Binding
Parameter name: COMMENT

OK, there is a German word "Ungültiges" which is probably "Invalid" in
English.

I have used the same approach for a MySQL-Database and here I was able
to bind Binary Data to SQP-Parameters. What is the deal with Oracle
not being able to handle these parameters.

Is there another approach to get Binary data from MS-SQL to Oracle
using ADO.NET.

Please help me with this.

Greetings Mirc

 

[Frans Bouma [C# MVP]]

Hi,

First of all, I am a newbie to .NET and Oracle.
I would appreciate any help or hints which lead me in the right
direction.

I am building a Database converter. Basically what it should do, is
take a table (schema and content) from MS-SQL server and transfer it
to an Oracle 10g Database.
Creating a table in Oracle is not a problem.
In MS-SQL I have a column of type "TEXT" which I map to a "BLOB" on
the Oracle side.
Now in .NET I use parameterized SQL queries to insert data in Oracle
like this:
statement = "INSERT INTO scott.test1 (" +
"DWDOCID, " +
"DWPAGECOUNT, " +
"DWSTOREDATE, " +
"DWSTORETIME, " +
"KEYWORDS, " +
"COMMENT" +
") VALUES (" +
"WDOCID, " +
"WPAGECOUNT, " +
"WSTOREDATE, " +
"WSTORETIME, " +
":KEYWORDS, " +
":COMMENT" +
")";

A reader reads through the source table (MS-SQL), gets a row and
populates the SQL-Parameters for each column as follows:
while(reader.Read())
{
OracleCommand cmd = new OracleCommand(statement, connection);
cmd.Parameters.Add("DWDOCID",OracleDbType.Varchar2);
cmd.Parameters.Add("DWPAGECOUNT",OracleDbType.Int32);
cmd.Parameters.Add("DWSTOREDATE",OracleDbType.Varchar2);
cmd.Parameters.Add("DWSTORETIME",OracleDbType.Varchar2);
cmd.Parameters.Add("KEYWORDS",OracleDbType.Varchar2);
cmd.Parameters.Add("COMMENT",OracleDbType.Blob);
cmd.Parameters["DWDOCID"].Value = reader["DWDOCID"];
cmd.Parameters["DWPAGECOUNT"].Value = reader["DWPAGECOUNT"]; string date =
reader["DWSTOREDATE"].ToString(); Console.WriteLine("date: " + date);
cmd.Parameters["DWSTOREDATE"].Value = "16.05.2004";
cmd.Parameters["DWSTORETIME"].Value = reader["DWSTORETIME"];
cmd.Parameters["KEYWORDS"].Value = reader["KEYWORDS"];
cmd.Parameters["COMMENT"].Value = reader["COMMENT"]; cmd.ExecuteNonQuery();
} Now, all the simple datataypes like Varchar2 and Int32 work perfectly.
The thing which makes problem is the columns of type "OracleDbType.Blob".
When the line "cmd.ExecuteNonQuery();" is processed I get the error

Unhandled Exception: System.ArgumentException: Ungültiges
Parameter-Binding
Parameter name: COMMENT

OK, there is a German word "Ungültiges" which is probably "Invalid" in
English.

I have used the same approach for a MySQL-Database and here I was able
to bind Binary Data to SQP-Parameters. What is the deal with Oracle
not being able to handle these parameters.

Is there another approach to get Binary data from MS-SQL to Oracle
using ADO.NET.

TEXT is character data. You should map it to a CLOB field, not a BLOB field.
Furthermore, you don't cast the types read from the datareader. This might
cause a problem. A BLOB field has to be filled with a byte array. You
probably store a string in there with your current code.

Frans.

 

[Frans Bouma [C# MVP]]

Hi Frans,

Thank you for your advice.
You are writing "A BLOB field has to be filled with a byte array. You
probably store a string in there with your current code."
Does a CLOB field also needs to be filled with a byte array and if "yes"
how do i cast the reader object into a byte array.

No, CLOB fields can be filled with a string. Your sqlserver datareader will
contain a System.String value for the Text field, so you can put that value
directly into the CLOB parameter

Frans.