how to insert NCLOB value with System.Data.OracleClient

[Andy Fish]

Hi,

I am trying to use an OracleParameter object to insert a value into an NCLOB
column - the value is stored as a string variable in c# and contains
non-ansi characters.

from what I can understand, I need to set the OracleType property which also
sets the DbType property in the superclass

if I set the OracleType to be nvarchar, some values seem to insert Ok but
some others give this error:

ORA-01461: can bind a LONG value only for insert into a LONG column

it doesn't seem completely dependent on the length of the data but I presume
this is the underlying problem.

so I have tried setting OracleType = OracleType.NClob. however, this gives

ORA-01084: invalid argument in OCI call

any idea how what I am doing wrong? FWIW i'm connected to 10g rel 10.2.0.1.0
and my code looks like this:

IDbConnection conn = new OracleConnection();
conn.ConnectionString = "...";
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO xxx (xxx) VALUES (xxx, :1, xxx )";
IDbDataParameter param = cmd.CreateParameter();
param.ParameterName = ":1";
param.Value = "xxx";
((OracleParameter)param).OracleType = OracleType.NClob;
param.Direction = ParameterDirection.Input;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();

Thanks

Andy

 

[Paul Clement]

¤ Hi,
¤
¤ I am trying to use an OracleParameter object to insert a value into an NCLOB
¤ column - the value is stored as a string variable in c# and contains
¤ non-ansi characters.
¤
¤ from what I can understand, I need to set the OracleType property which also
¤ sets the DbType property in the superclass
¤
¤ if I set the OracleType to be nvarchar, some values seem to insert Ok but
¤ some others give this error:
¤
¤ ORA-01461: can bind a LONG value only for insert into a LONG column
¤
¤ it doesn't seem completely dependent on the length of the data but I presume
¤ this is the underlying problem.
¤
¤ so I have tried setting OracleType = OracleType.NClob. however, this gives
¤
¤ ORA-01084: invalid argument in OCI call
¤
¤ any idea how what I am doing wrong? FWIW i'm connected to 10g rel 10.2.0.1.0
¤ and my code looks like this:
¤
¤ IDbConnection conn = new OracleConnection();
¤ conn.ConnectionString = "...";
¤ conn.Open();
¤ IDbCommand cmd = conn.CreateCommand();
¤ cmd.CommandText = "INSERT INTO xxx (xxx) VALUES (xxx, :1, xxx )";
¤ IDbDataParameter param = cmd.CreateParameter();
¤ param.ParameterName = ":1";
¤ param.Value = "xxx";
¤ ((OracleParameter)param).OracleType = OracleType.NClob;
¤ param.Direction = ParameterDirection.Input;
¤ cmd.Parameters.Add(param);
¤ cmd.ExecuteNonQuery();

Take a look at the following article:

Working with Oracle LOBs
http://msdn.microsoft.com/en-us/library/cydxhzhz(VS.80).aspx


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Andy Fish]

Take a look at the following article:

Working with Oracle LOBs
http://msdn.microsoft.com/en-us/library/cydxhzhz(VS.80).aspx

thanks for that - great article

in the end my problem turned out to be a bit spurious as it was caused by an
empty string in one of the bound parameters. When I was originally using
nvarchar for the data type, empty strings seemed to be handled OK but not
when I switched to NClob. In other words my code works as posted.

In the end, I haven't used OracleLob objects at all - I just assigned the c#
string to the value of the OracleParameter and it seems to insert fine - I
have tested with unicode characters and data values up to 100kb or so.

Andy