Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

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 surveying the Monastery: (4)
    As of 2018-02-21 04:17 GMT
    Find Nodes?
      Voting Booth?
      When it is dark outside I am happiest to see ...

      Results (274 votes). Check out past polls.