Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

Oracle BLOB Locator

by Lhamo Latso (Scribe)
on Aug 19, 2005 at 23:29 UTC ( #485328=perlquestion: print w/replies, xml ) Need Help??
Lhamo Latso has asked for the wisdom of the Perl Monks concerning the following question:

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;
  • Replies are listed 'Best First'.
    Re: Oracle BLOB Locator
    by Dietz (Curate) on Aug 20, 2005 at 15:31 UTC
      $fil = ${ $hashref->{IMP_IMAGE} }; ######## ERROR OCCURS HERE

      You already know where your error lies. What have you tried so far?
      I bet your $fil will be uninitialized, therefore you can not bind your locator.
      Why? You're trying to dereference a dereferenced hash value as a scalar value.

      $fil = $hashref->{IMP_IMAGE};
      $fil = ${$hashref}{IMP_IMAGE};

      Turning on strict would have told you.

        The first error listed occurs on the subsequent sth2.bind_param with:
        $fil = $hashref->{IMP_IMAGE};
        and the second error occurs on the sth2.execute with:
        $fil = ${ $hashref->{IMP_IMAGE} };
        I think the second one in more correct, being that I actually could do the bind. The PLS-00306 error occurs on the sth2.execute statement, during the call to dbms_lob.filegetname. The first passed arguement is the lob locator, of which DBD::Oracle provides 2 ora_types: ora_clob and ora_blob. My lob locator is BFILE. I worked later last night and found that the Oracle column IDs CLOB, BLOB and BFILE are 112, 113 and 114. That makes the error message accurate in that I gave the wrong type.

        I tried just passing an integer 114, but the DBD or DBI interface complained and replaced it with SQLVARCHAR.

        I just checked the code. I had "use strict;" on.

        Thanks for the help.

    Log In?

    What's my password?
    Create A New User
    Node Status?
    node history
    Node Type: perlquestion [id://485328]
    Approved by Roger
    and all is quiet...

    How do I use this? | Other CB clients
    Other Users?
    Others studying the Monastery: (1)
    As of 2017-04-27 00:29 GMT
    Find Nodes?
      Voting Booth?
      I'm a fool:

      Results (497 votes). Check out past polls.