Ah. You're trying to bind a placeholder as a BFILE. That's not supported yet. I've no idea what's required, possibly not much. Patches welcome.
Tim. On Mon, Aug 22, 2005 at 10:36:42AM -0500, Paul Dyer wrote: > Hi and thanks for the feedback, > > ORA_BFILE doesn't seem to be defined in DBD::Oracle. I get this error when > I use it: > > Bareword "ORA_BFILE" not allowed while "strict subs" in use > > or this error when I turn off strict: > > Argument "ORA_BFILE" isn't numeric in subroutine entry at > ./purge_images.pl.1 line 81. > Can't bind :fil, ora_type 0 not supported by DBD::Oracle > > If I put the integer 114 as the ora_type, this error follows: > > SQL type 114 for ':fil' is not fully supported, bound as SQL_VARCHAR > instead > > I've attached my test program, with my different attempts in comments. A > million thanks for your help. > > Paul Dyer > 504-338-8750 > > > >From: Tim Bunce <[EMAIL PROTECTED]> > >To: Paul Dyer <[EMAIL PROTECTED]> > >CC: [EMAIL PROTECTED], [email protected] > >Subject: Re: DBD Oracle column type > >Date: Mon, 22 Aug 2005 13:09:39 +0100 > > > >On Sun, Aug 21, 2005 at 05:39:39PM -0500, Paul Dyer wrote: > >> Hi Tim, > >> > >> Thanks for the reply. I am using Perl 5.8, Perl-DBI 1.32, DBD::Oracle > >> 1.16, and RedHat Linux 2.4.21-32. > >> > >> I will take another look at what could be wrong. It helps to know that > >> BFILE is included. I noticed that DBI has more sql_types, which include > >the > >> blob locator. I will test with some of those defined type. > > > >Probably best not to. The DBIs sql_types are standards based whereas the > >BFILE support in DBD::Oracle is Oracle specific - I can't remember > >offhand what mapping DBD::Oracle does between the two. Possibly none. > > > >Stick to folling examples using ORA_BLOB and just change ORA_BLOB to > >ORA_BFILE. > > > >> I will alse try Data::Dumper to parse the returned lob locator to get > >the > >> parts. > > > >You won't get very far. It's opaque. > > > >> If I can extract the directory and filename from the lob locator > >> myself, then I will not need to call the dbms_lob package at all. > > > >You can't[1], so you will. > > > >Tim. > > > >[1] In theory the Oracle::OCI package could help. But there might be a > >significant gap between theory and practice. > > > >> Paul > >> > >> >From: Tim Bunce <[EMAIL PROTECTED]> > >> >To: Paul Dyer <[EMAIL PROTECTED]> > >> >CC: [email protected] > >> >Subject: Re: DBD Oracle column type > >> >Date: Sun, 21 Aug 2005 19:29:34 +0100 > >> > > >> >On Sat, Aug 20, 2005 at 12:31:47AM -0500, Paul Dyer wrote: > >> >> > >> >> The problem is that my column type is NOT correctly BLOB. ora_type > >CLOB > >> >and > >> >> BLOB are defined as integer values (112 and 113). I am using type > >BFILE, > >> >> which is 114. When I use the integer 114 as the type, I get the error > >> >> stating invalid entry from DBD and that it will default to > >SQLVARCHAR. > >> >> > >> >> Would it be possible to get the BFILE type included in the next > >change > >> >> cycle of DBD::Oracle?? > >> > > >> >Umm. The Changes file shows: > >> > > >> > =head1 Changes in DBD-Oracle 1.13 14th March 2003 > >> > > >> > ... > >> > Added BFILE support thanks to David Hull. > >> > ... > >> > > >> >Perhaps you need up upgrade. > >> > > >> >Tim. > >> > > >> > >> > > #!/usr/bin/perl -w > ############################################################################### > # > # /usr/local/bin/purge_images.pl > # > # Purge bad LOB images from the database and the filesystem. > # > # Read the image_headers and image_pages tables to get the BFILE > locations > # and the directory path (from dba_directories). Load the info to an > array. > # Parse the array, delete the BFILE from the filesystem, and delete the > # corresponding rows from the image_ tables. > # > # Parameters: > # debug - set debug on > # > # 08/19/2005 Paul Dyer > ############################################################################### > use strict; > use DBI; > use DBD::Oracle qw(:ora_types); > my $rc; > > # set the parameters. > my ($PROGNAME) = $0 =~ /.*\/(.*)/; > my $DEBUG = (shift @ARGV || 0); > > if ( ! $ENV{"ORACLE_SID"} ) { > $ENV{"ORACLE_SID"} = "DBKPRD"; > $ENV{"ORACLE_HOME"} = "/usr/ora92"; > print STDERR "ORACLE_SID set to ". $ENV{"ORACLE_SID"} ."\n" if $DEBUG; > print STDERR "ORACLE_HOME set to ". $ENV{"ORACLE_HOME"} ."\n" if $DEBUG; > } > > $ENV{"DBI_USER"} = "x"; > $ENV{"DBI_PASS"} = "x"; > ### The database handle > my $dbh = DBI->connect( "dbi:Oracle:".$ENV{"ORACLE_SID"}, undef, undef, > { AutoCommit => 0, # must commit or rollback transactions > RaiseError => 0, > # LongTruncOk => 1, # not needed, use ora_auto_lob to get > # the locator instead of the lob. > PrintError => 1 }); # turn on warn errors. > > ### get the bad image rows, status code 8 > if ( defined $dbh ) { > $rc = fetch_data ("SELECT imp_imh_id, imp_page_no, imp_image ". > "FROM daybreak.image_pages, daybreak.image_headers ". > "WHERE imh_id = imp_imh_id ". > "AND imh_image_status_cd = 8" ); > } > > $dbh->disconnect; > exit; > > sub fetch_data { # prepare stmt handle, execute, then fetch. > my ($arg); > $arg = $_[0]; > my $rvalue; > my $hashref; > my ($fil, $dir_alias, $name); > > > my $sth = $dbh->prepare( $arg, > { ora_auto_lob => 0 }) # get the lob locator > or die "\nCan't prepare SQL statement:\n$DBI::errstr\n"; > > ### Execute the statement in the database > $sth->execute > or die "\nCan't execute SQL statement:\n$DBI::errstr\n"; > > ### Retrieve the returned rows of data > while ( $hashref = $sth->fetchrow_hashref() ) { > print STDERR "FETCH: $hashref->{IMP_IMH_ID}". > " $hashref->{IMP_PAGE_NO}\n" if $DEBUG; > > my $sth2 = $dbh->prepare("BEGIN dbms_lob.filegetname". > "(:fil, :dir_alias, :name); END;", > { ora_auto_lob => 0 }) # get the lob locator > or die "\nCan't prepare SQL statement:\n$DBI::errstr\n"; > > $sth2->bind_param(":fil", ${ $hashref->{IMP_IMAGE} }, > 114); > ## { ora_type => ORA_BFILE } ); > ## { ora_type => ORA_BLOB } ); > $sth2->bind_param_inout(":dir_alias", \$dir_alias, 100); > $sth2->bind_param_inout(":name", \$name, 100); > $sth2->execute > or die "\nCan't execute SQL statement:\n$DBI::errstr\n"; > } > > warn "\nData fetching terminated early by error:\n$DBI::errstr\n" > if $DBI::err; > return $rvalue; > } >
