NHibernate 2.1.2 GA + Oracle + clob/nclob = ORA-01461

[Ex_Soft]

When I try save more than 2000 chars into clob/nclob
ORA-01461: can bind a LONG value only for insert into a LONG column
error is occured.

using (ITransaction transaction = session.BeginTransaction())
{
TestTableTypes
TestTableTypes;

string
//tmpString = new string('я', 0x0ffff);
//tmpString = new string('я', 2000);
//tmpString = new string('я', 2001);

#if !TEST_CLOB_BY_OBJECT
TestTableTypes = new TestTableTypes();
TestTableTypes.Id = 1;
TestTableTypes.FClob = tmpString;
TestTableTypes.FNClob = tmpString;
session.SaveOrUpdate(TestTableTypes);
#else
query = session.CreateQuery("FROM TestTableTypes WHERE Id = 1");
TestTableTypes = query.List<TestTableTypes>()[0];
TestTableTypes.FClob = tmpString;
TestTableTypes.FNClob = tmpString;
#endif

transaction.Commit();
}

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-
import="true">
<class name="AnyTestOracleCastle.TestTableTypes,
AnyTestOracleCastle" lazy="true">
<id name="Id">
<generator class="native" />
</id>
<property name="FVarchar2" />
<property name="FNVarchar2" />
<property name="FClob" />
<property name="FNClob" column="FNClob" type="StringClob" not-
null="false" />
</class>
</hibernate-mapping>

But tmpString = new string('я', 0x0ffff) is saved without error.

P.S.
http://www.google.com/#&q=ora-01461+can+bind+a+long+value+only+for+insert+into+a+long+column+clob

[Carlos cubas]

I encountered this a long time ago.  Give this blog post a try.

http://thebasilet.blogspot.com/2009/07/nhibernate-oracle-clobs.html


-Carlos
 
Practice makes perfect, but if no one is perfect, why practice?




> Date: Tue, 7 Dec 2010 07:40:43 -0800
> Subject: [nhusers] NHibernate 2.1.2 GA + Oracle + clob/nclob = ORA-01461
> From: inoz...@yahoo.com
> To: nhu...@googlegroups.com

> --
> You received this message because you are subscribed to the Google Groups "nhusers" group.
> To post to this group, send email to nhu...@googlegroups.com.
> To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
>

[Ex_Soft]

> I encountered this a long time ago.

U R right. I have faced with this error in NHibernate 1.2.0.3001.
BTW, in NHibernate 3.0.0 this error still exists.

[Ex_Soft]

> http://thebasilet.blogspot.com/2009/07/nhibernate-oracle-clobs.html
> ...
> In reality this is a bug with the microsoft oracle client driver.

using System.Data.OracleClient;
...

//tmpString = new string('я', 2000);
//tmpString = new string('я', 2001);

//tmpString = new string('я', 3000);
//tmpString = new string('я', 3999);
//tmpString = new string('я', 4000);
//tmpString = new string('я', 4001);

//tmpString = new string('я', 0x0ffff);

cmd.CommandType = CommandType.Text;
cmd.CommandText = "update TestTableTypes set FClob = :FClob, FNClob
= :FNClob";
cmd.Parameters.Add(":FClob", OracleType.Clob).Value = tmpString;
cmd.Parameters.Add(":FNClob", OracleType.NClob).Value = tmpString;
tmpInt=cmd.ExecuteNonQuery();

works fine.

[Fabio Maulo]

Have you tried setting the length of the property ? (int the mapping)

2010/12/8 Ex_Soft <4oth...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

--
Fabio Maulo

[Ex_Soft]

> Have you tried setting the length of the property ? (int the mapping)

No. What length should I set? I don't know string's length that I will
save.

[Carlos cubas]

The bug is not described by your code below.  Read the post carefully.

pseudo code.

IDbParameter parameter = ....
parameter.DbType = DbType.String; //No CLob-NClob equivalent type exists for the ado.net DBType
parameter.Value = "new long string..."

OracleClient incorrectly infers the type to be long.  If you tell it what the type is as you did below.

> cmd.Parameters.Add(":FClob", OracleType.Clob).Value = tmpString;
> cmd.Parameters.Add(":FNClob", OracleType.NClob).Value = tmpString;

then it wont guess and everything will be fine.

-Carlos
 
Practice makes perfect, but if no one is perfect, why practice?

> Date: Tue, 7 Dec 2010 23:22:09 -0800
> Subject: [nhusers] Re: NHibernate 2.1.2 GA + Oracle + clob/nclob = ORA-01461
> From: 4oth...@gmail.com
> To: nhu...@googlegroups.com

[Ex_Soft]

> The bug is not described by your code below.  Read the post carefully.

I'm so sorry... :(

//tmpString = new string('я', 2000);

tmpString = new string('я', 2001);
//tmpString = new string('я', 3000);
//tmpString = new string('я', 3999);
//tmpString = new string('я', 4000);
//tmpString = new string('я', 4001);
//tmpString = new string('я', 0x0ffff);

cmd.CommandType = CommandType.Text;
cmd.CommandText = "update TestTableTypes set FClob = :FClob, FNClob
= :FNClob";

OracleParameter
p;

p = new OracleParameter();
p.ParameterName = ":FClob";
p.DbType = DbType.String;
p.Value = tmpString;
cmd.Parameters.Add(p);

p = new OracleParameter();
p.ParameterName = ":FNClob";
p.DbType = DbType.String;
p.Value = tmpString;
cmd.Parameters.Add(p);

tmpInt=cmd.ExecuteNonQuery();

shows this bug.

[Ex_Soft]

So, is there any acceptable solution of this problem?

[Fabio Maulo]

11000

--
Fabio Maulo

[Ex_Soft]

> 11000

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-
import="true">
<class name="AnyTestOracleCastle.TestTableTypes,
AnyTestOracleCastle" lazy="true">
<id name="Id">
<generator class="native" />
</id>
<property name="FVarchar2" />
<property name="FNVarchar2" />

<property name="FClob" length="11000" />

<property name="FNClob" column="FNClob" type="StringClob"

length="11000" not-null="false" />
</class>
</hibernate-mapping>

using (ITransaction transaction = session.BeginTransaction())
{
TestTableTypes
TestTableTypes;

string

tmpString = new string('я', 2001);

#if !TEST_CLOB_BY_OBJECT
TestTableTypes = new TestTableTypes();
TestTableTypes.Id = 1;
TestTableTypes.FClob = tmpString;
TestTableTypes.FNClob = tmpString;
session.SaveOrUpdate(TestTableTypes);
#else
query = session.CreateQuery("FROM TestTableTypes WHERE Id = 1");
TestTableTypes = query.List<TestTableTypes>()[0];
TestTableTypes.FClob = tmpString;
TestTableTypes.FNClob = tmpString;
#endif

transaction.Commit();
}

Error still exists :( (NHibernate 1.2.0.3001 / 2.1.2.4000 / 3.0.0.4000)

[Fabio Maulo]

which is the schema creation script generated by NH?
are you creating the schema using NH?

--
Fabio Maulo

[gor]

Try this maping:

<?xml version="1.0" encoding="utf-8" ?> 
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true"> 

    <class name="AnyTestOracleCastle.TestTableTypes AnyTestOracleCastle" lazy="true"> 
        <id name="Id"> 
            <generator class="native" /> 

        </id> 
        <property name="FVarchar2" /> 
        <property name="FNVarchar2" /> 

        <property name="FClob" length="8000" /> 

        <property name="FNClob" column="FNClob" length="8000" /> 

    </class> 
</hibernate-mapping> 

Had the same issue but with fluent nhibernate...

[Ex_Soft]

> Try this maping:
Error still exists...

[Carlos cubas]

Ex_soft was the solution proposed on the blog not satisfactory to you? if not? why not?

-Carlos
 
Practice makes perfect, but if no one is perfect, why practice?

> Date: Mon, 13 Dec 2010 03:55:34 -0800

> Subject: [nhusers] Re: NHibernate 2.1.2 GA + Oracle + clob/nclob = ORA-01461
> From: 4oth...@gmail.com
> To: nhu...@googlegroups.com
>

> > Try this maping:
> Error still exists...
>