Solved

Long Raw consuming more space for JPG image

Posted on 2006-11-09
9
997 Views
Last Modified: 2008-03-17
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
Comment
Question by:Asif_Rafique
  • 3
  • 3
  • 3
9 Comments
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 17905933
I think You should try CLOB datatype.
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 17905936
I meant - BLOB.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 17905940
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
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 17906011
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Asif_Rafique
ID: 17907609
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
 

Author Comment

by:Asif_Rafique
ID: 17907665
sorry 1000 images will consume 1gb, but it is still un-fair :)
0
 
LVL 47

Expert Comment

by:schwertner
ID: 17912645
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
 

Author Comment

by:Asif_Rafique
ID: 17912994
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
 
LVL 47

Accepted Solution

by:
schwertner earned 250 total points
ID: 17913322
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now