DBD Oracle column type

Hi,
I posted this on the cpanforum.org. Could you comment??

I am writing a Perl-DBI program to access two Oracle tables that store BFILE
lobs on the filesystem. After getting the lob locator, I plan to get the
directory path and filename from the lob locator, in order to purge the file
from the filesystem.

Trouble is, I have not been able to bind the lob locator returned so that I
can send it to the dbms_lob.filegetname procedure. I get the error:

PLS-00306: wrong number or types of arguments in call to 'FILEGETNAME'

Here is some of the code:

### 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";
$fil = ${ $hashref->{IMP_IMAGE} };
$sth2->bind_param(":fil", $fil,
{ ora_type => ORA_BLOB } );
$sth2->bind_param_inout(":dir_alias", \$dir_alias, 100);
$sth2->bind_param_inout(":name", \$name, 100);
$sth2->execute

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??

Thanks!!

Re: DBD Oracle column type

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.

Re: DBD Oracle column type

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
> >To: Paul Dyer
> >CC: dbi-users@perl.org
> >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.
> >
>
>

Re: DBD Oracle column type

------=_NextPart_000_6345_2e2d_57cd
Content-Type: text/plain; format=flowed

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
>To: Paul Dyer
>CC: Tim.Bunce@pobox.com, dbi-users@perl.org
>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
> > >To: Paul Dyer
> > >CC: dbi-users@perl.org
> > >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.
> > >
> >
> >


------=_NextPart_000_6345_2e2d_57cd
Content-Type: text/plain; name="purge_images.pl"; format=flowed
Content-Transfer-Encoding: 8bit
Content-Disposition: attachment; filename="purge_images.pl"

#!/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;
}


------=_NextPart_000_6345_2e2d_57cd--

Re: DBD Oracle column type

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
> >To: Paul Dyer
> >CC: Tim.Bunce@pobox.com, dbi-users@perl.org
> >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
> >> >To: Paul Dyer
> >> >CC: dbi-users@perl.org
> >> >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;
> }
>