Subject: DBD::Oracle and unicode-NCLOBs leads to ORA-24806: LOB form mismatch (DBD ERROR: OCILobRead)

From: "Wolfgang Weisselberg" <[email-masked]>
Date: Mon, 21 Jul 2003 21:10:53 +0200

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