Oracle BLOB for base64 CLOB

Question

Is it possible to convert oracle oracle to base64 clob at one time?

as:

CREATE TABLE test ( image BLOB, imageBase64 CLOB ); INSERT INTO test(image) VALUES (LOAD_FILE('/full/path/to/new/image.jpg')); UPDATE test SET imageBase64 = UTL_ENCODE.base64_encode(image); commit; 

I know that I can add functions / Store proc to do the job. The performance aspect is very important, so I ask if there is a way to overcome the 32K limitation by simply pushing the data in the CLOB.

Answer 1

Provided that the stored procs will nevertheless be a viable alternative for you, here is one possible solution to your problem ...

First, let me make this nice base64encode() Tim Hall function into a procedure ...

 create or replace procedure base64encode ( i_blob in blob , io_clob in out nocopy clob ) is l_step pls_integer := 22500; -- make sure you set a multiple of 3 not higher than 24573 l_converted varchar2(32767); l_buffer_size_approx pls_integer := 1048576; l_buffer clob; begin dbms_lob.createtemporary(l_buffer, true, dbms_lob.call); for i in 0 .. trunc((dbms_lob.getlength(i_blob) - 1 )/l_step) loop l_converted := utl_raw.cast_to_varchar2(utl_encode.base64_encode(dbms_lob.substr(i_blob, l_step, i * l_step + 1))); dbms_lob.writeappend(l_buffer, length(l_converted), l_converted); if dbms_lob.getlength(l_buffer) >= l_buffer_size_approx then dbms_lob.append(io_clob, l_buffer); dbms_lob.trim(l_buffer, 0); end if; end loop; dbms_lob.append(io_clob, l_buffer); dbms_lob.freetemporary(l_buffer); end; 

The “trick” here is to directly use persistent large object locators in procedure / function calls. Why "stubborn"? Because if you create a function that returns a LOB, then a temporary LOB is created that is created in the background, and that means using a TEMP disk / memory and copying the contents of the LOB. For large large objects, this can mean a performance hit. To meet your requirements, to make it most efficient, you should avoid using the TEMP space. Therefore, a stored procedure should be used instead of a function for this approach.

Then, of course, the procedure should be served with constant locators of large objects. You must do this, again, using a stored procedure where you, for example. first insert the empty LOB (actually creating a new LOB locator) into the table, and then providing this newly created LOB locator to the base64 encoding procedure ...

 create or replace procedure load_and_encode_image ( i_file_name in varchar2 ) is l_input_bfile bfile := bfilename('DIR_ANYTHING', i_file_name); l_image_base64_lob test.imageBase64%type; l_image_raw test.image%type; begin insert into test(image, imageBase64) values (empty_blob(), empty_clob()) returning image, imageBase64 into l_image_raw, l_image_base64_lob; begin dbms_lob.fileopen(l_input_bfile); dbms_lob.loadfromfile( dest_lob => l_image_raw, src_lob => l_input_bfile, amount => dbms_lob.getlength(l_input_bfile) ); dbms_lob.fileclose(l_input_bfile); exception when others then if dbms_lob.fileisopen(l_input_bfile) = 1 then dbms_lob.fileclose(l_input_bfile); end if; raise; end; base64encode( i_blob => l_image_raw, io_clob => l_image_base64_lob ); end; 

Note: Of course, if you base64 encode only small files (the actual size depends on your PGA settings, I guess; a question for the database administrator, this is), then the function-based approach can be equally implemented than this one based on procedure. The Base64-encoded 200 MB file on my laptop took 55 seconds using the function + update approach, 14 seconds using the procedure. Not exactly a speed demon, so choose what suits your needs.

Note. . I believe that this procedure-based approach can be further accelerated by reading the file so that the pieces are briefly in a loop, base64-encoding the fragments into other memory fragments and adding them constant LOBs to the target. Therefore, you should make the workload even easier by avoiding re-reading the full contents of the test.image LOB using the base64encode() procedure.

Answer 2

This function got from here to do this job.

 CREATE OR REPLACE FUNCTION base64encode(p_blob IN BLOB) RETURN CLOB -- ----------------------------------------------------------------------------------- -- File Name : http://oracle-base.com/dba/miscellaneous/base64encode.sql -- Author : Tim Hall -- Description : Encodes a BLOB into a Base64 CLOB. -- Last Modified: 09/11/2011 -- ----------------------------------------------------------------------------------- IS l_clob CLOB; l_step PLS_INTEGER := 12000; -- make sure you set a multiple of 3 not higher than 24573 BEGIN FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_blob) - 1 )/l_step) LOOP l_clob := l_clob || UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_blob, l_step, i * l_step + 1))); END LOOP; RETURN l_clob; END; / 

Then the update may look like

 UPDATE test SET imageBase64 = base64encode(image); 

Note that perhaps the function should be optimized using the DBMS_LOB.APPEND function instead of this concatenation operator. Try it if you have performance problems.

Answer 3

I solved the same problem at work using the Java stored procedure. There is no chunking / contatenation VARCHAR2 in this approach, since base64's ability to encode / decode is built into Java, just writing an Oracle function that subtly wraps the Java method works well and is high-performance, because once you have executed it several times, JSM HotSpot compiles Java -process for low-level code (high performance, as a function of C). I will edit this answer later and add more details about this Java code.

But to backtrack just one step, the question is, why do you store this data in both BLOB encoding and base64 (CLOB)? Is it because you have customers who want to consume data in the latest format? I would prefer to store only the BLOB format. One reason for this is that the base64 encoded version may be twice the size of the original binary BLOB, so storing them probably means 3x storage.

One of the solutions that I implemented at work was to create my own Java base64_encode() function, which encodes binary → base64, and then uses this function to encode base64 on the fly during a request (this is not expensive). From the application / client side, you will be requesting something like SELECT base64_encode(image) FROM test WHERE ...

If the application code cannot be affected (for example, the COTS application), or if your developers are not enthusiastic about using the function, you can abstract it for them (since you use 11g +) using the VIRTUAL (calculated) column of the table that contains the calculated base64_encode(image) . It will function as a view since it will not physically store encoded CLOB files, but will generate them during the request. For any client, they cannot say that they are not reading a physical column. Another advantage is that if you ever update jpg (BLOB), the virtual CLOB immediately and automatically updates. If you ever need to insert / update / delete a huge batch of BLOBs, you would save 66% of the repetition / archiving volume without having to process all CLOBs.

Finally, for performance, make sure you are using SecureFile LOB (for both BLOB and CLOB). They are really much faster and better in almost every way.

UPDATE I found my code, at least the version using the Java stored procedure, to do the opposite (converting base64 encoded CLOB to my binary BLOB version). You could not write the opposite.

 --DROP FUNCTION base64_decode ; --DROP java source base64; -- This is a PLSQL java wrapper function create or replace FUNCTION base64_decode ( myclob clob) RETURN blob AS LANGUAGE JAVA NAME 'Base64.decode ( oracle.sql.CLOB) return oracle.sql.BLOB'; / -- The Java code that base64 decodes a clob and returns a blob. create or replace and compile java source named base64 as import java.sql.*; import java.io.*; import oracle.sql.*; import sun.misc.BASE64Decoder; import oracle.jdbc.driver.*; public class Base64 { public static oracle.sql.BLOB decode(oracle.sql.CLOB myBase64EncodedClob) { BASE64Decoder base64 = new BASE64Decoder(); OutputStream outstrm = null; oracle.sql.BLOB myBlob = null; ByteArrayInputStream instrm = null; try { if (!myBase64EncodedClob.equals("Null")) { Connection conn = new OracleDriver().defaultConnection(); myBlob = oracle.sql.BLOB.createTemporary(conn, false,oracle.sql.BLOB.DURATION_CALL); outstrm = myBlob.getBinaryOutputStream(); ByteArrayOutputStream byteOutStream = new ByteArrayOutputStream(); InputStream in = myBase64EncodedClob.getAsciiStream(); int c; while ((c = in.read()) != -1) { byteOutStream.write((char) c); } instrm = new ByteArrayInputStream(byteOutStream.toByteArray()); try // Input stream to output Stream { base64.decodeBuffer(instrm, outstrm); } catch (Exception e) { e.printStackTrace(); } outstrm.close(); instrm.close(); byteOutStream.close(); in.close(); conn.close(); } } catch (Exception e) { e.printStackTrace(); } return myBlob; } // Public decode } // Class Base64 ; / 

Answer 4

The easiest way I found that works with special characters (in your case you don't have this problem) is using dbms_lob.converttoclob.

Create an encapsulated procedure:

 CREATE OR REPLACE FUNCTION blob2clob(blob_i IN BLOB) RETURN CLOB IS l_clob CLOB; l_dest_offset NUMBER := 1; l_src_offset NUMBER := 1; l_amount INTEGER := dbms_lob.lobmaxsize; l_clob_csid NUMBER := nls_charset_id('WE8ISO8859P15'); --dbms_lob.default_csid; l_lang_context INTEGER := dbms_lob.default_lang_ctx; l_warning INTEGER; BEGIN --------------------------- -- Create Temporary BLOB -- --------------------------- dbms_lob.createtemporary(lob_loc => l_clob, cache => TRUE); -------------------------- -- Convert CLOB to BLOB -- -------------------------- dbms_lob.converttoclob(dest_lob => l_clob, src_blob => blob_i, amount => l_amount, dest_offset => l_dest_offset, src_offset => l_src_offset, blob_csid => l_clob_csid, lang_context => l_lang_context, warning => l_warning); -- RETURN l_clob; END blob2clob; 

Then you can use:

 blob2clob(utl_encode.base64_encode(image))