Re: [cx-oracle-users] ORA-01461 when inserting into VARCHAR2 column

[Rodney B. <ba...@pl...>]

> I'm finding cases where inserting a string (via bind variable) with
> length > 2000 into a VARCHAR2 column fails with "ORA-01461: can bind
> a LONG value only for insert into a LONG column".
>
> In my test case, this happens when the first insert uses a unicode
> bind variable (not necessarily > 2000 in length).  Subsequent inserts
>  using either str or unicode bind variables > 2000 in length fail
> with ORA-01461.
>
> If the first insert uses a str bind variable, subsequent inserts
> succeed with either str or unicode.

After further investigation, there seem to be two things happening here.

Firstly, when binding a unicode variable, cx_Oracle seems to use
SQLCS_NCHAR, but for a str variable, it uses SQLCS_IMPLICIT.  The national
characterset for my database is AL16UTF16, so I guess this means that Oracle
sees that 2001 characters won't fit in the 4000 byte maximum for NVARCHAR2
using UTF-16 and converts to LONG instead.

Secondly, the decision to use SQLCS_NCHAR is not revisited when the previous
statement passed to execute() is the same as the current one.  This means
that str variables can end up being treated like unicode variables and vice
versa.

I'm not using NVARCHAR2, so I don't think using SQLCS_NCHAR is ever useful
for my application; however, I guess it is useful for others.

Rodney