[cx-oracle-users] NVARCHAR2 col gives ORA-01401: inserted value too large for column

[chris d. <cdu...@ya...>]

Dear List,

I'm trying to select data from an NVARCHAR2 col then
insert it into another NVARCHAR2 col. This works some
of the time and sometimes I get:
  ORA-01401: inserted value too large for column

A specific case of this is using the Oracle 9i sample
schema
  (Create and population scripts for the one required
table are in oe_cre.sql and oe_p_d.sql in
$ORACLE_HOME/demo/schema/order_entry)

Source oe.product_descriptions.translated_name [on
database A] where product_id = 1791 and language_id =
'CS'.
Target oe.product_descriptions.translated_name [on
database B]

In the above the database column is NVARCHAR2(50) in
char i.e. 100 bytes as the NCHAR set is multibyte. Now
the byte length of translated_name for the row in
question is 52 and python gives the length of the
returned value as 52 as well. However when the insert
is done, using cx_Oracle, it fails. However using
straight SQL insert into .... select ... across a
database link it works fine, for my needs I can't use
this type of workaround.
(I'm aware this a bit of an unrealistic example but
its a simple test case that anyone with 9i server can
re-produce)

The python code is:

import cx_Oracle as cx
db1 = cx.connect("oe/test@SXL920D1")
db2 = cx.connect("oe/test@SXL920D2")
db1Cur = db1.cursor()
db2Cur = db2.cursor()

db1Cur.execute("select * from product_descriptions
where product_id = 1791 and language_id = 'CS'")

data = db1Cur.fetchall()
dict = {"arg1": data[0][0], "arg2": data[0][1],
"arg3": data[0][2], "arg4": data[0][2]}

insStr = "insert into product_descriptions
(PRODUCT_ID,LANGUAGE_ID,TRANSLATED_NAME,TRANSLATED_DESCRIPTION)
values(:arg1, :arg2, :arg3, :arg4)"

db2Cur.execute(insStr, dict)

I've tried playing around with setinputsizes and had a
good google around but with no luck. 
My environment is Suse Linux 8.2. Python 2.3.3,
cx_Oracle 4.0.1 and Oracle 9.2.0.4

Oracle NLS settings are:
NLS_CHARACTERSET  = WE8ISO8859P1
NLS_NCHAR_CHARACTERSET = AL16UTF16
NLS_LENGTH_SEMANTICS = BYTE

Any help would be much appreciated.

Thanks very much,

Chris Dunscombe


	
		
__________________________________
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/