Bug #75163 for DBD-Oracle: bug with BFILEs and ora_auto

Mon Feb 20 10:55:08 2012 norbert.debes [...] oradbpro.com - Ticket created

Subject:	bug with BFILEs and ora_auto_lob=>0 in DBD::Oracle?
Date:	Mon, 20 Feb 2012 16:54:39 +0100
To:	bug-DBD-Oracle [...] rt.cpan.org, timb [...] cpan.org, yanick [...] cpan.org
From:	Norbert Debes <norbert.debes [...] oradbpro.com>

Hi Tim, Yannick, I'd like to report this issue on the otherwise excellent Perl DBI and DBD::Oracle. It seems that BFILEs don't work with ora_auto_lob => 0 in DBD::Oracle. The problem appears to be that BFILEs need to be *explicitly opened*. There is also a separate constant in OCI for BFILEs which I guess would be worth adding to DBD::Oracle. This constant does not seem to be available in DBD::Oracle. Page 2-10 in 11.2 OCI manual has this table: Table 2--2 Descriptor Types Description C Datatype OCI Type Constant snapshot descriptor OCISnapshot OCI_DTYPE_SNAP result set descriptor OCIResult OCI_DTYPE_RSET *LOB datatype locator OCILobLocator OCI_DTYPE_LOB BFILE datatype locator OCILobLocator OCI_DTYPE_FILE* Here's what happens when I run the attached program/testcase: E:\home\ndebes\it\perl>perl bfiledmp.pl DBI version 1.616 DBD version 1.30 File size 181120 bytes BFILE LOB locator: OCILobLocatorPtr=SCALAR(0x95377c); ora_lob_is_init: 1 BFILE LOB locator: OCILobLocatorPtr=SCALAR(0x95377c); ora_lob_is_init: 1 DBD::Oracle::db ora_lob_read failed: ORA-22289: cannot perform FILEREAD operation on an unopened file or LOB (DBD ERROR: OCILobRead) at bfiledmp.pl line 401. ora_lob_read returned 0 bytes I'm trying to read the alert log over Oracle Net and TCP/IP as part of some monitoring routine. Since the alert log is appended to I want to read only the additional lines. That's not possible with ora_auto_lob => 1 which places the entire file's contents into a variable value. On 2/20/2012 13:51, Cornel Albert wrote: If you have a running Oracle instance, create a directory like this: Show quoted text

SQL> col bdump new_value bdump SQL> select value as bdump from v$parameter where

name='background_dump_dest'; BDUMP -------------------------------------------------------------------------------- C:\software\oracle\diag\rdbms\eleven2\eleven2\trace Show quoted text

SQL> create or replace directory BACKGROUND_DUMP_DEST as '&bdump';

old 1: create or replace directory BACKGROUND_DUMP_DEST as '&bdump' new 1: create or replace directory BACKGROUND_DUMP_DEST as 'C:\software\oracle\diag\rdbms\eleven2\eleven2\trace' Directory created. Show quoted text

SQL> select * from dba_directories where

directory_name='BACKGROUND_DUMP_DEST'; OWNER DIRECTORY_NAME ------------------------------ ------------------------------ DIRECTORY_PATH -------------------------------------------------------------------------------- SYS BACKGROUND_DUMP_DEST C:\software\oracle\diag\rdbms\eleven2\eleven2\trace Show quoted text

SQL> $dir

C:\software\oracle\diag\rdbms\eleven2\eleven2\trace\alert_eleven2.log C:\\software\\oracle\\diag\\rdbms\\eleven2\\eleven2\\trace\\alert_eleven2.log Show quoted text

SQL> $ls -l

C:\software\oracle\diag\rdbms\eleven2\eleven2\trace\alert_eleven2.log -rw-r--r-- 1 ndebes None 181276 Feb 20 16:43 C:\software\oracle\diag\rdbms\eleven2\eleven2\trace\alert_eleven2.log Now you can read alert_<ORACLE_SID>.log over Oracle Net as a BFILE. Testcase attached. /Here's a little tidbit: while Oracle failed to document the new SYSASM feature in the Oracle® Call Interface Programmer's Guide 11g Release 2 (11.2) E10646-04 October 2009 you've already documented it and DBD::Oracle supports it. Good job!/ -- Mit freundlichen Grüßen/Kind regards Norbert Debes Hinweis: Kontaktdaten s. angehängte vcard/Note: contact information in attached vcard

Message body is not shown because sender requested not to inline it.

Thu Mar 01 16:06:05 2012 champoux [...] pythian.com - Correspondence added

Hi Norbert, Thanks for the report and the example script. I have a favor to ask you: could you try to take the example and reduce it to the minimal code required to show the bug? I'm hoping that a little bit of its 400+ lines of code are not necessary to illustrate the issue. :-) Joy, `/anick

Thu Mar 01 16:06:06 2012 The RT System itself - Status changed from 'new' to 'open'

Fri Mar 02 04:43:58 2012 bohica [...] ntlworld.com - Correspondence added

On Mon Feb 20 10:55:08 2012, norbert.debes@oradbpro.com wrote: Show quoted text

> Hi Tim, Yannick, > > I'd like to report this issue on the otherwise excellent Perl DBI and > DBD::Oracle. > > It seems that BFILEs don't work with ora_auto_lob => 0 in DBD::Oracle. >

<snipped> When I follow your instructions and change your code for my system and run it without any arguments I get: DBI version 1.618 DBD version 1.38 File size 13515393 bytes BFILE LOB locator: OCILobLocatorPtr=SCALAR(0x9bef200); ora_lob_is_init: 1 BFILE LOB locator: OCILobLocatorPtr=SCALAR(0x9bef200); ora_lob_is_init: 1 DBD::Oracle::db ora_lob_read failed: ORA-22289: cannot perform FILEREAD operation on an unopened file or LOB (DBD ERROR: OCILobRead) at rt75163.pl line 402. ora_lob_read returned 0 bytes Is that what you expect to happen before I try and look into this? Martin -- Martin J. Evans Wetherby, UK

Fri Mar 02 05:38:56 2012 bohica [...] ntlworld.com - Correspondence added

On Mon Feb 20 10:55:08 2012, norbert.debes@oradbpro.com wrote: Show quoted text

> There is also a separate constant in OCI for BFILEs which I guess > would > be worth adding to DBD::Oracle. This constant does not seem to be > available in DBD::Oracle. Page 2-10 in 11.2 OCI manual has this table: > Table 2--2 Descriptor Types > Description C Datatype OCI Type Constant > snapshot descriptor OCISnapshot OCI_DTYPE_SNAP > result set descriptor OCIResult OCI_DTYPE_RSET > *LOB datatype locator OCILobLocator OCI_DTYPE_LOB > BFILE datatype locator OCILobLocator OCI_DTYPE_FILE*

Interestingly DBD::Oracle does define ORA_BFILE in Oracle.h like this: #define ORA_BFILE 114 but never exports it. It is easy to export it but I'm not sure on the value as a grep for 114 and OCI_DTYPE_FILE in the instant client headers returns: ocidfn.h:#define SQLT_BFILEE 114 oci.h:#define OCI_DTYPE_FILE 56 Martin -- Martin J. Evans Wetherby, UK

Fri Mar 02 08:08:37 2012 norbert.debes [...] oradbpro.com - Correspondence added

Subject:	Re: [rt.cpan.org #75163] bug with BFILEs and ora_auto_lob=>0 in DBD::Oracle?
Date:	Fri, 02 Mar 2012 14:08:16 +0100
To:	bug-DBD-Oracle [...] rt.cpan.org
From:	Norbert Debes <norbert.debes [...] oradbpro.com>

Hi Martin, yes, that's the same error I had. Thanks for reproducing it. The Oracle DBMS requires that the BFILE be opened explicitly, hence the error: ORA-22289: cannot perform FILEREAD operation on an unopened file or LOB It sounds like the DBD::Oracle code does not realize that it is dealing with a BFILE Lob locator and does not explicitly open the BFILE behind the scenes. I tried opening the BFILE using dbms_lob.open, but then I get a different error that suggests the BFILE Lob locator is not passed correctly to DBMS_LOB. It seems to me that the BFILE does get opened behind the scenes with ora_auto_lob => 1, but with ora_auto_lob => 0 it does. Maybe you can veriry my theory. The goal is to read files on the database server piece by piece (hence ora_auto_lob => 0) over an Oracle Net connection. I suppose the goal is clear. I'd he happy to discuss with you over the phone or Skype if you think that would help. Mit freundlichen Grüßen/Kind regards Norbert Debes Hinweis: Kontaktdaten s. angehängte vcard/Note: contact information in attached vcard On 3/2/2012 10:43, Martin J Evans via RT wrote: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=75163> > > On Mon Feb 20 10:55:08 2012, norbert.debes@oradbpro.com wrote:

>> Hi Tim, Yannick, >> >> I'd like to report this issue on the otherwise excellent Perl DBI and >> DBD::Oracle. >> >> It seems that BFILEs don't work with ora_auto_lob => 0 in DBD::Oracle. >>

> <snipped> > > When I follow your instructions and change your code for my system and > run it without any arguments I get: > > DBI version 1.618 DBD version 1.38 > File size 13515393 bytes > BFILE LOB locator: OCILobLocatorPtr=SCALAR(0x9bef200); ora_lob_is_init: > 1 > BFILE LOB locator: OCILobLocatorPtr=SCALAR(0x9bef200); ora_lob_is_init: > 1 > DBD::Oracle::db ora_lob_read failed: ORA-22289: cannot perform FILEREAD > operation on an unopened file or LOB (DBD ERROR: OCILobRead) at > rt75163.pl line 402. > ora_lob_read returned 0 bytes > > Is that what you expect to happen before I try and look into this? > > Martin

Message body is not shown because sender requested not to inline it.

Fri Mar 02 08:10:59 2012 norbert.debes [...] oradbpro.com - Correspondence added

Subject:	Re: [rt.cpan.org #75163] bug with BFILEs and ora_auto_lob=>0 in DBD::Oracle?
Date:	Fri, 02 Mar 2012 14:10:29 +0100
To:	bug-DBD-Oracle [...] rt.cpan.org
From:	Norbert Debes <norbert.debes [...] oradbpro.com>

yep, interesting Mit freundlichen Grüßen/Kind regards Norbert Debes Hinweis: Kontaktdaten s. angehängte vcard/Note: contact information in attached vcard On 3/2/2012 11:38, Martin J Evans via RT wrote: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=75163> > > On Mon Feb 20 10:55:08 2012, norbert.debes@oradbpro.com wrote: >

>> There is also a separate constant in OCI for BFILEs which I guess >> would >> be worth adding to DBD::Oracle. This constant does not seem to be >> available in DBD::Oracle. Page 2-10 in 11.2 OCI manual has this table: >> Table 2--2 Descriptor Types >> Description C Datatype OCI Type Constant >> snapshot descriptor OCISnapshot OCI_DTYPE_SNAP >> result set descriptor OCIResult OCI_DTYPE_RSET >> *LOB datatype locator OCILobLocator OCI_DTYPE_LOB >> BFILE datatype locator OCILobLocator OCI_DTYPE_FILE*

> Interestingly DBD::Oracle does define ORA_BFILE in Oracle.h like this: > > #define ORA_BFILE 114 > > but never exports it. It is easy to export it but I'm not sure on the > value as a grep for 114 and OCI_DTYPE_FILE in the instant client headers > returns: > > ocidfn.h:#define SQLT_BFILEE 114 > oci.h:#define OCI_DTYPE_FILE 56 > > Martin

Message body is not shown because sender requested not to inline it.

Fri Mar 02 13:02:21 2012 bohica [...] ntlworld.com - Correspondence added

On Fri Mar 02 08:08:37 2012, norbert.debes@oradbpro.com wrote: Show quoted text

> Hi Martin, > > yes, that's the same error I had. Thanks for reproducing it. The > Oracle > DBMS requires that the BFILE be opened explicitly, hence the error: > > ORA-22289: cannot perform FILEREAD operation on an unopened file or > LOB > > It sounds like the DBD::Oracle code does not realize that it is > dealing > with a BFILE Lob locator and does not explicitly open the BFILE behind > the scenes. > I tried opening the BFILE using dbms_lob.open, but then I get a > different error that suggests the BFILE Lob locator is not passed > correctly to DBMS_LOB. > > It seems to me that the BFILE does get opened behind the scenes with > ora_auto_lob => 1, but with ora_auto_lob => 0 it does. Maybe you can > veriry my theory.

I presume you mean with ora_auto_lob = 0 it does NOT. Show quoted text

> The goal is to read files on the database server piece by piece (hence > ora_auto_lob => 0) over an Oracle Net connection. > > I suppose the goal is clear. I'd he happy to discuss with you over the > phone or Skype if you think that would help.

I need to look at the code and try and figure it out - I don't use BFILEs and I did not write the original code. I'm also pushed for time right now. You can find me on irc.perl.org channel #dbi as mje most days and some evenings. Martin -- Martin J. Evans Wetherby, UK

Sat Mar 03 04:25:27 2012 bohica [...] ntlworld.com - Correspondence added

On Fri Mar 02 13:02:21 2012, MJEVANS wrote: Show quoted text

> I need to look at the code and try and figure it out - I don't use > BFILEs and I did not write the original code. I'm also pushed for time > right now. You can find me on irc.perl.org channel #dbi as mje most days > and some evenings. > > Martin

You are correct. The code is not calling OCILobFileOpen and OCILobFileClose when ora_auto_lobs = 0. I'll try and find some time to fix it. Martin -- Martin J. Evans Wetherby, UK

Sat Mar 03 05:53:20 2012 bohica [...] ntlworld.com - Correspondence added

On Sat Mar 03 04:25:27 2012, MJEVANS wrote: Show quoted text

> On Fri Mar 02 13:02:21 2012, MJEVANS wrote:

> > I need to look at the code and try and figure it out - I don't use > > BFILEs and I did not write the original code. I'm also pushed for time > > right now. You can find me on irc.perl.org channel #dbi as mje most days > > and some evenings. > > > > Martin

> > You are correct. The code is not calling OCILobFileOpen and > OCILobFileClose when ora_auto_lobs = 0. I'll try and find some time to > fix it. > > Martin

The problem is the code which reads a lob is in Oracle.xs and is only passed the connection handle and the lob locator. I don't know how to tell the passed lob locator is a BFILE (any ideas?). I could try calling OCILobFileIsOpen but I don't know what would happen if it was not a BFILE locator. Then there is the problem of when to close it - OCILobFileClose, as the caller may not read all the lob but there must be some clean up code somewhere for locators. Martin -- Martin J. Evans Wetherby, UK

Sat Mar 03 06:12:24 2012 bohica [...] ntlworld.com - Correspondence added

On Sat Mar 03 05:53:20 2012, MJEVANS wrote: Show quoted text

> On Sat Mar 03 04:25:27 2012, MJEVANS wrote:

> > On Fri Mar 02 13:02:21 2012, MJEVANS wrote:

> > > I need to look at the code and try and figure it out - I don't use > > > BFILEs and I did not write the original code. I'm also pushed for time > > > right now. You can find me on irc.perl.org channel #dbi as mje

most days Show quoted text

> > > and some evenings. > > > > > > Martin

> > > > You are correct. The code is not calling OCILobFileOpen and > > OCILobFileClose when ora_auto_lobs = 0. I'll try and find some time to > > fix it. > > > > Martin

> > The problem is the code which reads a lob is in Oracle.xs and is only > passed the connection handle and the lob locator. I don't know how to > tell the passed lob locator is a BFILE (any ideas?). I could try calling > OCILobFileIsOpen but I don't know what would happen if it was not a > BFILE locator. > > Then there is the problem of when to close it - OCILobFileClose, as the > caller may not read all the lob but there must be some clean up code > somewhere for locators. > > Martin

This patch makes it work but is far from complete as: 1. it does not trace the call to OCILobFileIsOpen 2. it does not close the bfile at all so probably leaks something It is a diff from the subversion trunk. Index: Oracle.xs =================================================================== --- Oracle.xs (revision 15190) +++ Oracle.xs (working copy) @@ -560,6 +560,20 @@ dest_sv = &PL_sv_undef; return; } + { + boolean is_open; + + status = OCILobFileIsOpen(imp_dbh->svchp, imp_dbh->errhp, locator, &is_open); + if (status == OCI_SUCCESS && !is_open) { + OCILobFileOpen_log_stat(imp_dbh->svchp, imp_dbh->errhp, locator, + (ub1)OCI_FILE_READONLY, status); + if (status != OCI_SUCCESS) { + oci_error(dbh, imp_dbh->errhp, status, "OCILobFileOpen"); + dest_sv = &PL_sv_undef; + } + } + } + -- Martin J. Evans Wetherby, UK

Sat Mar 03 06:46:46 2012 bohica [...] ntlworld.com - Correspondence added

Here is a more complete patch - just missing tracing now. Seems to work and seems to still pass all the existing tests. The diff is against subversion trunk and has some other things in I'm working on too. Martin -- Martin J. Evans Wetherby, UK

Subject:

x.diff

Index: oci8.c =================================================================== --- oci8.c (revision 15202) +++ oci8.c (working copy) @@ -4651,7 +4651,7 @@ for(i=0; i < lr->num_fields; ++i) { imp_fbh_t *fbh = &lr->fbh_ary[i]; - ora_free_fbh_contents(fbh); + ora_free_fbh_contents(sth, fbh); } sv_free(lr->fbh_ary_sv); Safefree(imp_sth->lob_refetch); Index: lib/DBD/Oracle.pm =================================================================== --- lib/DBD/Oracle.pm (revision 15202) +++ lib/DBD/Oracle.pm (working copy) @@ -29,7 +29,7 @@ ora_types => [ qw( ORA_VARCHAR2 ORA_STRING ORA_NUMBER ORA_LONG ORA_ROWID ORA_DATE ORA_RAW ORA_LONGRAW ORA_CHAR ORA_CHARZ ORA_MLSLABEL ORA_XMLTYPE - ORA_CLOB ORA_BLOB ORA_RSET ORA_VARCHAR2_TABLE ORA_NUMBER_TABLE + ORA_CLOB ORA_BLOB ORA_BFILE ORA_RSET ORA_VARCHAR2_TABLE ORA_NUMBER_TABLE SQLT_INT SQLT_FLT ORA_OCI SQLT_CHR SQLT_BIN ) ], ora_session_modes => [ qw( ORA_SYSDBA ORA_SYSOPER ORA_SYSASM) ], @@ -1201,7 +1201,7 @@ ORA_VARCHAR2 ORA_STRING ORA_NUMBER ORA_LONG ORA_ROWID ORA_DATE ORA_RAW ORA_LONGRAW ORA_CHAR ORA_CHARZ ORA_MLSLABEL ORA_XMLTYPE ORA_CLOB ORA_BLOB ORA_RSET ORA_VARCHAR2_TABLE ORA_NUMBER_TABLE SQLT_INT SQLT_FLT ORA_OCI - SQLT_CHR SQLT_BIN + SQLT_CHR SQLT_BIN ORA_BFILE =item SQLCS_IMPLICIT Index: dbdimp.c =================================================================== --- dbdimp.c (revision 15202) +++ dbdimp.c (working copy) @@ -3197,7 +3197,7 @@ if (SvPVX(sv)) { SvCUR_set(sv, phs->alen); *SvEND(sv) = '\0'; - SvPOK_only_UTF8(sv); + SvPOK_only_UTF8(sv); if (CSFORM_IMPLIES_UTF8(SQLCS_IMPLICIT)) { #ifdef sv_utf8_decode sv_utf8_decode(sv); @@ -3205,6 +3205,13 @@ SvUTF8_on(sv); #endif } + if (CSFORM_IMPLIES_UTF8(SQLCS_IMPLICIT)) { +#ifdef sv_utf8_decode + sv_utf8_decode(sv); +#else + SvUTF8_on(sv); +#endif + } } else { /* shouldn't happen */ debug = 2; @@ -3977,16 +3984,34 @@ void -ora_free_fbh_contents(imp_fbh_t *fbh) +ora_free_fbh_contents(SV *sth, imp_fbh_t *fbh) { dTHX; + D_imp_sth(sth); + D_imp_dbh_from_sth; + if (fbh->fb_ary) fb_ary_free(fbh->fb_ary); sv_free(fbh->name_sv); - if (fbh->desc_h) - OCIDescriptorFree_log(fbh->desc_h, fbh->desc_t); - if (fbh->obj) + if (fbh->desc_h) { + boolean is_open; + sword status; + + status = OCILobFileIsOpen(imp_dbh->svchp, imp_dbh->errhp, fbh->desc_h, &is_open); + if (status == OCI_SUCCESS && is_open) { + OCILobFileClose_log_stat(imp_sth->svchp, imp_sth->errhp, + fbh->desc_h, status); + } + + + OCIDescriptorFree_log(fbh->desc_h, fbh->desc_t); + } + + if (fbh->obj) { + if (fbh->obj->obj_value) + OCIObjectFree(fbh->imp_sth->envhp, fbh->imp_sth->errhp, fbh->obj->obj_value, (ub2)0); Safefree(fbh->obj); + } } @@ -4092,7 +4117,7 @@ imp_sth->eod_errno = 1403; for(i=0; i < fields; ++i) { imp_fbh_t *fbh = &imp_sth->fbh[i]; - ora_free_fbh_contents(fbh); + ora_free_fbh_contents(sth, fbh); } Safefree(imp_sth->fbh); if (imp_sth->fbh_cbuf) Index: dbdimp.h =================================================================== --- dbdimp.h (revision 15202) +++ dbdimp.h (working copy) @@ -321,7 +321,7 @@ void dbd_init_oci _((dbistate_t *dbistate)); void dbd_preparse _((imp_sth_t *imp_sth, char *statement)); void dbd_fbh_dump(imp_fbh_t *fbh, int i, int aidx); -void ora_free_fbh_contents _((imp_fbh_t *fbh)); +void ora_free_fbh_contents _((SV *sth, imp_fbh_t *fbh)); void ora_free_templob _((SV *sth, imp_sth_t *imp_sth, OCILobLocator *lobloc)); int ora_dbtype_is_long _((int dbtype)); fb_ary_t *fb_ary_alloc _((ub4 bufl, int size)); Index: Oracle.xs =================================================================== --- Oracle.xs (revision 15202) +++ Oracle.xs (working copy) @@ -24,6 +24,7 @@ ORA_XMLTYPE = ORA_XMLTYPE ORA_CLOB = ORA_CLOB ORA_BLOB = ORA_BLOB + ORA_BFILE = ORA_BFILE ORA_RSET = ORA_RSET ORA_VARCHAR2_TABLE = ORA_VARCHAR2_TABLE ORA_NUMBER_TABLE = ORA_NUMBER_TABLE @@ -560,6 +561,20 @@ dest_sv = &PL_sv_undef; return; } + { + boolean is_open; + + status = OCILobFileIsOpen(imp_dbh->svchp, imp_dbh->errhp, locator, &is_open); + if (status == OCI_SUCCESS && !is_open) { + OCILobFileOpen_log_stat(imp_dbh->svchp, imp_dbh->errhp, locator, + (ub1)OCI_FILE_READONLY, status); + if (status != OCI_SUCCESS) { + oci_error(dbh, imp_dbh->errhp, status, "OCILobFileOpen"); + dest_sv = &PL_sv_undef; + } + } + } + OCILobRead_log_stat(imp_dbh->svchp, imp_dbh->errhp, locator, &amtp, (ub4)offset, /* offset starts at 1 */ bufp, (ub4)bufp_len, Index: t/31lob.t =================================================================== --- t/31lob.t (revision 15202) +++ t/31lob.t (working copy) @@ -8,8 +8,8 @@ unshift @INC ,'t'; require 'nchar_test_lib.pl'; -plan skip_all => "see RT#69350" - if ORA_OCI() =~ /^11\.2\./; +#plan skip_all => "see RT#69350" +# if ORA_OCI() =~ /^11\.2\./; my $dbh; $| = 1;

Sat Mar 03 16:28:56 2012 norbert.debes [...] oradbpro.com - Correspondence added

Subject:	Re: [rt.cpan.org #75163] bug with BFILEs and ora_auto_lob=>0 in DBD::Oracle?
Date:	Sat, 03 Mar 2012 21:42:13 +0100
To:	bug-DBD-Oracle [...] rt.cpan.org
From:	Norbert Debes <norbert.debes [...] oradbpro.com>

Hi Martin, thank you very much for working on this issue. How about adding functions oralobopen und oralobclose? In my view it would be ok to have to call these explicitly when working with BFILEs. You're right, something will be leaking. This week I ran some PL/SQL code that opened BFILEs but did not close them. It hit an error rather quickly. I just took some minutes to reproduce the error - so here it is: Show quoted text

SQL> declare

v_bfile bfile; begin select bfilename('ADR_TRACE', 'alert_ELEVEN2.log') into v_bfile from dual; dbms_lob.open(v_bfile, dbms_lob.file_readonly); end; / declare * ERROR at line 1: ORA-22290: operation would exceed the maximum number of opened files or LOBs ORA-06512: at "SYS.DBMS_LOB", line 1014 ORA-06512: at line 5 It depends on this parameter: session_max_open_files integer 10 I suppose the only way to fix it properly is to implement two new functions oralobopen und oralobclose (whatever their names may be). Or alternatively, to make a Perl variable that represents a BFILE lob locator work with PL/SQL such that an anonymous PL/SQL block can be used to open and close a BFILE lob like this: begin dbms_lob.open(:bfile_loc, dbms_lob.file_readonly); end; begin dbms_lob.close(:bfile_loc); end; These PL/SQL blocks would need to be prepared, and exeuted with set_param_inout, since the parameter FILE_LOC is an IN/OUT parameter. I tried that but it failed. It was on another system and I don't have the error here. Mit freundlichen Grüßen/Kind regards Norbert Debes Hinweis: Kontaktdaten s. angehängte vcard/Note: contact information in attached vcard On 3/3/2012 12:46, Martin J Evans via RT wrote: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=75163> > > Here is a more complete patch - just missing tracing now. > > Seems to work and seems to still pass all the existing tests. > > The diff is against subversion trunk and has some other things in I'm > working on too. > > Martin

Message body is not shown because sender requested not to inline it.

Sun Mar 04 04:14:46 2012 bohica [...] ntlworld.com - Correspondence added

On Sat Mar 03 16:28:56 2012, norbert.debes@oradbpro.com wrote: Show quoted text

> Hi Martin, > > thank you very much for working on this issue. > How about adding functions oralobopen und oralobclose? In my view it > would be ok to have to call these explicitly when working with BFILEs. > You're right, something will be leaking.

The version in the patch should not leak as the file is closed when the sth goes out of scope so it should avoid having to add 2 new functions. The only bit I'm not sure about is the way it determines the locator is a bfile descriptor. I couldn't find any way of determining this other than calling OCILobFileIsOpen which I guess (but did not test) will return an error for blob/clob descriptors (the error is ignored). So the only slight drawback with this code is that for blob/clobs there is an extra call over what happened before (which I doubt makes that much difference when retrieving the lob will be the biggest/longest operation. Show quoted text

> This week I ran some PL/SQL > code that opened BFILEs but did not close them. It hit an error rather > quickly. I just took some minutes to reproduce the error - so here it > is:

> SQL> declare

> v_bfile bfile; > begin > select bfilename('ADR_TRACE', 'alert_ELEVEN2.log') into v_bfile > from dual; > dbms_lob.open(v_bfile, dbms_lob.file_readonly); > end; > / > > declare > * > ERROR at line 1: > ORA-22290: operation would exceed the maximum number of opened files > or LOBs > ORA-06512: at "SYS.DBMS_LOB", line 1014 > ORA-06512: at line 5 > > It depends on this parameter: > session_max_open_files integer 10

Yeah, we could do with a test adding to the DBD::Oracle test suite for this. I'm not sure whether the select in your example is generally available without the other commands you provided to define the file. If you can think of a way of writing a test for bfiles which does not require changing someone's database I'd like to hear about it. Show quoted text

> I suppose the only way to fix it properly is to implement two new > functions oralobopen und oralobclose (whatever their names may be).

We could do that but it is more work and I'm pushed for time. I only got a chance to look at this over the weekend as I am away from home and looking after my mother in law. Show quoted text

> Or alternatively, to make a Perl variable that represents a BFILE lob > locator work with PL/SQL such that an anonymous PL/SQL block can be > used > to open and close a BFILE lob like this: > > begin > dbms_lob.open(:bfile_loc, dbms_lob.file_readonly); > end; > > begin > dbms_lob.close(:bfile_loc); > end; > > These PL/SQL blocks would need to be prepared, and exeuted with > set_param_inout, since the parameter FILE_LOC is an IN/OUT parameter. > > I tried that but it failed. It was on another system and I don't have > the error here. >

Try the patch I posted and maybe I'll have time to refine it or perhaps Yanick could pick it up. Martin -- Martin J. Evans Wetherby, UK

Mon Mar 05 14:04:47 2012 bohica [...] ntlworld.com - Correspondence added

Please don't take this as pestering but if you could try the patch and find it works I could commit it. If for some reason you cannot apply the patch I can supply a tar.gz of DBD::Oracle with the patch applied. Martin -- Martin J. Evans Wetherby, UK

Mon Mar 05 14:42:09 2012 norbert.debes [...] oradbpro.com - Correspondence added

Subject:	Re: [rt.cpan.org #75163] bug with BFILEs and ora_auto_lob=>0 in DBD::Oracle?
Date:	Mon, 05 Mar 2012 20:41:53 +0100
To:	bug-DBD-Oracle [...] rt.cpan.org
From:	Norbert Debes <norbert.debes [...] oradbpro.com>

Hi Martin, sorry, I wanted to reply to you earlier. Well you know the problem is, this is an assignment for one of the biggest insurance companies in Germany (I guess at least one of the biggest) and as far as I can tell they have never built Perl on Windows or AIX. They're using ActiveState on Windows (from 2008!!!) and probably some distro that came from IBM on AIX (5.8.8 on AIX, no DBD::Oracle). I've been using the Perl distro that comes with Oracle 11.2 since it includes DBD::Oracle. That is 5.10.0. I can't compile and roll out Perl and I've never done it (neither on Win nor on AIX). So at this time I can't try the patch. Meanwhile I've resorted to using an anonymous PL/SQL block which opens and closes the BFILE for each 4000 bytes that the code reads. This finally works and is fast enough. But of course I'm looking forward to trying the patch once it makes its way into ActiveState Perl or into what Oracle distributes with the DBMS. Thanks for your help, sorry if I've disappointed you. Mit freundlichen Grüßen/Kind regards Norbert Debes Hinweis: Kontaktdaten s. angehängte vcard/Note: contact information in attached vcard On 3/5/2012 20:04, Martin J Evans via RT wrote: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=75163> > > Please don't take this as pestering but if you could try the patch and > find it works I could commit it. If for some reason you cannot apply the > patch I can supply a tar.gz of DBD::Oracle with the patch applied. > > Martin

Message body is not shown because sender requested not to inline it.

Tue Mar 06 04:09:09 2012 bohica [...] ntlworld.com - Correspondence added

On Mon Mar 05 14:42:09 2012, norbert.debes@oradbpro.com wrote: Show quoted text

> Hi Martin, > > sorry, I wanted to reply to you earlier. Well you know the problem is, > this is an assignment for one of the biggest insurance companies in > Germany (I guess at least one of the biggest) and as far as I can tell > they have never built Perl on Windows or AIX. They're using > ActiveState > on Windows (from 2008!!!) and probably some distro that came from IBM > on > AIX (5.8.8 on AIX, no DBD::Oracle). I've been using the Perl distro > that > comes with Oracle 11.2 since it includes DBD::Oracle. That is 5.10.0. > > I can't compile and roll out Perl and I've never done it (neither on > Win > nor on AIX). > > So at this time I can't try the patch.

ok, that's fine but... Not sure about ActiveState from 2008 but you can usually download the MinGW package and that lets you compile stuff. My motes from README.windows in DBD::ODBC say: 4. If you are manually attempting to build DBD::ODBC for use with ActiveState Perl (ActivePerl) don't. You can use cpan like this: cpan M/MJ/MJEVANS/DBD-ODBC-1.23_4.tar.gz If you don't have Microsoft VC on your PATH, then this will download and install MinGW for you (inside the Perl tree) and use that instead. This should work for the latest builds of all major Perl versions, 5.8.9.827, 5.10.1.1007 and 5.12.0.1200. On older versions you may need to install MinGW with PPM "by-hand" first: ppm install MinGW cpan M/MJ/MJEVANS/DBD-ODBC-1.23_4.tar.gz On ActivePerl 5.8.8 and earlier you will have to download, install and configure MinGW and dmake manually though, so you may not want to bother... However, I'm not sure if your "can't compile" is really a not allowed to or just you believe you have no way to. It is a great shame people in your position are restricted in this way. Show quoted text

> Meanwhile I've resorted to using an anonymous PL/SQL block which opens > and closes the BFILE for each 4000 bytes that the code reads. This > finally works and is fast enough. > > But of course I'm looking forward to trying the patch once it makes > its > way into ActiveState Perl or into what Oracle distributes with the > DBMS. > > Thanks for your help, sorry if I've disappointed you.

No, I understand. I cannot decide whether the patch as is which will DWIM is best or to add the open/close methods because I'm not sure about the impact of calling OCILobFileIsOpen on non bfile lobs. I'll see what other people think. I'll have to add a developer test only as I've found no way of doing bfile reads without first changing the database in some way (which we cannot do on other peoples machines) - that is a shame. Martin -- Martin J. Evans Wetherby, UK

Tue Mar 06 05:28:19 2012 norbert.debes [...] oradbpro.com - Correspondence added

Subject:	Re: [rt.cpan.org #75163] bug with BFILEs and ora_auto_lob=>0 in DBD::Oracle?
Date:	Tue, 06 Mar 2012 11:27:59 +0100
To:	bug-DBD-Oracle [...] rt.cpan.org
From:	Norbert Debes <norbert.debes [...] oradbpro.com>

Hi Martin, what I can do is verify my assumption that they never built Perl themselves. I don't believe the additional open call for an internal LOB will noticeably hurt a significant number of Perl programs. To find out if a locator refers to a BFILE you could also use OCILobFileExists() which would also cause an error on internal LOBs Returns TRUE if the BFILE exists on the server; FALSE if it does not. Comments Checks to see if the BFILE exists on the server's file system.*It is an error to call this function for an internal LOB.* Automated tests for BFILE: most databases these days do have some rows in the view ALL_DIRECTORIES, e.g. data pump works with directories and so does OCM (Oracle Configuration Manager). But you would need a username and password and know at least ORACLE_SID and ORACLE_HOME and the user would need read privilege on the directory and then you would need to place a file into the directory to be able to access it as a BFILE or find out whether there are any files readable to the user that owns the processes of the Oracle DBMS instance. This is probably too much for an automated test. Mit freundlichen Grüßen/Kind regards Norbert Debes Hinweis: Kontaktdaten s. angehängte vcard/Note: contact information in attached vcard On 3/6/2012 10:09, Martin J Evans via RT wrote: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=75163> > > On Mon Mar 05 14:42:09 2012, norbert.debes@oradbpro.com wrote:

>> Hi Martin, >> >> sorry, I wanted to reply to you earlier. Well you know the problem is, >> this is an assignment for one of the biggest insurance companies in >> Germany (I guess at least one of the biggest) and as far as I can tell >> they have never built Perl on Windows or AIX. They're using >> ActiveState >> on Windows (from 2008!!!) and probably some distro that came from IBM >> on >> AIX (5.8.8 on AIX, no DBD::Oracle). I've been using the Perl distro >> that >> comes with Oracle 11.2 since it includes DBD::Oracle. That is 5.10.0. >> >> I can't compile and roll out Perl and I've never done it (neither on >> Win >> nor on AIX). >> >> So at this time I can't try the patch.

> ok, that's fine but... > > Not sure about ActiveState from 2008 but you can usually download the > MinGW package and that lets you compile stuff. My motes from > README.windows in DBD::ODBC say: > > 4. If you are manually attempting to build DBD::ODBC for use with > ActiveState Perl (ActivePerl) don't. You can use cpan like this: > > cpan M/MJ/MJEVANS/DBD-ODBC-1.23_4.tar.gz > > If you don't have Microsoft VC on your PATH, then this will > download and install MinGW for you (inside the Perl tree) and use > that instead. > > This should work for the latest builds of all major Perl versions, > 5.8.9.827, 5.10.1.1007 and 5.12.0.1200. On older versions you may > need to install MinGW with PPM "by-hand" first: > > ppm install MinGW > cpan M/MJ/MJEVANS/DBD-ODBC-1.23_4.tar.gz > > On ActivePerl 5.8.8 and earlier you will have to download, install > and configure MinGW and dmake manually though, so you may not want > to bother... > > However, I'm not sure if your "can't compile" is really a not allowed to > or just you believe you have no way to. > > It is a great shame people in your position are restricted in this way. >

>> Meanwhile I've resorted to using an anonymous PL/SQL block which opens >> and closes the BFILE for each 4000 bytes that the code reads. This >> finally works and is fast enough. >> >> But of course I'm looking forward to trying the patch once it makes >> its >> way into ActiveState Perl or into what Oracle distributes with the >> DBMS. >> >> Thanks for your help, sorry if I've disappointed you.

> No, I understand. I cannot decide whether the patch as is which will > DWIM is best or to add the open/close methods because I'm not sure about > the impact of calling OCILobFileIsOpen on non bfile lobs. I'll see what > other people think. I'll have to add a developer test only as I've found > no way of doing bfile reads without first changing the database in some > way (which we cannot do on other peoples machines) - that is a shame. > > Martin

Message body is not shown because sender requested not to inline it.

Sun Apr 22 06:20:57 2012 bohica [...] ntlworld.com - Status changed from 'open' to 'resolved'

Sun Apr 22 06:20:57 2012 bohica [...] ntlworld.com - Fixed in 1.43_00 added

Bug #75163 for DBD-Oracle: bug with BFILEs and ora_auto_lob=>0 in DBD::Oracle?

Preferred bug tracker