Oracle database, using stored procedures to bulk convert BLOB fields to JPG format

Environment Description:

Photo: User Picmgr stored in the production library, database version oracle10g;

Purpose: The photo is directed to the local path D:\image , and the storage format is jpg .

First step: Install the oracle11g database version locally

Note: Oracle version does not matter, 10g, 11g are OK,

The key is the database version.

Create a DBA user named Tonytonytony (the user name comes with you).

Use user Tonytonytony to create a directory (Image_dir),

A table (Get_image), and Databaselink (Dblinkpic).

Create or replace directory Image_dir as ' D:\image ';

--Create a table for temporary storage of photo data
CREATE TABLE Get_image
(
Num number,
XM VARCHAR2 (50),
Sfzh VARCHAR2 (18),
FileName VARCHAR2 (100),
Picture BLOB
);

Step two: Data preparation and photo export

　　Create or Replace procedure Pro01_auto_reload_data is
Begin
Delete Get_image; --Clear table information
Commit
Insert into Get_image (xm,sfzh,filename,picture)

Select A.xm,a.sfzh,a.sfzh| | ' _ ' | | a.xm| | '. JPG ', b.picture

From table01 A,[email protected] B

where A.xm=b.xm and A.sfzh=b.sfzh;
Commit
Update get_image set num=rownum;
Commit
Update get_image Set picture = null where length (picture) > ' 32767 ';

--The photo size is out of range, then set to empty, these photos need to be saved manually.
Commit
End Pro01_auto_reload_data;

　　

--The second stored procedure

Create or Replace procedure pro02_auto_download_image(i_num in Number,i_count on number) is
V_file Utl_file.file_type;

V_blob blob;
V_amount Binary_integer: = 32767;
V_pos INTEGER: = 1;
V_buffer RAW (32767);

V_blob_lenth INTEGER;
V_filename VARCHAR2 (50);

V_num INTEGER;
V_count INTEGER;

begin
v_num: = I_num;
V_count: = I_count;
While v_num <= V_count Loop
Select A.filename to V_filename from Get_image a where a.num = V_num;
Select A.picture to V_blob from Get_image a where a.num = V_num;
V_blob_lenth: = Dbms_lob.getlength (V_blob);
V_file: = Utl_file.fopen (' Image_dir ', V_filename, ' WB ', v_blob_lenth);
While V_pos < V_blob_lenth loop
Dbms_lob.read (v_blob,v_amount,v_pos,v_buffer);
Utl_file.put_raw (v_file,v_buffer,true);
V_pos: = V_pos + v_amount;
end Loop;
V_num: = v_num + 1;
V_pos: = 1;
Utl_file.fclose (v_file);
end Loop;
end Pro02_auto_download_image;

This second stored procedure has been able to guide the photo, but when used, found that if 6, 7 photos,

The speed will be very slow, in order to achieve the highest efficiency, I would like to export only one card at a time.

So I wrote a third stored procedure.

　　

Step three: Implement Batch Export

Create or Replace procedure Pro03_auto_run_one_by_one is

V_begin INTEGER: = 1;
V_end INTEGER: = 1;
V_count INTEGER;

begin
 --pro01_auto_reload_data;
  Select COUNT (*) into V_count from get_image;< /span>
  while V_begin <= V_count loop
    Pro02_auto_download_ Image (V_begin,v_end);
    v_begin: = V_begin + 1;
    v_end: = v_end + 1;
     end loop;
end Pro03_auto_run_one_by_one;

-Note: If the exported photo size is 0KB, you will need to do a manual save operation.

The way to do this is to first execute the first stored procedure and dump the photo information into the table Get_image of the local database.

Then execute a third stored procedure.

Oracle database, using stored procedures to bulk convert BLOB fields to JPG format