[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2524
  • Last Modified:

Storing a pdf file in a blob column without creating a directory

I currently run a report using forms 6i which generates a pdf file. I need to store this pdf file into a blob column without saving this pdf to a directory. The reason why I dont store the pdf file in a directory is because my database is on a different server and my pdf file could be stored on a different server.

Please let me know how I could do this.
0
sahiti66
Asked:
sahiti66
1 Solution
 
schwertnerCommented:
There are several steps involved in doing this:
- a server-side package that will allow reading and writing BLOB chunks
- a client-side interface to use above interface
- bin2hex.c to allow to read binary files from Forms using text_io
- hex2bin.c to allow to write binary files
 
Make sure the table with BLOB column ( test_table) is populated with  
empty_blob() locator, like:
   insert into test_table values (1,empty_blob())
 
 
1. Below is the server-side package called lob_client_operations
 
 
 
create or replace package lob_client_operations as
 
/* FUNCTION GET_LOB_SIZE
   This function returns a given column's size in blob column stored
   in database.
   Passed parameters are:
          p_tablename ........  name of the table where blob column is stored
          p_pkcolname ........  name of the PK column in the table ,
                                assuming only one-column PK definition
                                for composite  PKs this might be needed to be  
                                changed
          p_textcolname ......  name of the column holding BLOB data
          p_docid       ......  value, in CHAR, of the simple PK value of the  
                                document that we need to look for.
*/
function get_lob_size(p_tablename in varchar2,
                      p_pkcolname in varchar2,
                      p_textcolname in varchar2,
                      p_docid in varchar2) return number;
 
 
 
/* PROCEDURE GET_LOB_CHUNK
   This procedure gets a chunk of BLOB data from table
   Passed parameters are:
          p_tablename ........  name of the table where blob column is stored
          p_pkcolname ........  name of the PK column in the table ,
                                assuming only one-column PK definition
                                for composite  PKs this might be needed to be  
                                changed
          p_textcolname ......  name of the column holding BLOB data
          p_docid       ......  value, in CHAR, of the simple PK value of the  
                                document that we need to look for.
          amount        ......  amount of data to be read
          offset        ......  offset of data with respect to BLOB data in table
          buff          ......  data returning as read
*/
 
procedure get_lob_chunk ( p_tablename in varchar2,
                          p_pkcolname in varchar2,
                          p_textcolname in varchar2,
                          p_docid in varchar2,
                          amount IN OUT NUMBER,
                          offset     IN NUMBER,  
                          buff      OUT RAW);
 
 
 
/* PROCEDURE PUT_LOB_CHUNK
   This procedure putss a chunk of BLOB data back into the table
   Passed parameters are:
          p_tablename ........  name of the table where blob column is stored
          p_pkcolname ........  name of the PK column in the table ,
                                assuming only one-column PK definition
                                for composite  PKs this might be needed to be  
                                changed
          p_textcolname ......  name of the column holding BLOB data
          p_docid       ......  value, in CHAR, of the simple PK value of the  
                                document that we need to look for.
          amount        ......  amount of data to be written
          offset        ......  offset of data with respect to BLOB data in table
          buff          ......  data to be put into database
*/
 
procedure put_lob_chunk ( p_tablename in varchar2,
                          p_pkcolname in varchar2,
                          p_textcolname in varchar2,
                          p_docid in varchar2,
                          amount IN NUMBER,
                          offset     IN NUMBER,  
                          buff      IN RAW);
end;
/
 
 
create or replace package body lob_client_operations  
as  
procedure get_lob_chunk ( p_tablename in varchar2,
                          p_pkcolname in varchar2,
                          p_textcolname in varchar2,
                          p_docid in varchar2,
                          amount IN OUT NUMBER,
                          offset     IN NUMBER,  
                          buff      OUT RAW)
   is
   
     l_lob BLOB;
     query_string varchar2(500)  := 'select '||p_textcolname||
                                    ' from '||p_tablename||
                                    ' where '||p_pkcolname||
                                    ' = '''||p_docid||'''';  
   BEGIN
          execute immediate query_string into l_lob;
         DBMS_LOB.READ(l_lob, amount, offset, buff);
   END;
 
 
procedure put_lob_chunk ( p_tablename in varchar2,
                          p_pkcolname in varchar2,
                          p_textcolname in varchar2,
                          p_docid in varchar2,
                          amount IN NUMBER,
                          offset     IN NUMBER,  
                          buff      IN RAW)
   is
   
     l_lob BLOB;
     query_string varchar2(500)  := 'select '||p_textcolname||
                                    ' from '||p_tablename||
                                    ' where '||p_pkcolname||
                                    ' = '''||p_docid||''' for update';  
   BEGIN
      execute immediate query_string into l_lob;
      DBMS_LOB.WRITE(l_lob, amount, offset, buff);
      COMMIT;
   END;
 
 
function get_lob_size(p_tablename in varchar2,
                      p_pkcolname in varchar2,
                      p_textcolname in varchar2,
                      p_docid in varchar2) return number is
 
     l_lob BLOB;
     v_length number := 0;
     query_string varchar2(500)  := 'select '||p_textcolname||
                                    ' from '||p_tablename||
                                    ' where '||p_pkcolname||
                                    ' = '''||p_docid||'''';  
   BEGIN
      execute immediate query_string into l_lob;
      v_length := dbms_lob.getlength(l_lob);
      return(v_length);
   END;
 
end;
/
 
 
 
2. Below is the Forms client-side package that will handle both upload and download
   activities.
 
 
 
PACKAGE lob_forms_client IS
/* package by ncomert@tr 27.11.2001 */
 
-- below lines necessary for locating C-coded  exe files  
-- they are located in c:\ by default
  hex2bin_filename varchar2(40) := 'c:\hex2bin.exe';
  bin2hex_filename varchar2(40) := 'c:\bin2hex.exe';
-- a temporary file is needed to process data  
-- the name and the location does not matter
  temp_filename    varchar2(40) := 'c:\a.out';
   
-- below lines represent tablename, blob column in the table
-- and Pk for the table,respectively.
-- they are just constants to be used in a sample application where the following  
-- table exist:
--     create table test_table(id number(5) primary key,
--                             document blob);
 
  p_tablename varchar2(30) := 'test_table';         -- table name holding BLOB data
  p_table_blob_colname varchar2(30) := 'document';  -- BLOB column in the table named above
  p_table_pk_colname   varchar2(30) := 'id';        -- name of the PK column in the table
 
-- constants for retrieving blob  
-- below variable chunk_size can be increase to increase performance
  chunk_size number := 4096;
  raw_chunk_size number := 256;
   
/* PROCEDURE GET_LOB
   This procedure downmloads BLOB data from table into OS file
   Passed parameters are:
          p_docid   ........  document id (PK) of the row in the table  
          p_filename  ......  name of the file where the BLOB data will be retrieved.
*/
  procedure get_lob(p_docid in varchar2,p_filename in varchar2);
 
 
 
/* PROCEDURE PUT_LOB
   This procedure downmloads BLOB data from table into OS file
   Passed parameters are:
          p_docid   ........  document id (PK) of the row in the table  
          p_filename  ......  name of the file where the BLOB data will be written back
*/
  procedure put_lob(p_docid in varchar2,p_filename in varchar2);
 
END;
 
 
 
 
 
 
PACKAGE BODY lob_forms_client IS
  procedure get_lob(p_docid in varchar2, -- document PK column , always in varchar2
                    p_filename in varchar2) -- filename in OS to write the blob data into
  is
   myfile   text_io.file_type;
   buff     VARCHAR2(10000);
   len      NUMBER := lob_forms_client.chunk_size;
   filesize number := 0;
   offset   NUMBER := 1;
   
  begin  
   -- open file for write operation
   myfile := text_io.fopen(lob_forms_client.temp_filename,'W');
   -- get file size  
   filesize := lob_client_operations.get_lob_size(p_tablename  => lob_forms_client.p_tablename,
                            p_pkcolname  => lob_forms_client.p_table_pk_colname,
                            p_textcolname => lob_forms_client.p_table_blob_colname,
                            p_docid => p_docid);
   LOOP
           -- get blob data chunk from stored procedure
          lob_client_operations.get_lob_chunk(p_tablename  => lob_forms_client.p_tablename,
                  p_pkcolname  => lob_forms_client.p_table_pk_colname,
                  p_textcolname => lob_forms_client.p_table_blob_colname,
                  p_docid => p_docid,
                  amount => len,
                  offset => offset,  
                  buff   => buff);
      -- Increase the offset pointer.
 
      offset := offset + len;                          
      len := lob_forms_client.chunk_size;      
      -- Write the data to a file after converting the buffer to hex.
      TEXT_IO.PUTF(MYFILE, rawtohex(buff));
 
      -- Compare offset with the size of the file.
      EXIT WHEN filesize < offset;
   END LOOP;
   -- close file
   TEXT_IO.FCLOSE(MYFILE);      
   -- run file conversion
   host(lob_forms_client.hex2bin_filename||' '||
        lob_forms_client.temp_filename||' '||p_filename);
  end;
   
 
procedure put_lob(p_docid in varchar2, -- document PK column , always in varchar2
                  p_filename in varchar2) -- filename to upload into blob column
  is
   myfile   text_io.file_type;
   buff     VARCHAR2(1000);
   len      NUMBER ;
   filesize number := 0;
   offset   NUMBER := 1;
   buffer_size number := 512;
   raw_buffer raw(512);
   i number;    
   
  begin  
  -- first runfile conversion       
      host(lob_forms_client.bin2hex_filename||' '||p_filename||' '||
        lob_forms_client.temp_filename);
  -- dummy loop to spend some time
  for i in 1..1000 loop
         null;
  end loop;
   
  -- open text file for read process
  myfile := text_io.fopen(lob_forms_client.temp_filename,'R');
  LOOP
      -- read file
      TEXT_IO.get_line(MYFILE, buff);
      raw_buffer := hextoraw(buff);
      len := utl_raw.length(raw_buffer);
      -- put raw data chunk into blob column           
      lob_client_operations.put_lob_chunk(p_tablename  => lob_forms_client.p_tablename,
                  p_pkcolname  => lob_forms_client.p_table_pk_colname,
                  p_textcolname => lob_forms_client.p_table_blob_colname,
                  p_docid => p_docid,
                  amount => len,
                  offset => offset,  
                  buff   => raw_buffer);
      -- Increase the offset pointer.
      offset := offset + len;                          
      -- reset buffer size.
  END LOOP;
   -- close text file  for read.
  TEXT_IO.FCLOSE(MYFILE);      
  EXCEPTION  
      WHEN NO_DATA_FOUND THEN  
         TEXT_IO.FCLOSE(myfile);  
  end;
END;
 
 
 
 
3. Below is the source code for bin2hex.exe,in C language.
 
 
#include <stdio.h>
 
main (int argc, char **argv)
{
      FILE *filein, *out;
      unsigned char b=0;
      long int i=0;
 
        //Check the number of arguments.
        if (argc < 3 || argc > 3)
        {  
         printf("bin2hex file1.ext file2.ext is the correct syntax.\n");
             exit(0);
        }  
 
       filein=fopen (argv[1], "rb");
       if (!filein)  
       {
         perror ("Error opening source file\n");
       return 1;
       }
       out=fopen (argv[2], "w");
       if (!out)  
        {
         perror ("Error opening destination file\n");
           return 2;
       }
       fread (&b, 1, 1, filein);
       while (!feof (filein)) {
         fprintf (out, "%02X", b);
         if (!(++i%256))  // here print a CR after 256 chars to ease line read  
                  fprintf (out, "\n", i);
             fread (&b, 1, 1, filein);
      }
       fclose (out);
       fclose (filein);
       return 0;
}
 
 
 
4. Below is the source code for hex2bin.exe,in C language.
 
// File hex2bin.c
 
#include<stdio.h>
#include<string.h>
 
main(int argc , char *argv[])
{
   FILE *fp1 , *fp2; //File pointers
   char str[3];
   int value;
 
   //Check the number of arguments.
   if (argc < 3 || argc > 3)
   {  
     printf("hex2bin file1.ext file2.ext is the correct syntax.\n");          
     exit(0);
   }  
 
   //Open the source file to read from.
   fp1 = fopen(argv[1],"r");  
 
   //Open file binary file to write to.
   fp2 = fopen(argv[2],"wb");  
 
   //Loop until the end of the file.
   while(!feof(fp1))  
   {
 
      //Read the hexadecimal value(1).
      str[0] = fgetc(fp1);  
 
      //Read the hexadecimal value(2).          
      str[1] = fgetc(fp1);  
      if (!feof(fp1))
      {
         //Convert the first byte to the binary value.
         if (str[0] > 64 && str[0] < 71)  
            str[0] = str[0] - 55;
         else
            str[0] = str[0] - 48;
 
         //Convert the second byte to the binary value.
         if (str[1] > 64 && str[1] < 71)
            str[1] = str[1] - 55;
         else
            str[1] = str[1] - 48;  
 
         //Convert the hex value to binary (first & second byte).
         value = str[0] * 16 + str[1];
 
         //Write the binary data to the binary file.
         fprintf(fp2,"%c",value);  
      }
   }
   fclose(fp1);
   fclose(fp2);
}
 
 
 
 
5. To call the process from Forms interface, say behind a button that  
 says "Load Document" then the code would be something like:
 
lob_forms_client.put_lob(p_docid => '1',
        p_filename  => 'c:\dummy\mydoc.doc');
 
so the above code will insert file c:\dummy\mydoc.doc into  
database to row with PK column value = 1  
 
Same is possible for reading BLOB documents as well:
Below code will read BLOB data from table with PK value=20 into
file named c:\dummy\outfile.doc
 
lob_forms_client.get_lob(p_docid => '20',
        p_filename  => 'c:\dummy\outfile.doc');

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now