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

Brought to you by: atuining

cx-oracle-users

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

From: Rodney B. <ba...@pl...> - 2011-05-26 19:17:18

> 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

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

From: Rodney B. <ba...@pl...> - 2011-05-31 17:29:44

In case the lack of response is due to the absence of any sample code,
here's a patch that adds a couple of tests to illustrate the problem.  The
test in StringVar.py succeeds while the equivalent test in uStringVar.py
fails.

Rodney

 diff -Naur cx_Oracle-5.1/test/SetupTest.sql
cx_Oracle-5.1test/test/SetupTest.sql
--- cx_Oracle-5.1/test/SetupTest.sql    2011-03-19 18:05:30.000000000 -0500
+++ cx_Oracle-5.1test/test/SetupTest.sql        2011-05-31
11:35:50.458172563 -0500
@@ -102,7 +102,7 @@

 create table cx_Oracle.TestExecuteMany (
   IntCol                number(9) not null,
-  StringCol             varchar2(100)
+  StringCol             varchar2(4000)
 ) tablespace users;

 create table cx_Oracle.TestObjects (
diff -Naur cx_Oracle-5.1/test/StringVar.py
cx_Oracle-5.1test/test/StringVar.py
--- cx_Oracle-5.1/test/StringVar.py     2011-03-19 18:05:30.000000000 -0500
+++ cx_Oracle-5.1test/test/StringVar.py 2011-05-31 12:07:49.142192794 -0500
@@ -248,6 +248,17 @@
         inString = "X" * badStringSize
         self.failUnlessRaises(ValueError, var.setvalue, 0, inString)

+    def testInsert2001(self):
+        """test inserting 2001 characters into varchar2 column"""
+        self.cursor.execute("truncate table TestExecuteMany")
+        inString = "x" * 2001
+        sql = "insert into TestExecuteMany (IntCol, StringCol) " \
+              "values (:1, :2)"
+        self.cursor.execute(sql, [1, inString])
+        self.cursor.execute("select count(*) from TestExecuteMany")
+        count, = self.cursor.fetchone()
+        self.failUnlessEqual(count, 1)
+
     def testCursorDescription(self):
         "test cursor description is accurate"
         self.cursor.execute("select * from TestStrings")
diff -Naur cx_Oracle-5.1/test/uStringVar.py
cx_Oracle-5.1test/test/uStringVar.py
--- cx_Oracle-5.1/test/uStringVar.py    2011-03-19 18:05:29.000000000 -0500
+++ cx_Oracle-5.1test/test/uStringVar.py        2011-05-31
12:08:28.850211512 -0500
@@ -228,6 +228,17 @@
         inString = u"X" * badStringSize
         self.failUnlessRaises(ValueError, var.setvalue, 0, inString)

+    def testInsert2001(self):
+        """test inserting 2001 characters into varchar2 column"""
+        self.cursor.execute("truncate table TestExecuteMany")
+        inString = u"x" * 2001
+        sql = "insert into TestExecuteMany (IntCol, StringCol) " \
+              "values (:1, :2)"
+        self.cursor.execute(sql, [1, inString])
+        self.cursor.execute("select count(*) from TestExecuteMany")
+        count, = self.cursor.fetchone()
+        self.failUnlessEqual(count, 1)
+
     def testCursorDescription(self):
         "test cursor description is accurate"
         self.cursor.execute(u"select * from TestStrings")

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

From: Anthony T. <ant...@gm...> - 2011-05-31 18:39:21

Hi Rodney,

The lack of response (on my part anyway) is due to sheer busyness. I
hope to have more time in the near future and when I get a chance to
look at this problem I'll respond to your request for help. In the
meantime I do appreciate you trying to make it easier for me!

Anthony

On Tue, May 31, 2011 at 11:29 AM, Rodney Barnett
<ba...@pl...> wrote:
> In case the lack of response is due to the absence of any sample code,
> here's a patch that adds a couple of tests to illustrate the problem.  The
> test in StringVar.py succeeds while the equivalent test in uStringVar.py
> fails.
>
> Rodney
>
>  diff -Naur cx_Oracle-5.1/test/SetupTest.sql
> cx_Oracle-5.1test/test/SetupTest.sql
> --- cx_Oracle-5.1/test/SetupTest.sql    2011-03-19 18:05:30.000000000 -0500
> +++ cx_Oracle-5.1test/test/SetupTest.sql        2011-05-31
> 11:35:50.458172563 -0500
> @@ -102,7 +102,7 @@
>
>  create table cx_Oracle.TestExecuteMany (
>   IntCol                number(9) not null,
> -  StringCol             varchar2(100)
> +  StringCol             varchar2(4000)
>  ) tablespace users;
>
>  create table cx_Oracle.TestObjects (
> diff -Naur cx_Oracle-5.1/test/StringVar.py
> cx_Oracle-5.1test/test/StringVar.py
> --- cx_Oracle-5.1/test/StringVar.py     2011-03-19 18:05:30.000000000 -0500
> +++ cx_Oracle-5.1test/test/StringVar.py 2011-05-31 12:07:49.142192794 -0500
> @@ -248,6 +248,17 @@
>         inString = "X" * badStringSize
>         self.failUnlessRaises(ValueError, var.setvalue, 0, inString)
>
> +    def testInsert2001(self):
> +        """test inserting 2001 characters into varchar2 column"""
> +        self.cursor.execute("truncate table TestExecuteMany")
> +        inString = "x" * 2001
> +        sql = "insert into TestExecuteMany (IntCol, StringCol) " \
> +              "values (:1, :2)"
> +        self.cursor.execute(sql, [1, inString])
> +        self.cursor.execute("select count(*) from TestExecuteMany")
> +        count, = self.cursor.fetchone()
> +        self.failUnlessEqual(count, 1)
> +
>     def testCursorDescription(self):
>         "test cursor description is accurate"
>         self.cursor.execute("select * from TestStrings")
> diff -Naur cx_Oracle-5.1/test/uStringVar.py
> cx_Oracle-5.1test/test/uStringVar.py
> --- cx_Oracle-5.1/test/uStringVar.py    2011-03-19 18:05:29.000000000 -0500
> +++ cx_Oracle-5.1test/test/uStringVar.py        2011-05-31
> 12:08:28.850211512 -0500
> @@ -228,6 +228,17 @@
>         inString = u"X" * badStringSize
>         self.failUnlessRaises(ValueError, var.setvalue, 0, inString)
>
> +    def testInsert2001(self):
> +        """test inserting 2001 characters into varchar2 column"""
> +        self.cursor.execute("truncate table TestExecuteMany")
> +        inString = u"x" * 2001
> +        sql = "insert into TestExecuteMany (IntCol, StringCol) " \
> +              "values (:1, :2)"
> +        self.cursor.execute(sql, [1, inString])
> +        self.cursor.execute("select count(*) from TestExecuteMany")
> +        count, = self.cursor.fetchone()
> +        self.failUnlessEqual(count, 1)
> +
>     def testCursorDescription(self):
>         "test cursor description is accurate"
>         self.cursor.execute(u"select * from TestStrings")
>
>
>
>
> ------------------------------------------------------------------------------
> Simplify data backup and recovery for your virtual environment with vRanger.
> Installation's a snap, and flexible recovery options mean your data is safe,
> secure and there when you need it. Data protection magic?
> Nope - It's vRanger. Get your free trial download today.
> http://p.sf.net/sfu/quest-sfdev2dev
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

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

From: Rodney B. <ba...@pl...> - 2011-05-31 19:40:06

Thanks for the reply, Anthony.  I'll let this sit for a while.

Rodney

-----Original Message-----
From: Anthony Tuininga [mailto:ant...@gm...] 
Sent: Tuesday, May 31, 2011 1:39 PM
To: cx-...@li...
Subject: Re: [cx-oracle-users] ORA-01461 when inserting into VARCHAR2 column

Hi Rodney,

The lack of response (on my part anyway) is due to sheer busyness. I
hope to have more time in the near future and when I get a chance to
look at this problem I'll respond to your request for help. In the
meantime I do appreciate you trying to make it easier for me!

Anthony

On Tue, May 31, 2011 at 11:29 AM, Rodney Barnett
<ba...@pl...> wrote:
> In case the lack of response is due to the absence of any sample code,
> here's a patch that adds a couple of tests to illustrate the problem.  The
> test in StringVar.py succeeds while the equivalent test in uStringVar.py
> fails.
>
> Rodney
>
>  diff -Naur cx_Oracle-5.1/test/SetupTest.sql
> cx_Oracle-5.1test/test/SetupTest.sql
> --- cx_Oracle-5.1/test/SetupTest.sql    2011-03-19 18:05:30.000000000
-0500
> +++ cx_Oracle-5.1test/test/SetupTest.sql        2011-05-31
> 11:35:50.458172563 -0500
> @@ -102,7 +102,7 @@
>
>  create table cx_Oracle.TestExecuteMany (
>   IntCol                number(9) not null,
> -  StringCol             varchar2(100)
> +  StringCol             varchar2(4000)
>  ) tablespace users;
>
>  create table cx_Oracle.TestObjects (
> diff -Naur cx_Oracle-5.1/test/StringVar.py
> cx_Oracle-5.1test/test/StringVar.py
> --- cx_Oracle-5.1/test/StringVar.py     2011-03-19 18:05:30.000000000
-0500
> +++ cx_Oracle-5.1test/test/StringVar.py 2011-05-31 12:07:49.142192794
-0500
> @@ -248,6 +248,17 @@
>         inString = "X" * badStringSize
>         self.failUnlessRaises(ValueError, var.setvalue, 0, inString)
>
> +    def testInsert2001(self):
> +        """test inserting 2001 characters into varchar2 column"""
> +        self.cursor.execute("truncate table TestExecuteMany")
> +        inString = "x" * 2001
> +        sql = "insert into TestExecuteMany (IntCol, StringCol) " \
> +              "values (:1, :2)"
> +        self.cursor.execute(sql, [1, inString])
> +        self.cursor.execute("select count(*) from TestExecuteMany")
> +        count, = self.cursor.fetchone()
> +        self.failUnlessEqual(count, 1)
> +
>     def testCursorDescription(self):
>         "test cursor description is accurate"
>         self.cursor.execute("select * from TestStrings")
> diff -Naur cx_Oracle-5.1/test/uStringVar.py
> cx_Oracle-5.1test/test/uStringVar.py
> --- cx_Oracle-5.1/test/uStringVar.py    2011-03-19 18:05:29.000000000
-0500
> +++ cx_Oracle-5.1test/test/uStringVar.py        2011-05-31
> 12:08:28.850211512 -0500
> @@ -228,6 +228,17 @@
>         inString = u"X" * badStringSize
>         self.failUnlessRaises(ValueError, var.setvalue, 0, inString)
>
> +    def testInsert2001(self):
> +        """test inserting 2001 characters into varchar2 column"""
> +        self.cursor.execute("truncate table TestExecuteMany")
> +        inString = u"x" * 2001
> +        sql = "insert into TestExecuteMany (IntCol, StringCol) " \
> +              "values (:1, :2)"
> +        self.cursor.execute(sql, [1, inString])
> +        self.cursor.execute("select count(*) from TestExecuteMany")
> +        count, = self.cursor.fetchone()
> +        self.failUnlessEqual(count, 1)
> +
>     def testCursorDescription(self):
>         "test cursor description is accurate"
>         self.cursor.execute(u"select * from TestStrings")
>
>
>
>
>
----------------------------------------------------------------------------
--
> Simplify data backup and recovery for your virtual environment with
vRanger.
> Installation's a snap, and flexible recovery options mean your data is
safe,
> secure and there when you need it. Data protection magic?
> Nope - It's vRanger. Get your free trial download today.
> http://p.sf.net/sfu/quest-sfdev2dev
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

----------------------------------------------------------------------------
--
Simplify data backup and recovery for your virtual environment with vRanger.

Installation's a snap, and flexible recovery options mean your data is safe,
secure and there when you need it. Data protection magic?
Nope - It's vRanger. Get your free trial download today. 
http://p.sf.net/sfu/quest-sfdev2dev
_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users