• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1025
  • Last Modified:

Long Raw consuming more space for JPG image

Hi Experts,

I am using oracle 9i, I want to save JPG image into my database, for this purpose I am using Long Raw,
the problem is this that my tablespace is consuming 1MB for one image to save (my actual image size is 75kb),
hence my tablespace filled out rapidly,

my question is;

1. Is Long Raw is suitable field category for JPG storage
2. Is there any other field category which could consume the actual size of the image.

Thanx.
0
Asif_Rafique
Asked:
Asif_Rafique
  • 3
  • 3
  • 3
1 Solution
 
GGuzdziolCommented:
I think You should try CLOB datatype.
0
 
GGuzdziolCommented:
I meant - BLOB.
0
 
schwertnerCommented:
BLOB is more appropriate.

Be aware that Oracle shows iritating length for the big columns.

If you ise BLOB use

/*********************************************************
** Returns the lenght of a BLOB
*********************************************************/
FUNCTION lob_l (Lob_loc BLOB)
RETURN integer IS
Length INTEGER;
BEGIN
/* Get the length of the LOB: */
length := DBMS_LOB.GETLENGTH(Lob_loc);
IF length IS NULL THEN
Length :=  0;
END IF;
RETURN length;
END lob_l;
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Asif_RafiqueAuthor Commented:
Dear GGuzdziol / Schwertner,

I also tried BLOB but there is no difference, it also consuming 1MB for one image, whereas the actual size of the image is less then 100kb, I am seeking the solution where oracle consume the actual size of the jpg image, otherwise just 100 images will cost me 1GB of harddisk space, which would be quite un-fair.
0
 
Asif_RafiqueAuthor Commented:
sorry 1000 images will consume 1gb, but it is still un-fair :)
0
 
schwertnerCommented:
I agree! there have to be something in your images that forces Oracle to use 1 GB.
If you use the package DBMS_LOB you can have control on the length.
Try to read your image somehow and to count the bytes.

How you load images in Oracle?
 May be you use a big buffer and do not truncate it.
Where you say Oracle the real length of the image?
I think there is error in your program used for loading
images. You have to control the lengths.
0
 
Asif_RafiqueAuthor Commented:
Dear Schwertner,

here is my scenario;

my table script;

CREATE TABLE TTPLSIGN.SHOLDER
  (
  BRCODE CHAR (3),
  FOLIO NUMBER (6),
  SIGN BLOB
 )
     TABLESPACE TTPLSIGN
     NOLOGGING
     PCTFREE 10
     PCTUSED 40
     INITRANS 1
     MAXTRANS 255
     STORAGE (
      INITIAL 64K
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      FREELISTS 1
      FREELIST GROUPS 1)
  LOB (SIGN) STORE AS SYS_LOB0000035929C00004$$
    (STORAGE (
      INITIAL 64K
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      FREELISTS 1
      FREELIST GROUPS 1)
     CHUNK 4096

     PCTVERSION 10)
 PARALLEL
/


I am using following statement in form 6i to save the image;

read_image_file('c:\sign\1.jpg','jpeg','sholder.sign');

Is anything wrong in this;
please advise me.
Thanx




0
 
schwertnerCommented:
I am afraid it is much more complex. I found this for you.

Any users who wish to upload binary data into BLOB columns
from Forms applications can use this document.


I have tried to make server-side package as flexible as possible
so you will need to pass tablename, pkcolumnname,blobcolumnname and
pkvalue into procedures.

On client-side, however, package variables as constants are used
to adopt the application to specific table. Variables are:

  p_tablename
  p_table_blob_colname
  p_table_pk_colname


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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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