I hope this will be addressed in the next release.
Tim.
On Mon, Jul 21, 2003 at 09:10:53PM +0200, Wolfgang Weisselberg wrote:
> 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
