Re: Retrieving Files Stored in LOBS / BLOBS

From: Anita M Wilcox <amw_at_world.std.com>
Date: Tue, 3 Nov 1998 19:11:19 GMT
Message-ID: <F1v1B2.154@world.std.com>

In article <36363955.E7179007_at_harris.com>, Brian Corey <bcorey_at_harris.com> wrote:
>I have stored a QuickTime movie in a simple table in Oracle 8.0.3 as a
>BLOB.
>
>I would like to setup a simple demonstration that would retrieve this
>movie from the Oracle Server running on Sun OS to my Oracle Client
>Running NT and "kickoff" the Windows QuickTime application, showing the
>movie. Any ideas/examples?
>
>The main part of the question is once a file is stored in a blob, how do
>I retrieve it back to the OS? The second part is how do I kick off an
>OS application, such as Quicktime, from PL/SQL or another Oracle
>application and pass it a value (such as this file)?
>

Here is some code I was given to do this in Perl using Win32::ODBC. NOTE: I have not tested this yet, so I don't know if it works.

##
#   get_file         
# 
#   Reads a file from the files table, 
#   associated with a file ID
#
#  Inputs:
#  $file_id - the unique id of the file
#   
#  Outputs:
#  %out_file - associative array containing the
#              field/value pairs, including the body
#              of the file itself
#
#  This function should return one set of name/value
#  pairs, since there should only be one set of 
#  file data per file_id 
#
#  usage:   %out_file = $ful->get_file($file_id);
#  returns: %results where $results{"file_name"} = "myanalysis.txt"
#                      and $results{"file_data"} = text_of_file
#            etc...the same for the rest of the data
#
#  This function returns undef (undefined value) when there is no data
#  resulting from the query
#

sub get_file
{
  my $self = shift;
  my ($file_id) = @_;

  # define local variables
  my ($statement, %results, $offset);   

  # build and execute the SQL statement   $statement = qq

               {
                 SELECT DBMS_LOB.GETLENGTH(file_data) as body_size 
                 FROM files 
                 WHERE files.file_id = $file_id
               };

  if ($db->Sql($statement))
  {
    # uh-oh, didn't work
    $db_err = $db->Error;
    $err->log_error($db_err);
  }
  else
  {

    # get the row of data resulting from the query     while ($db->FetchRow())
    {

      # read the resulting data into an array
      %fileinfo =$db->DataHash;
      $offset = 1;
      $packetsize = 512;
      
      while ($offset < $fileinfo{BODY_SIZE})
      {
        # now get a chunk of file data
        $statement = qq
                     {
                       SELECT DBMS_LOB.SUBSTR(file_data, $packetsize, $offset) as the_file
                       FROM files
                       WHERE files.file_id = $file_id
                     };
        if ($db->Sql($statement))
        {
          # uh-oh, didn't work
          $db_err = $db->Error;
          $err->log_error($db_err);
        }
        else
        {
          $db->FetchRow;
          %filedata = $db->DataHash;
          $file_chunks .= $filedata{THE_FILE};
        }
        
        $offset += $packetsize;
        $packetsize = $MAX_ODBC_PACKET;
      }
      
      $results{'file_data'} = $file_chunks;

Anita Received on Tue Nov 03 1998 - 13:11:19 CST