Bug #51595 for DBD-Oracle: bind_param () fails its function if table has more than one blob

Tue Nov 17 08:18:50 2009 HMBRAND [...] cpan.org - Ticket created

Subject:

bind_param () fails its function if table has more than one blob

--8<--- use strict; use warnings; use DBI; my $dbh = DBI->connect ("dbi:Oracle:", (split m{/}, $ENV {ORACLE_USERID}), { RaiseError => 1, PrintError => 1, ShowErrorStatement => 1, AutoCommit => 1, ChopBlanks => 1, # InhibitNULL => 9, FetchHashKeyName => "NAME_lc", }) or die "Cannot connect: $!\n"; my $dta = "The quick brown fox jumps over the lazy dog"; { print STDERR " Table with just one blob ...\n"; $dbh->do (q; create table test_foo ( i integer, bl blob );); my $sth = $dbh->prepare ("select * from test_foo"); $sth->execute; my @nm = @{$sth->{NAME_lc}}; my @tp = @{$sth->{TYPE}}; # bind expected types my $sti = $dbh->prepare ("insert into test_foo values (?, ?)"); $sti->bind_param ($_ + 1, undef, $tp[$_]) for 0 .. $#tp; $sti->execute (1, $dta); $_->finish for $sth, $sti; $dbh->do ("drop table test_foo"); } { print STDERR " Table with more than one blob ...\n"; $dbh->do (q; create table test_foo ( i integer, bl1 blob, bl2 blob, bl3 blob, bl4 blob );); my $sth = $dbh->prepare ("select * from test_foo"); $sth->execute; my @nm = @{$sth->{NAME_lc}}; my @tp = @{$sth->{TYPE}}; # bind expected types my $sti = $dbh->prepare ("insert into test_foo values (?, ?, ?, ?, ?)"); $sti->bind_param ($_ + 1, undef, $tp[$_]) for 0 .. $#tp; $sti->execute (1, $dta, $dta, $dta, $dta); $_->finish for $sth, $sti; $dbh->do ("drop table test_foo"); } -->8--- => Table with just one blob ... Table with more than one blob ... DBD::Oracle::st execute failed: ORA-04043: object test_foo does not exist (DBD ERROR: Need bind_param(..., { ora_field=>... }) attribute to identify table LOB field names) [for Statement "insert into test_foo values (?, ?, ?, ?, ?)" with ParamValues: :p1=1, :p2='The quick brown fox jumps over the lazy dog', :p3='The quick brown fox jumps over the lazy dog', :p4='The quick brown fox jumps over the lazy dog', :p5='The quick brown fox jumps over the lazy dog'] at demo2.pl line 65. DBD::Oracle::st execute failed: ORA-04043: object test_foo does not exist (DBD ERROR: Need bind_param(..., { ora_field=>... }) attribute to identify table LOB field names) [for Statement "insert into test_foo values (?, ?, ?, ?, ?)" with ParamValues: :p1=1, :p2='The quick brown fox jumps over the lazy dog', :p3='The quick brown fox jumps over the lazy dog', :p4='The quick brown fox jumps over the lazy dog', :p5='The quick brown fox jumps over the lazy dog'] at demo2.pl line 65. Exit 255

Thu Dec 31 09:57:25 2009 champoux [...] pythian.com - Correspondence added

I encountered this myself with DBD::Oracle 1.23 and have corrected it with 1.24 or the current trunk version of DBD::Oracle

If you have a chance can you see if this code base

http://svn.perl.org/modules/dbd-oracle/trunk

or if you want it in one go you can try the latest release condidate

http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.24-RC1.tar

Thu Dec 31 09:57:26 2009 The RT System itself - Status changed from 'new' to 'open'

Thu Dec 31 09:57:27 2009 champoux [...] pythian.com - Status changed from 'open' to 'resolved'

Thu Dec 31 10:30:32 2009 h.m.brand [...] xs4all.nl - Correspondence added

Subject:	Re: [rt.cpan.org #51595] bind_param () fails its function if table has more than one blob
Date:	Thu, 31 Dec 2009 16:29:54 +0100
To:	bug-DBD-Oracle [...] rt.cpan.org
From:	"H.Merijn Brand" <h.m.brand [...] xs4all.nl>

On Thu, 31 Dec 2009 09:57:26 -0500, "Pythian Remote DBA via RT" <bug-DBD-Oracle@rt.cpan.org> wrote: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=51595 > > > I encountered this myself with DBD::Oracle 1.23 and have corrected it with 1.24 > or the current trunk version of DBD::Oracle > > If you have a chance can you see if this code base > > http://svn.perl.org/modules/dbd-oracle/trunk

updated from svn $ DBD-Oracle-svn 103 > perl Makefile.PL Using DBI 1.609 (for perl 5.010001 on x86_64-linux) installed in /pro/lib/perl5/site_perl/5.10.1/x86_64-linux/auto/DBI/ Argument "6.55_02" isn't numeric in numeric ge (>=) at Makefile.PL line 61. Configuring DBD::Oracle for perl 5.010001 on linux (x86_64-linux) Remember to actually *READ* the README file! Especially if you have any problems. Installing on a linux, Ver#2.6 Using Oracle in /pro/oracle/v11.1.0 DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR) Oracle version 11.1.0.6 (11.1) Found /pro/oracle/v11.1.0/rdbms/lib/ins_rdbms.mk Found /usr/share/oracle/11.1/client64/demo.mk Using /pro/oracle/v11.1.0/rdbms/lib/ins_rdbms.mk Your LD_LIBRARY_PATH env var is set to '/pro/asql/o90G/bin:/pro/asql/o90G/lib:/pro/oracle/v11.1.0/lib' Reading /pro/oracle/v11.1.0/rdbms/lib/ins_rdbms.mk Reading /pro/oracle/v11.1.0/rdbms/lib/env_rdbms.mk WARNING: Oracle /pro/oracle/v11.1.0/rdbms/lib/ins_rdbms.mk doesn't define a 'build' rule. WARNING: I will now try to guess how to build and link DBD::Oracle for you. This kind of guess work is very error prone and Oracle-version sensitive. It is possible that it won't be supported in future versions of DBD::Oracle. *PLEASE* notify dbi-users about exactly _why_ you had to build it this way. Found header files in /pro/oracle/v11.1.0/rdbms/public. Checking for functioning wait.ph System: perl5.010001 linux pc09 2.6.27.23-0.1-default #1 smp 2009-05-26 17:02:05 -0400 x86_64 x86_64 x86_64 gnulinux Compiler: ccache cc -O2 -fPIC -fno-strict-aliasing -pipe -fstack-protector -I/pro/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 Linker: /usr/bin/ld Sysliblist: -ldl -lm -lpthread -lnsl -lirc -lipgo Oracle makefiles would have used these definitions but we override them: CC: $(COMPDIR)/bin/gcc CFLAGS: $(GFLAG) $(OPTIMIZE) $(CDEBUG) $(CCFLAGS) $(PFLAGS)\ $(SHARED_CFLAG) $(USRFLAGS) [$(GFLAG) -O3 $(CDEBUG) -m32 -trigraphs -fPIC -I/pro/oracle/v11.1.0/rdbms/demo -I/pro/oracle/v11.1.0/rdbms/public -I/pro/oracle/v11.1.0/plsql/public -I/pro/oracle/v11.1.0/network/public -DLINUX -D_GNU_SOURCE -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -DSLTS_ENABLE -DSLMXMX_ENABLE -D_REENTRANT -DNS_THREADS -D__NO_CTYPE=1 $(LPFLAGS) $(USRFLAGS)] LDFLAGS: $(LDFLAGS32) [-m32 -o $@ -L/pro/oracle/v11.1.0/rdbms//lib32/ -L/pro/oracle/v11.1.0/lib32/ -L/pro/oracle/v11.1.0/lib32/stubs/] Linking with -lclntsh -ldl -lm -lpthread -lnsl -lirc -lipgo -ldl -lm -lpthread [from $(OCISHAREDLIBS)] WARNING: If you have problems you may need to rebuild perl with threading enabled. LD_RUN_PATH=/pro/oracle/v11.1.0/lib Using DBD::Oracle 1.23. Using DBD::Oracle 1.23. Using DBI 1.609 (for perl 5.010001 on x86_64-linux) installed in /pro/lib/perl5/site_perl/5.10.1/x86_64-linux/auto/DBI/ Writing Makefile for DBD::Oracle $ make test : : /pro/bin/perl /pro/lib/perl5/5.10.1/ExtUtils/xsubpp -typemap /pro/lib/perl5/5.10.1/ExtUtils/typemap -typemap typemap Oracle.xs > Oracle.xsc && mv Oracle.xsc Oracle.c ccache cc -g -c -I/pro/oracle/v11.1.0/rdbms/public -I/pro/oracle/v11.1.0/rdbms/demo -I/pro/oracle/v11.1.0/rdbms/public -I/pro/oracle/v11.1.0/plsql/public -I/pro/oracle/v11.1.0/network/public -I/pro/lib/perl5/site_perl/5.10.1/x86_64-linux/auto/DBI -g -fPIC -fno-strict-aliasing -pipe -fstack-protector -I/pro/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -DVERSION=\"1.23\" -DXS_VERSION=\"1.23\" -fPIC "-I/pro/lib/perl5/5.10.1/x86_64-linux/CORE" -Wall -Wno-comment -DUTF8_SUPPORT -DNEW_OCI_INIT -DORA_OCI_VERSION=\"11.1.0.6\" -Wall -Wcast-align -Wpointer-arith -Wbad-function-cast -Wcast-qual Oracle.c ccache cc -g -c -I/pro/oracle/v11.1.0/rdbms/public -I/pro/oracle/v11.1.0/rdbms/demo -I/pro/oracle/v11.1.0/rdbms/public -I/pro/oracle/v11.1.0/plsql/public -I/pro/oracle/v11.1.0/network/public -I/pro/lib/perl5/site_perl/5.10.1/x86_64-linux/auto/DBI -g -fPIC -fno-strict-aliasing -pipe -fstack-protector -I/pro/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -DVERSION=\"1.23\" -DXS_VERSION=\"1.23\" -fPIC "-I/pro/lib/perl5/5.10.1/x86_64-linux/CORE" -Wall -Wno-comment -DUTF8_SUPPORT -DNEW_OCI_INIT -DORA_OCI_VERSION=\"11.1.0.6\" -Wall -Wcast-align -Wpointer-arith -Wbad-function-cast -Wcast-qual dbdimp.c dbdimp.c: In function ‘ora_db_login6’: dbdimp.c:546: warning: format ‘%d’ expects type ‘int’, but argument 8 has type ‘size_t’ dbdimp.c:553: warning: format ‘%d’ expects type ‘int’, but argument 8 has type ‘size_t’ dbdimp.c:765: warning: format ‘%p’ expects type ‘void *’, but argument 6 has type ‘long int’ dbdimp.c:793: warning: format ‘%p’ expects type ‘void *’, but argument 6 has type ‘long int’ dbdimp.c: In function ‘dbd_rebind_ph_varchar2_table’: dbdimp.c:1679: warning: format ‘%p’ expects type ‘void *’, but argument 6 has type ‘long int’ dbdimp.c:1711: warning: format ‘%p’ expects type ‘void *’, but argument 6 has type ‘long int’ dbdimp.c:1720: warning: format ‘%p’ expects type ‘void *’, but argument 6 has type ‘long int’ dbdimp.c: In function ‘dbd_rebind_ph_number_table’: dbdimp.c:2086: warning: format ‘%p’ expects type ‘void *’, but argument 6 has type ‘long int’ dbdimp.c: In function ‘dbd_rebind_ph’: dbdimp.c:2689: warning: format ‘%p’ expects type ‘void *’, but argument 6 has type ‘long int’ dbdimp.c:2721: warning: format ‘%p’ expects type ‘void *’, but argument 6 has type ‘long int’ dbdimp.c:2730: warning: cast from function call of type ‘char *’ to non-matching type ‘long int’ dbdimp.c:2730: warning: format ‘%p’ expects type ‘void *’, but argument 6 has type ‘long int’ dbdimp.c: In function ‘do_bind_array_exec’: dbdimp.c:3243: warning: format ‘%p’ expects type ‘void *’, but argument 6 has type ‘long int’ dbdimp.c:3294: warning: format ‘%p’ expects type ‘void *’, but argument 6 has type ‘long int’ : oci8.c:430:99: warning: "/*" within comment oci8.c:431:2: warning: "/*" within comment oci8.c:435:44: warning: "/*" within comment oci8.c:516:50: warning: "/*" within comment oci8.c:689:79: warning: "/*" within comment oci8.c:997:3: warning: "/*" within comment oci8.c: In function ‘dbd_rebind_ph_lob’: oci8.c:1460: warning: format ‘%p’ expects type ‘void *’, but argument 6 has type ‘long int’ oci8.c: In function ‘fetch_lob’: oci8.c:1886: warning: format ‘%lu’ expects type ‘long unsigned int’, but argument 4 has type ‘ub4’ oci8.c: In function ‘get_object’: oci8.c:2254: warning: cast from pointer to integer of different size oci8.c:2254: warning: cast from pointer to integer of different size oci8.c: In function ‘sth_set_row_cache’: oci8.c:2781: warning: format ‘%p’ expects type ‘void *’, but argument 6 has type ‘long int’ oci8.c:2791: warning: format ‘%p’ expects type ‘void *’, but argument 6 has type ‘long int’ oci8.c: In function ‘ora_describe’: oci8.c:3588: warning: format ‘%p’ expects type ‘void *’, but argument 6 has type ‘long int’ oci8.c:3090: warning: unused variable ‘has_lobs’ oci8.c: In function ‘ora_parse_uid’: oci8.c:3887: warning: format ‘%p’ expects type ‘void *’, but argument 6 has type ‘long int’ oci8.c:3891: warning: format ‘%p’ expects type ‘void *’, but argument 6 has type ‘long int’ Running Mkbootstrap for DBD::Oracle () chmod 644 Oracle.bs /pro/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/ora_explain PERL_DL_NONLAZY=1 /pro/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t t/01base.t .............. # Test loading DBI, DBD::Oracle and version t/01base.t .............. ok t/10general.t ........... ok t/12impdata.t ........... ok t/14threads.t ........... skipped: this linux perl 5.010001 not configured to support iThreads t/15nls.t ............... ok t/20select.t ............ ok t/21nchar.t ............. ok t/22nchar_al32utf8.t .... ok t/22nchar_utf8.t ........ ok t/23wide_db.t ........... skipped: Database character set is not Unicode t/23wide_db_8bit.t ...... skipped: Database character set is not Unicode t/23wide_db_al32utf8.t .. skipped: Database character set is not Unicode t/24implicit_utf8.t ..... ok t/25plsql.t ............. ok t/26exe_array.t ......... ok t/28array_bind.t ........ ok t/30long.t .............. ok t/31lob.t ............... 1/11 DBD::Oracle::st execute failed: ORA-24813: cannot send or receive an unsupported LOB (DBD ERROR: OCIStmtExecute) [for Statement "BEGIN ? := DBMS_LOB.GETLENGTH( ? ); END;" with ParamValues: :p1=undef, :p2=OCILobLocatorPtr=SCALAR(0xc39be8)] at t/31lob.t line 123. t/31lob.t ............... Dubious, test returned 1 (wstat 256, 0x100) Failed 4/11 subtests t/31lob_extended.t ...... ok t/32xmltype.t ........... ok t/34pres_lobs.t ......... ok t/40ph_type.t ........... 1/19 Placeholder behaviour for ora_type=1 VARCHAR2 (the default) varies with Oracle version. Oracle 7 didn't strip trailing spaces, Oracle 8 did, until 9.2.x Your system doesn't. If that seems odd, let us know. t/40ph_type.t ........... ok t/50cursor.t ............ ok t/51scroll.t ............ ok t/55nested.t ............ ok t/56embbeded.t .......... ok t/58object.t ............ ok t/60reauth.t ............ skipped: ORACLE_USERID_2 not defined. t/70meta.t .............. ok t/80ora_charset.t ....... 1/14 t/80ora_charset.t ....... Dubious, test returned 4 (wstat 1024, 0x400) Failed 4/14 subtests Test Summary Report ------------------- t/31lob.t (Wstat: 256 Tests: 8 Failed: 1) Failed test: 8 Non-zero exit status: 1 Parse errors: Bad plan. You planned 11 tests but ran 8. t/80ora_charset.t (Wstat: 1024 Tests: 14 Failed: 4) Failed tests: 9-10, 13-14 Non-zero exit status: 4 Files=30, Tests=2108, 18 wallclock secs ( 0.48 usr 0.08 sys + 5.31 cusr 1.02 csys = 6.89 CPU) Result: FAIL Failed 2/30 test programs. 5/2108 subtests failed. Show quoted text

> or if you want it in one go you can try the latest release condidate > > http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.24-RC1.tar

-- H.Merijn Brand http://tux.nl Perl Monger http://amsterdam.pm.org/ using & porting perl 5.6.2, 5.8.x, 5.10.x, 5.11.x on HP-UX 10.20, 11.00, 11.11, 11.23, and 11.31, OpenSuSE 10.3, 11.0, and 11.1, AIX 5.2 and 5.3. http://mirrors.develooper.com/hpux/ http://www.test-smoke.org/ http://qa.perl.org http://www.goldmark.org/jeff/stupid-disclaimers/

Thu Dec 31 10:30:34 2009 The RT System itself - Status changed from 'resolved' to 'open'

Mon Feb 08 13:58:50 2010 champoux [...] pythian.com - Correspondence added

This is fixed in 1.24 I think this is a dup of an earlier #

Mon Feb 08 13:58:51 2010 champoux [...] pythian.com - Status changed from 'open' to 'resolved'

Tue Dec 21 19:02:40 2010 champoux [...] pythian.com - Fixed in 1.24 added

Tue Dec 21 19:02:40 2010 champoux [...] pythian.com - Fixed in 1.24a added

Tue Dec 21 19:02:40 2010 champoux [...] pythian.com - Fixed in 1.24b added

Bug #51595 for DBD-Oracle: bind_param () fails its function if table has more than one blob

Preferred bug tracker