Hello all!
Even after extensive googling and looking through the docs I fail
to fetch NCLOBs from an Oracle 9.2i database where the national
character set is unicode.
I believe it has been done before, but I could not find anything ...
I would be grateful for any pointers.
NCLOBs work like CLOBs when the national character set is not
unicode, even with the old DBI 1.21 and DBD::Oracle 1.12 on an
Oracle 8 client. NVarchar2 and NChar work OK even with unicode.
Setting NSL_LANG to AMERICAN_AMERICA.UTF8 or .AL32UTF8 does not
help, as I suspected.
I tried using Oracle 9.2i (the DB is using 9.2i) on a Debian
Linux box. I upgraded DBI to 1.37 and DBD::Oracle to 1.14 (the
newest versions according to CPAN). I got zero errors on make
test with both. Perl is Debian's normal "perl5 (revision 5.0
version 6 subversion 1)".
A simple
select memo from unicode_test
(memo being the NCLOB field) fails (again, only with the national
charset being unicode).
The relevant code snippet (RaiseError being set, of course):
| $| = 1;
| print "DBI: $DBI::VERSION\n",
| "DBD::Oracle $DBD::Oracle::VERSION\n";
| my $sth = $dbh->prepare("select memo from unicode_test");
| $sth->execute();
|
| while ( my ($memo) = $sth->fetchrow_array() ) {
| print Dumper $memo;
| }
| exit;
The output:
| DBI: 1.37
| DBD::Oracle 1.14
| $VAR1 = '';
| $VAR1 = '';
| DBD::Oracle::st fetchrow_array failed: ORA-24806: LOB form mismatch (DBD ERROR:
OCILobRead) [for statement ``select memo from unicode_test'' with params: ]) at
nclobtest.pl line 77.
| DBD::Oracle::st fetchrow_array failed: ORA-24806: LOB form mismatch (DBD ERROR:
OCILobRead) [for statement ``select memo from unicode_test'' with params: ]) at
nclobtest.pl line 77.
Running with tracelevel 3:
[...]
| dbd_st_prepare'd sql SELECT
| dbd_describe SELECT (EXPLICIT, lb 99999999)...
| fbh 1: 'MEMO' NO null , otype 112->112, dbsize 4000/4000, p0.s0
| dbd_describe'd 1 columns (row bytes: 4000 max, 4000 est avg, cache: 6)
| <- prepare= DBI::st=HASH(0x831643c) at nclobtest.pl line 78 via nclobtest.pl
line 60
| -> execute for DBD::Oracle::st (DBI::st=HASH(0x831643c)~0x82f8e28)
| dbd_st_execute SELECT (out0, lob0)...
| dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
| <- execute= '0E0' at nclobtest.pl line 79 via nclobtest.pl line 60
| -> fetchrow_array for DBD::Oracle::st (DBI::st=HASH(0x831643c)~0x82f8e28)
| dbd_st_fetch 1 fields...
| dbih_setup_fbav for 1 fields => 0x82f8e34
| dbd_st_fetch 1 fields SUCCESS
| OCILobRead field 2 SKIPPED: LOBlen 0, LongReadLen 99999999, BufLen 0, Got 0
| <- fetchrow_array= ( '' ) [1 items] row1 at nclobtest.pl line 81 via
nclobtest.pl line 60
| $VAR1 = '';
[EXACTLY the same "-> fetchrow_array" to "<- fetchrow_array" again]
| $VAR1 = '';
| -> fetchrow_array for DBD::Oracle::st (DBI::st=HASH(0x831643c)~0x82f8e28)
| dbd_st_fetch 1 fields...
| dbd_st_fetch 1 fields SUCCESS
| OCILobRead field 2 ERROR: LOBlen 6c, LongReadLen 99999999c, BufLen 24b, Got 6c
| !! ERROR: 24806 'ORA-24806: LOB form mismatch (DBD ERROR: OCILobRead)'
| <- fetchrow_array= ( ) [0 items] row3 at nclobtest.pl line 81 via nclobtest.pl
line 60
| 1 -> FETCH for DBD::Oracle::st (DBI::st=HASH(0x82f8e28)~INNER 'ParamValues')
| error: 24806 'ORA-24806: LOB form mismatch (DBD ERROR: OCILobRead)'
| 1 <- FETCH= HASH(0x831661c)0keys at nclobtest.pl line 81 via nclobtest.pl line 60
| DBD::Oracle::st fetchrow_array failed: ORA-24806: LOB form mismatch (DBD ERROR:
OCILobRead) [for statement ``select memo from unicode_test'' with params: ]) at
nclobtest.pl line 81.
| DBD::Oracle::st fetchrow_array failed: ORA-24806: LOB form mismatch (DBD ERROR:
OCILobRead) [for statement ``select memo from unicode_test'' with params: ]) at
nclobtest.pl line 81.
Tracelevel 9 (yes, it's a bit verbose :-/ )
[...]
| OCIDescriptorAlloc(0x831c058,0x8395228,OCI_DTYPE_LOB,0,0)
| fbh 1: 'MEMO' NO null , otype 112->112, dbsize 4000/4000, p0.s0
| OCIAttrSet(0x8335b34,OCI_HTYPE_STMT,0xbffff30c,4,11,0x832d5a4)=SUCCESS
|
OCIDefineByPos(0x8335b34,0x8395224,0x832d5a4,1,0x8395228,-1,112,0x83414e0,0x83414f0,0x8341500,0)=SUCCESS
| dbd_describe'd 1 columns (row bytes: 4000 max, 4000 est avg, cache: 6)
| <- prepare= DBI::st=HASH(0x831643c) at nclobtest.pl line 78 via nclobtest.pl
line 60
| -> execute for DBD::Oracle::st (DBI::st=HASH(0x831643c)~0x82f8e28)
[...]
| -> fetchrow_array for DBD::Oracle::st (DBI::st=HASH(0x831643c)~0x82f8e28)
| dbd_st_fetch 1 fields...
| OCIStmtFetch(0x8335b34,0x832d5a4,1,2,0)=SUCCESS
| dbih_setup_fbav for 1 fields => 0x82f8e34
| dbd_st_fetch 1 fields SUCCESS
| OCILobGetLength(0x832d530,0x832d5a4,0x832c69c,0xbffff31c)=SUCCESS
| OCILobRead field 2 SKIPPED: LOBlen 0, LongReadLen 99999999, BufLen 0, Got 0
| 0 (rc=0): ''
| <- fetchrow_array= ( '' ) [1 items] row1 at nclobtest.pl line 81 via
nclobtest.pl line 60
| $VAR1 = '';
[EXACTLY the same "-> fetchrow_array" to "<- fetchrow_array" again]
| $VAR1 = '';
| -> fetchrow_array for DBD::Oracle::st (DBI::st=HASH(0x831643c)~0x82f8e28)
| dbd_st_fetch 1 fields...
| OCIStmtFetch(0x8335b34,0x832d5a4,1,2,0)=SUCCESS
| dbd_st_fetch 1 fields SUCCESS
| OCILobGetLength(0x832d530,0x832d5a4,0x832c69c,0xbffff31c)=SUCCESS
|
OCILobRead(0x832d530,0x832d5a4,0x832c69c,0xbffff318,1,0x8394da0,24,(nil),(nil),0,1)=ERROR
| OCILobRead field 2 ERROR: LOBlen 6c, LongReadLen 99999999c, BufLen 24b, Got 6c
| OCIErrorGet(0x832d5a4,1,"<NULL>",0xbfffee88,"ORA-24806: LOB form mismatch
| ",1024,2)=SUCCESS
| OCIErrorGet after OCILobRead (er1:ok): -1, 24806: ORA-24806: LOB form mismatch
|
| OCIErrorGet(0x832d5a4,2,"<NULL>",0xbfffee88,"ORA-24806: LOB form mismatch
| ",1024,2)=NO_DATA
| 0 (rc=0): undef
| !! ERROR: 24806 'ORA-24806: LOB form mismatch (DBD ERROR: OCILobRead)'
| <- fetchrow_array= ( ) [0 items] row3 at nclobtest.pl line 81 via nclobtest.pl
line 60
[gets and prints error message]
Any ideas anyone?
-Wolfgang
