perlquestion
ivanwillsau
<p>Hi,</p>
<p>I have a Catalyst project that talks to an Oracle database and tries to read a column from a table that is a CLOB data type. When I run the query in Catalyst I get an error ORA-24812 (which is about character code conversion). I then run the same query in a test script that just loads the Catalyst model directly runs the same query and get no error.
</p>
<p>The databse is an Oracle 10g database which has recently been changed from US7ASCII to AL32UTF8 character encoding, and it belongs to another project so I have no ability to change it in any way.</p>
<p>I ran both queries with dbi tracing turned on to level 4 (see diff below), the only (possible?) differences that I could see where the ptype of one bind params was different and a csid in the working code has 1->0->1 and the filing code has 873->0->873. I think the problem is due to Catalyst setting some character encoding setting somewhere but I can't work out what is set or how to unset it (tried using utf8 and setting NLS_LANG et al Oracle environment variables.)
</p>
<code>
--- test.trace 2011-03-08 08:41:27.000000000 +1100
+++ cat.trace 2011-03-08 08:41:28.000000000 +1100
@@ -1,6 +1,6 @@
- DBI::db=HASH(0x???????) trace level set to 0x0/4 (DBI @ 0x0/0) in DBI 1.609-ithread (pid 4136)
+ DBI 1.609-ithread default trace level set to 0x0/4 (pid 4122) at Inbox.pm line 386 via Inbox.pm line 346
-> prepare_cached for DBD::Oracle::db (DBI::db=HASH(0x???????)~0x??????? 'SELECT me.id, me.part_ref, me.image_id, me.text_id, me.binary_id, me.name, me.bin_mimetype, md_mms_repos_text.text, md_mms_repos_text.text_mime FROM md_mms_repos_part me LEFT JOIN md_mms_repos_text md_mms_repos_text ON md_mms_repos_text.text_id = me.text_id WHERE ( md_mms_repos_text.text_id = ? )' HASH(0x???????) 3) thr#???????
-1 -> prepare for DBD::Oracle::db (DBI::db=HASH(0x???????)~INNER 'SELECT me.id, me.part_ref, me.image_id, me.text_id, me.binary_id, me.name, me.bin_mimetype, md_mms_repos_text.text, md_mms_repos_text.text_mime FROM md_mms_repos_part me LEFT JOIN md_mms_repos_text md_mms_repos_text ON md_mms_repos_text.text_id = me.text_id WHERE ( md_mms_repos_text.text_id = ? )' HASH(0x???????)) thr#???????
+1 -> prepare for DBD::Oracle::db (DBI::db=HASH(0x???????)~INNER 'SELECT me.id, me.part_ref, me.image_id, me.text_id, me.binary_id, me.name, me.bin_mimetype, md_mms_repos_text.text, md_mms_repos_text.text_mime FROM md_mms_repos_part me LEFT JOIN md_mms_repos_text md_mms_repos_text ON md_mms_repos_text.text_id = me.text_id WHERE ( md_mms_repos_text.text_id = ? )' HASH(0x???????) thr#???????
dbd_preparse scanned 1 distinct placeholders
dbd_st_prepare'd sql SELECT (pl1, auto_lob1, check_sql1)
dbd_describe SELECT (EXPLICIT, lb 2000000)...
@@ -50,10 +50,10 @@
<- prepare_cached= DBI::st=HASH(0x???????) at /usr/lib/perl5/vendor_perl/5.8.8/DBIx/Class/Storage/DBI.pm line 1777 via at /usr/lib/perl5/vendor_perl/5.8.8/DBIx/Class/Storage/DBI.pm line 516
-> bind_param for DBD::Oracle::st (DBI::st=HASH(0x???????)~0x??????? 1 '152756' HASH(0x???????) thr#???????
dbd_bind_ph(): bind :p1 <== '152756' (type 0 (DEFAULT (varchar)), attribs: HASH(0x???????)
-dbd_rebind_ph_char() (1): bind :p1 <== '152756' (size 6/8/0, ptype 4(VARCHAR), otype 1 )
+dbd_rebind_ph_char() (1): bind :p1 <== '152756' (size 6/8/0, ptype 5(VARCHAR), otype 1 )
dbd_rebind_ph_char() (2): bind :p1 <== ''15275' (size 6/8, otype 1(VARCHAR), indp 0, at_exec 1)
bind :p1 as ftype 1 (VARCHAR)
-dbd_rebind_ph(): bind :p1 <== '152756' (in, not-utf8, csid 1->0->1, ftype 1 (VARCHAR), csform 0->0, maxlen 8, maxdata_size 0)
+dbd_rebind_ph(): bind :p1 <== '152756' (in, not-utf8, csid 873->0->873, ftype 1 (VARCHAR), csform 0->0, maxlen 8, maxdata_size 0)
<- bind_param= 1 at /usr/lib/perl5/vendor_perl/5.8.8/DBIx/Class/Storage/DBI.pm line 1123 via at /usr/lib/perl5/vendor_perl/5.8.8/DBIx/Class/Storage/DBI/Oracle/Generic.pm line 111
-> execute for DBD::Oracle::st (DBI::st=HASH(0x???????)~0x???????) thr#???????
dbd_st_execute SELECT (out0, lob0)...
@@ -72,7 +72,9 @@
field #6 with rc=1405(NULL)
field #7 with rc=1405(NULL)
field #8 with rc=0(OK)
- OCILobRead field 8 of 9 SUCCESS: csform 1, LOBlen 11c, LongReadLen 2000000c, BufLen 44b, Got 11c
+ OCILobRead field 8 of 9 ERROR: csform 1, LOBlen 11c, LongReadLen 2000000c, BufLen 44b, Got 0c
+ OCIErrorGet after OCILobRead (er1:ok): -1, 24812: Error while trying to retrieve text for error ORA-24812
+
field #9 with rc=0(OK)
- <- fetchrow_array= ( '182057' '1' undef '152756' undef undef undef 'Hey Buffy
-' 'text/plain' ) [9 items] row1 at /usr/lib/perl5/vendor_perl/5.8.8/DBIx/Class/Storage/DBI/Cursor.pm line 89 via at /usr/lib/perl5/vendor_perl/5.8.8/DBIx/Class/Storage/DBI.pm line 516
+ !! ERROR: '24812' 'Error while trying to retrieve text for error ORA-24812 (DBD ERROR: OCILobRead)' (err#1)
+ <- fetchrow_array= ( ) [0 items] row1 at /usr/lib/perl5/vendor_perl/5.8.8/DBIx/Class/Storage/DBI/Cursor.pm line 89 via at /usr/lib/perl5/vendor_perl/5.8.8/DBIx/Class/Storage/DBI.pm line 531
</code>
<p>Any suggestions on what might be going on would be appreciated</p>
<p>Thanks,<br/>
Ivan</p>