كيف لي ان اخزن صور كثيرة مثل صور طلاب جامعة في قاعدة البيانات بحجم اقل

This note describes steps to Load a Binary file into a BLOB Column of a table, and also d
escribes how to unload contents of a blob column to a file on the filesystem.
Solution

Load contents of Binary File to a blob column of a table

Create these 2 tables

SQL> CREATE TABLE MYLOB ( ID NUMBER, PHOTO BLOB ) ;
SQL> CREATE TABLE TEMP_BFILE( B_FILE BFILE) ;

Insert bfile locator for the binary file into temp_file
SQL> insert into temp_bfile values ( bfilename('MYDIR','pic1.jpg'));

MYDIR is a directory object created with the CREATE DIRECTORY command.

Inserting lob into file
SQL> declare
tmp_blob blob default EMPTY_BLOB();
tmp_bfile bfile:=null;
dest_offset integer:=1;
src_offset integer:=1;
begin
select b_file into tmp_bfile from temp_bfile;
DBMS_LOB.OPEN (tmp_bfile, DBMS_LOB.FILE_READONLY);
dbms_lob.createtemporary(tmp_blob, TRUE);
DBMS_LOB.LOADBLOBFROMFILE(tmp_blob,tmp_bfile,DBMS_LOB.LOBMAXSIZE,dest_offset,src
_offset);
insert into mylob values(1,tmp_blob);
DBMS_LOB.CLOSE(tmp_bfile);
commit;
end;

Unload contents of a blob column of a table to a file on the filesystem

Offload the blob to a file
SQL> declare
tmp_blob blob default empty_blob();
begin
dbms_lob.createtemporary(tmp_blob, true);
select photo into tmp_blob from mylob;
sys.retrieve_lob_to_file (tmp_blob, 'MYDIR','mypic1.jpg');
end;
/

Note : MYDIR is a directory object created with the CREATE DIRECTORY command


Source for procedure retrieve_lob_to_file

CREATE OR REPLACE PROCEDURE retrieve_lob_to_file(temp_blob in BLOB, file_path in varchar2,
file_name in varchar2) IS
data_buffer RAW (32767);
position INTEGER := 1;
filehandle utl_file.file_type;
error_number NUMBER;
error_message VARCHAR2(100);
blob_length INTEGER;
chunk_size BINARY_INTEGER := 32767;
BEGIN
blob_length := dbms_lob.getlength(temp_blob);

filehandle := utl_file.fopen(file_path, file_name,'wb', 1024);

WHILE position < blob_length LOOP

dbms_lob.read (temp_blob, chunk_size, position, data_buffer);

utl_file.put_raw (filehandle, data_buffer);

position := position + chunk_size;

data_buffer := null;

END LOOP;

utl_file.fclose(filehandle);


EXCEPTION
WHEN OTHERS THEN
BEGIN
error_number := sqlcode;
error_message := substr(sqlerrm ,1 ,100);
dbms_output.put_line('Error #: ' || error_number);
dbms_output.put_line('Error Message: ' || error_message);
utl_file.fclose_all;
END;
END;/

Concerning the compression:

The UTL_COMPRESS package provides a set of data compression utilities.
The purpose of this package is to compress/uncompress RAW, BLOB, or BFILE data.
This package is compatible with common Lempel-Ziv utilities, such as Unix
'compress' and Windows 'Zip'.

In this package we use LZ_COMPRESS functions and procedures.
These functions and procedures compress data using Lempel-Ziv compression
algorithm. Lempel-Ziv is a universal lossless data compression
algorithm. There is not much information provided in oracle documentation
regarding Lempel-Ziv compression algorithm and its compression ratio.
So i searched on internet for exact compression ratio in Lempel-Ziv algorithm.
Below are the results of my search :

* The following table will show you an implementation of the Lempel-Ziv
algorithm

Lempel-Ziv

LaTeX file 44%
Speech file 64%
Image file 88%

Size of compressed file as percentage of the original file

* The large text file described in the Statistical Distributions of English Text
(containing the seven classic books with a 27-letter English alphabet) has a
compression ratio of 36.3% (original size=5086936 bytes, compressed size=
1846919,
using the Linux ``gzip'' program). This corresponds to a rate of
2.9 bits/character --- compared with the entropy rate of 2.3 bits/character
predicted by Shannon. This loss of optimality is most likely due to the finite
dictionary size.

* Many popular programs (e.g. Unix compress and uncompress, gzip and gunzip,
and Windows WinZip) are based on the Lempel-Ziv algorithm.

To know more about Lempel-Ziv Coding you can see following link :

http://www.data-compression.com/lossless.html

To know about UTL_COMPRESS package and how it works, you can use Note 249974.1 - 10G: Using the new UTL_COMP
RESS Oracle Supplied Package.***********************
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3
Information in this document applies to any platform.
Goal

This note describes steps to Load a Binary file into a BLOB Column of a table, and
also describes how to unload contents of a blob column to a file on the filesys
tem.
Solution

Load contents of Binary File to a blob column of a table

Create these 2 tables

SQL> CREATE TABLE MYLOB ( ID NUMBER, PHOTO BLOB ) ;
SQL> CREATE TABLE TEMP_BFILE( B_FILE BFILE) ;

Insert bfile locator for the binary file into temp_file
SQL> insert into temp_bfile values ( bfilename('MYDIR','pic1.jpg'));

MYDIR is a directory object created with the CREATE DIRECTORY command.

Inserting lob into file
SQL> declare
tmp_blob blob default EMPTY_BLOB();
tmp_bfile bfile:=null;
dest_offset integer:=1;
src_offset integer:=1;
begin
select b_file into tmp_bfile from temp_bfile;
DBMS_LOB.OPEN (tmp_bfile, DBMS_LOB.FILE_READONLY);
dbms_lob.createtemporary(tmp_blob, TRUE);
DBMS_LOB.LOADBLOBFROMFILE(tmp_blob,tmp_bfile,DBMS_LOB.LOBMAXSIZE,dest_offset,src
_offset);
insert into mylob values(1,tmp_blob);
DBMS_LOB.CLOSE(tmp_bfile);
commit;
end;

Unload contents of a blob column of a table to a file on the filesystem

Offload the blob to a file
SQL> declare
tmp_blob blob default empty_blob();
begin
dbms_lob.createtemporary(tmp_blob, true);
select photo into tmp_blob from mylob;
sys.retrieve_lob_to_file (tmp_blob, 'MYDIR','mypic1.jpg');
end;
/

Note : MYDIR is a directory object created with the CREATE DIRECTORY command


Source for procedure retrieve_lob_to_file

CREATE OR REPLACE PROCEDURE retrieve_lob_to_file(temp_blob in BLOB, file_path in var
char2, file_name in varchar2) IS
data_buffer RAW (32767);
position INTEGER := 1;
filehandle utl_file.file_type;
error_number NUMBER;
error_message VARCHAR2(100);
blob_length INTEGER;
chunk_size BINARY_INTEGER := 32767;
BEGIN
blob_length := dbms_lob.getlength(temp_blob);

filehandle := utl_file.fopen(file_path, file_name,'wb', 1024);

WHILE position < blob_length LOOP

dbms_lob.read (temp_blob, chunk_size, position, data_buffer);

utl_file.put_raw (filehandle, data_buffer);

position := position + chunk_size;

data_buffer := null;

END LOOP;

utl_file.fclose(filehandle);


EXCEPTION
WHEN OTHERS THEN
BEGIN
error_number := sqlcode;
error_message := substr(sqlerrm ,1 ,100);
dbms_output.put_line('Error #: ' || error_number);
dbms_output.put_line('Error Message: ' || error_message);
utl_file.fclose_all;
END;
END;
/

Please note the plsql code in the above examples is provided for learning purposes only however it has been tested on a 10.2.0.1 database
, and thorough testing is necessary before implementing this on production.

************************


Concerning the compression:

The UTL_COMPRESS package provides a set of data compression utilities.
The purpose of this package is to compress/uncompress RAW, BLOB, or BFILE data.
This package is compatible with common Lempel-Ziv utilities, such as Unix
'compress' and Windows 'Zip'.

In this package we use LZ_COMPRESS functions and procedures.
These functions and procedures compress data using Lempel-Ziv compression
algorithm. Lempel-Ziv is a universal lossless data compression
algorithm. There is not much information provided in oracle documentation
regarding Lempel-Ziv compression algorithm and its compression ratio.
So i searched on internet for exact compression ratio in Lempel-Ziv algorithm.
Below are the results of my search :

* The following table will show you an implementation of the Lempel-Ziv
algorithm

Lempel-Ziv

LaTeX file 44%
Speech file 64%
Image file 88%

Size of compressed file as percentage of the original file

* The large text file described in the Statistical Distributions of English Text
(containing the seven classic books with a 27-letter English alphabet) has a
compression ratio of 36.3% (original size=5086936 bytes, compressed size=
1846919,
using the Linux ``gzip'' program). This corresponds to a rate of
2.9 bits/character --- compared with the entropy rate of 2.3 bits/character
predicted by Shannon. This loss of optimality is most likely due to the finite
dictionary size.

* Many popular programs (e.g. Unix compress and uncompress, gzip and gunzip,
and Windows WinZip) are based on the Lempel-Ziv algorithm.

To know more about Lempel-Ziv Coding you can see following link :

http://www.data-compression.com/lossless.html

To know about UTL_COMPRESS package and how it works, you can use Note 249974.1 - 10G: Using the new U
TL_COMPRESS Oracle Supplied Package.