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;
}
