Hi,
Hoping someone can help me here as I'm really stuck with this. I have a
large amount of code which reads some XML and inserts data from the XML
into a database. One particular insert fails:
insert into document
(book,category,country,expiry,id,last_update,mnem,name,odec,route,source,tdate,ttimestamp,ttype,version)
values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
and the parameters are:
SD, HR, UK, 2017-07-08 14:10:00, XXXXXXXXXXX, 1183290197812, XX, xxxxx,
N, 534453, xxxxxxxxxx, 2007-07-01, 1183269593843, xxxxxx, 1.2.6
the error is:
DBD::Oracle::st execute failed: ORA-01843: not a valid month (DBD ERROR:
error possibly near <*> indicator at char 146 in 'insert into document
(book,category,country,expiry,id,last_update,mnem,name,odec,route,source,tdate,ttimestamp,ttype,version)
values(:p1,:p2,:p3,:<*>p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14,:p15)')
at /usr/lib/perl5/site_perl/5.8.8/DBIx/Log4perl/st.pm line 38.
There is nothing obviously wrong with p4 and 2017-07-08 14:10:00 as the
session was previously altered to set the nls_timestamp_format to
'yyyy-mm-dd hh24:mi:ss. I have even verified that by looking at
NLS_SESSION_PARAMETERS just before the execute call.
After much head scratching I discovered the problem P4 has utf8 set on
it and downgrading it makes the code work i.e. when utf8::is_utf8
returns 1 on the timestamp scalar it fails and if I do a
utf8::downgrade(scalar) on it before calling execute it works.
When I examine a DBD:Oracle (v 1.19 BTW) trace file the only difference
I can see between the one that fails:
bind :p4 <== "2017-07-08 14:10:00" (type 0)
rebinding :p4 (is-utf8, ftype 1, csid 0, csform 0, inout 0)
bind :p4 <== "2017-07-08 14:10:00" (size 19/20/0, ptype 4, otype 1)
bind :p4 <== '2017-07-08 14:10:00' (size 19/19, otype 1, indp 0,
at_exec 1)
OCIBindByName(8ef1a50,8ed3a8c,8dfdad8,":p4",3,8ece658,19,1,8ed3aa4,0,8ed3
a9c,0,0,2)=SUCCESS
OCIBindDynamic(8ef1460,8dfdad8,8ed3a68,cfece0,8ed3a68,cfefc0)=SUCCESS
rebinding :p4 with UTF8 value so setting csform=SQLCS_IMPLICIT
OCIAttrSet(8ef1460,OCI_HTYPE_BIND,bfc3a76b,0,32,8dfdad8)=SUCCESS
OCIAttrGet(8ef1460,OCI_HTYPE_BIND,8ed3a78,0,31,8dfdad8)=SUCCESS
bind :p4 <== "2017-07-08 14:10:00" (in, is-utf8, csid
873->0->873, ftype 1, csform 0->2, maxlen 19, maxdata_size 0)
OCIAttrSet(8ef1460,OCI_HTYPE_BIND,bfc3a768,0,31,8dfdad8)=SUCCESS
and the successful one:
bind :p4 <== '2017-07-08 14:10:00' (type 0)
rebinding :p4 (not-utf8, ftype 1, csid 0, csform 0, inout 0)
bind :p4 <== '2017-07-08 14:10:00' (size 19/20/0, ptype 4, otype 1)
bind :p4 <== '2017-07-08 14:10:00' (size 19/19, otype 1, indp 0,
at_exec 1)
OCIBindByName(9330bc8,9315214,923ed10,":p4",3,926b1e0,19,1,931522c,0,9315
224,0,0,2)=SUCCESS
OCIBindDynamic(93305d8,923ed10,93151f0,1c4ce0,93151f0,1c4fc0)=SUCCESS
OCIAttrGet(93305d8,OCI_HTYPE_BIND,9315200,0,31,923ed10)=SUCCESS
bind :p4 <== '2017-07-08 14:10:00' (in, not-utf8, csid
873->0->873, ftype 1, csform 0->0, maxlen 19, maxdata_size 0)
is the us-utf8 versus not-utf8.
I have tried reducing the problem to a small script but just setting the
UTF8 flag on any timestamp does not make it fail so I'm unsure where to
go now. Any ideas?
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
