Using Oracle Java stored procedures to export blob Images

Address: http://fengwen.iteye.com/blog/580323

Problem: a xx Settlement System requires transaction statistics from another system, which stores and exports images for viewing.

 

The Slice directly exists in the database as blob. I thought it was a Java lob operation problem, but because it was two times of development, it must follow the previous rules, that is

 

Some services are implemented by stored procedures. Their selection is to use the dbms_lob and utl_file packages. It is no problem to Import images to the database, but it is always incorrect during export.

 

The size of the exported file is inconsistent with that of the imported image.

In fact, this is an old 9i problem (why do I have to say it again ),Oracle DBA tips cornerIs described as follows:

It shoshould be noted that in oracle9IThis PL/SQL procedure does not work with all binary files. this is due to Oracle bug (bug #: 2883782 ). the PL/SQL procedure that I use to write binary (raw) data out is
Utl_file.put_raw. This procedure, alongUtl_file.get_raw, Was introduced in Oracle 9IRelease 2 as previous versions
Utl_fileOnly worked with text files.

In oracle9IThere is currently a restriction of a maximum of 32 K that can be written
Put_rawUnless you insertNew LineCharacters in between the data. In oracle10GThere is a new binary mode. When files are opened with this mode,
"WB", Any amount of raw data can be written without the need for new lines. In short, this is a bug that can bite you if your binary files do not have
New LineCharacter within the raw data in yourMax_linesize Buffer. If you do get bit by this bug in oracle9I, There is no solution that I have found other than writing it in Java.

When I originally started writing and testing the procedure, I was using a fairly small image file (~ 1kb). This worked fine. I later tested a large PDF file (~ 3 MB) and it failed (after writing only a small portion of the file) with the following exception:

BEGIN Write_BLOB_To_File; END;*ERROR at line 1:ORA-29285: file write errorORA-06512: at "SYS.UTL_FILE", line 18ORA-06512: at "SYS.UTL_FILE", line 1007ORA-06512: at "SCOTT.WRITE_BLOB_TO_FILE", line 74ORA-06512: at line 1

Again, using the newBinary writeMode in oracle10GWhen opening binary files shocould fix this.

 

NOTE: If fopen is enabled in W mode, put_raw will automatically Press enter. Even if only one character is written, the system will convert the linefeed 0a to 0d0a because Windows uses 0d0a as the linefeed, that is to say, the carriage return is automatically added. the WB mode is used to enable it in 10 Gb. In this mode, the conversion of "Carriage Return" and "linefeed" is not performed. that is to say, there is a problem in reading and writing binary files in 9i ....

Unfortunately:

1. It is impossible to upgrade the database because of this feature. 9i is indeed a version with a bug ......

2. Because it is two times of development, the implementation of stored procedures is restricted, so pure JDBC or spring's javaslelobhandler cannot be used.

 

My solution is as follows:

1. Use a few external tools, such as lobs_win32.exe (http://www.dbatools.net/software/lobs.zip) to achieve lob operations, the operation is simple, but not conducive to integration with the current project, and there are also restrictions based on the OCI Method

2. in fact, PL/SQL does not support I/O operations well (9i also has a version that will cause PL/SQL to be unable to access the file system due to invalid utl_file_dir settings ), java's Io operations are much richer. so why don't we learn from each other? That is to say, you can write a Java stored procedure and use simple I/O operations to replace the PL/SQL package.

 

============================================================= ==========

 

First, create a test table, trigger, and the corresponding dir:

SQL code
 

1. Create Table image_lob
2. (I _id number primary key not null,
3. I _img blob not null );
5. Create or replace trigger tri_img
6. Before insert on fw. image_lob
7. For each row
8. Begin
9. Select FW. se_test.nextval into: New. I _id from dual;
10. End;
12. Create or replace directory dir_images as 'C: \ picture ';

Create Table image_lob (I _id number primary key not null, I _img blob not null); Create or replace trigger tri_imgbefore insert on fw. image_lobfor each rowbegin select FW. se_test.nextval into: New. I _id from dual; end; create or replace directory dir_images as 'C: \ picture ';

 

Next, write the process of importing images:

PL/SQL code
 

1. Create or replace procedure p_img_insert (v_filename varchar2)
2. Is
3. V_bfile bfile; -- file pointer
4. V_blob blob;
5. Dir constant varchar2 (20): = 'dir _ images '; -- file storage directory
6. Begin
7. /* Use the empty_blob () function to initialize columns of the Blob type to be empty for future filling */
8. Insert into FW. image_lob (I _img)
9. Values (empty_blob () return I _img into v_blob;
11. V_bfile: = bfilename (Dir, v_filename); -- get the Directory and file pointed to by the Locator
12. If (dbms_lob.fileexists (v_bfile )! = 0) then -- if the file pointed to by the file locator exists
13. Dbms_lob.fileopen (v_bfile, dbms_lob.file_readonly); -- open the target file
15. /* Load file data to the specified lob type variable */
16. Dbms_lob.loadfromfile (v_blob, v_bfile, dbms_lob.getlength (v_bfile ));
18. Dbms_lob.fileclose (v_bfile); -- close the file
19. Commit;
20. Dbms_output.put_line ('image read from '| dir |' directory '| v_filename | 'insert into table ');
22. Else -- if the file pointed to by the file locator does not exist
23. Dbms_output.put_line ('file not found ');
24. End if;
25. Exception when others then
26. Dbms_output.put_line (sqlerrm );
27. End;

Create or replace procedure p_img_insert (v_filename varchar2) is v_bfile bfile; -- file pointer v_blob; Dir constant varchar2 (20): = 'dir _ images '; -- file storage directorybegin/* initialize blob columns to be empty through empty_blob () function. */insert into fw. image_lob (I _img) values (empty_blob () return I _img into v_blob; v_bfile: = bfilename (Dir, v_filename); -- Obtain the Directory and file if (v_bfile) pointed to by the positioner )! = 0) then -- if the file pointed to by the file locator has dbms_lob.fileopen (v_bfile, dbms_lob.file_readonly ); -- open the target file/* load the file data to the specified lob type variable */dbms_lob.loadfromfile (v_blob, v_bfile, dbms_lob.getlength (v_bfile); dbms_lob.fileclose (v_bfile ); -- close the file commit; dbms_output.put_line ('image read from '| dir |' directory '| v_filename | 'insert to table '); else -- if the file pointed to by the file locator does not exist dbms_output.put_line ('file not found '); end if; Exception when others then dbms_output.put_line (sqlerrm); end;

 

Test insertion:

SQL code
 

1. SQL> set serveroutput on
2. SQL> exec FW. p_img_insert (v_filename => '1. jpg ');
4. Image 1. jpg has been read from the dir_images directory and inserted into the table
6. PL/SQL procedure successfully completed

SQL> set serveroutput onsql> exec FW. p_img_insert (v_filename => '1. JPG '); Image 1 has been read from the dir_images directory. JPG inserts PL/SQL procedure successfully completed into the table

 

The data in the table is as follows:

SQL code
 

1. SQL> select * from FW. image_lob;
3. I _id I _img
4. ---------------
5. 21 <blob

SQL> select * from FW. image_lob; I _id I _img ---------- ----- 21 <blob

 

Next we will focus on loading and compiling the Ava program, which is easy to perform I/O operations:

PL/SQL code
 

1. Create or replace and compile Java source named "sp_exp_blob"
2. Package util;
4. Import java. Io. fileoutputstream;
5. Import java. Io. outputstream;
7. Import oracle. SQL. Blob;
9. Public class oracleblobutil {
11. Public static void exp (BLOB blob, string expdir) throws exception {
12. Byte [] bt = blob. getbytes (1, (INT) blob. Length ());
13. Outputstream OS = new fileoutputstream (expdir );
14. OS. Write (BT );
15. OS. Flush ();
16. OS. Close ();
17. }
18. }

Create or replace and compile Java source named "sp_exp_blob" aspackage util; import Java. io. fileoutputstream; import Java. io. outputstream; import oracle. SQL. blob; public class oracleblobutil {public static void exp (BLOB blob, string expdir) throws exception {byte [] bt = blob. getbytes (1, (INT) blob. length (); outputstream OS = new fileoutputstream (expdir); OS. write (BT); OS. flush (); OS. close ();}}

 

Creation process:

 

PL/SQL code
 

1. Create or replace procedure p_exp_blob (v_blob blob, v_exp_dir varchar2)
2. As language Java name 'util. oracleblobutil. exp (Oracle. SQL. Blob, java. Lang. String )';

Create or replace procedure p_exp_blob (v_blob blob, v_exp_dir varchar2) as language Java name 'util. oracleblobutil. exp (Oracle. SQL. Blob, java. Lang. String )';

 

Test the export function:

PL/SQL code
 

1. Declare
2. V_blob FW. image_lob. I _img % type;
3. Begin
4. Select I _img into v_blob from FW. image_lob where I _id = 21;
5. <Strong> <span style = "color: # 0000ff"> dbms_java.grant_permission ('FW ', 'sys: Java. io. filepermission ', 'c:/picture/Hangzhou exp.jpg', 'write'); </span> </strong>
   
6. FW. p_exp_blob (v_blob => v_blob, v_exp_dir => 'C:/picture/1_exp.jpg ');
7. End;

Declare v_blob FW. image_lob. I _img % type; begin select I _img into v_blob from FW. image_lob where I _id = 21;Dbms_java.grant_permission ('FW ', 'sys: Java. Io. filepermission', 'c:/picture/1_exp.jpg ', 'write ');FW. p_exp_blob (v_blob => v_blob, v_exp_dir => 'C:/picture/1_exp.jpg '); end;

 

If it succeeds, you will see 1_exp.jpg under C:/picture.

Note that the blue line of code sets the write permission on the file. If it does not exist, Java will appear. security. accesscontrolexception: The permission ....., can refer to the foreigner's post http://cn.forums.oracle.com/forums/thread.jspa? Threadid = 832298 & tstart = 0 & messageid = 3149561 #3149561. For more information, see Oracle Java security documentation.

Of course, users can also be granted permissions separately.

 

SQL code
 

1. SQL> connect sys/is311027 @ Feng as sysdba;
2. Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
3. Connected as sys
5. SQL> call dbms_java.grant_permission ('FW ', 'sys: Java. Io. filepermission', 'c:/picture/1_exp.jpg ', 'write ');
   
7. Method called

SQL> connect sys/is311027 @ Feng as sysdba; connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 connected as syssql> call dbms_java.grant_permission ('FW ', 'sys: Java. io. filepermission ', 'c:/picture/export exp.jpg', 'write'); method called

 

 

The final conclusion is as follows. With the JVM introduced since 8i, we can use Java and PL/SQL to complement each other and use various advantages of Java to expand database applications.

 

And make full use of the efficient memory recovery technology and thread management capabilities, instead of limited to PL/SQL. As an example in this article, PL/SQL and

 

Java can coexist in a harmonious manner in the same application, while the two can coexist. At the same time, we can use the best methods of both worlds to develop better database applications.

 

Use.