Oracle BLOB Locator

[Lhamo Latso]

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 either get the error:

Can't bind a reference (OCILobLocatorPtr=SCALAR(.. or,

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

Thanks for any help. This is rather Oracle specific, or not?

This runs on Perl 5.8, Perl-DBI 1.32, DBD::Oracle 1.16, Linux 2.4.21-32.

my $dbh = DBI->connect( "dbi:Oracle:".$ENV{"ORACLE_SID"}, "u", "p", 
          { 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_heade
+rs ".
                     "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 loc
+ator
       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 loc
+ator
           or die "\nCan't prepare SQL statement:\n$DBI::errstr\n";
       $fil = ${ $hashref->{IMP_IMAGE} };   ######## ERROR OCCURS HERE
       $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
           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;
   }

Update: I received feedback from the author of DBD::Oracle. "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."

I was passing a LOB locator as a parameter to back to the database, unsuccessfully. The alternative I took, was to create a stored procedure on the database to do that call for me and return the directory path and filename. Here is the changed code and procedure:

my $sth2 = $dbh->prepare("BEGIN cbtno.purge_images".
                "(:fil, :dir_alias, :name); END;")
    or die "\nCan't prepare SQL statement:\n$DBI::errstr\n";
sth2->bind_param(":fil", $hashref->{IMP_ID} );
sth2->bind_param_inout(":dir_alias", \$dir_alias, 100);
$sth2->bind_param_inout(":name", \$name, 100);
$sth2->execute

# NOT Perl
CREATE OR REPLACE PROCEDURE purge_images
  (imp_id_in      IN NUMBER,
   dir_out        OUT VARCHAR2,
   file_out       OUT VARCHAR2,
   debug_in       IN BOOLEAN  := false
  ) IS
    fil            BFILE;
    dir_alias      VARCHAR2(30);
    dir_path       VARCHAR2(255);
    name           VARCHAR2(2000);
BEGIN
  dir_out  := 'none';
  file_out := 'none';
  FOR l_rec in (select imp_imh_id, imp_page_no, imp_image
                from daybreak.image_pages
                where imp_id = imp_id_in) LOOP
    fil := l_rec.imp_image;

    dbms_lob.filegetname(fil, dir_alias, name);

    SELECT directory_path INTO dir_path
      FROM dba_directories
      WHERE directory_name = dir_alias;

    dir_out  := dir_path;
    file_out := name;
    END LOOP;
END;