Hi,
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.
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.
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.)
--- 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.binar +y_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 ( m +d_mms_repos_text.text_id = ? )' HASH(0x???????) 3) thr#??????? -1 -> prepare for DBD::Oracle::db (DBI::db=HASH(0x???????)~INNER 'SE +LECT me.id, me.part_ref, me.image_id, me.text_id, me.binary_id, me.na +me, me.bin_mimetype, md_mms_repos_text.text, md_mms_repos_text.text_m +ime FROM md_mms_repos_part me LEFT JOIN md_mms_repos_text md_mms_repo +s_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 'SE +LECT me.id, me.part_ref, me.image_id, me.text_id, me.binary_id, me.na +me, me.bin_mimetype, md_mms_repos_text.text, md_mms_repos_text.text_m +ime FROM md_mms_repos_part me LEFT JOIN md_mms_repos_text md_mms_repo +s_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/vend +or_perl/5.8.8/DBIx/Class/Storage/DBI.pm line 1777 via at /usr/lib/pe +rl5/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)), att +ribs: 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(VA +RCHAR), indp 0, at_exec 1) bind :p1 as ftype 1 (VARCHAR) -dbd_rebind_ph(): bind :p1 <== '152756' (in, not-utf8, csid 1->0->1, f +type 1 (VARCHAR), csform 0->0, maxlen 8, maxdata_size 0) +dbd_rebind_ph(): bind :p1 <== '152756' (in, not-utf8, csid 873->0->87 +3, 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/S +torage/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, LongRe +adLen 2000000c, BufLen 44b, Got 11c + OCILobRead field 8 of 9 ERROR: csform 1, LOBlen 11c, LongRead +Len 2000000c, BufLen 44b, Got 0c + OCIErrorGet after OCILobRead (er1:ok): -1, 24812: Error while try +ing to retrieve text for error ORA-24812 + field #9 with rc=0(OK) - <- fetchrow_array= ( '182057' '1' undef '152756' undef undef unde +f 'Hey Buffy -' 'text/plain' ) [9 items] row1 at /usr/lib/perl5/vendor_perl/5.8.8/D +BIx/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_pe +rl/5.8.8/DBIx/Class/Storage/DBI/Cursor.pm line 89 via at /usr/lib/pe +rl5/vendor_perl/5.8.8/DBIx/Class/Storage/DBI.pm line 531
Any suggestions on what might be going on would be appreciated
Thanks,
Ivan
In reply to I get an error using a DBIx::Class model in Catalyst but not when it is standalone by ivanwillsau
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |