I was not allowed to store the silhouette image in a production database table or reference it from the production file system with a BFILE. I also wanted to avoid the extra dependencies and supporting structures required for those options. Basically, the only practical option left was to break down the image into its binary representation and store the hexadecimal value in a string array declared in a PL/SQL function that decodes the hexadecimal array and returns the silhouette image as a BLOB.
Although there exists a number of free online tools to encode the image and determine its hexadecimal equivalent, I decided to use our development database and explore what could be done with the Oracle built-in utilities like the UTL_RAW and DBMS_LOB packages and the BFILENAME, RAWTOHEX, and HEXTORAW functions. Please excuse the lack of proper exception handling around the file I/O operations; this example is just that and not meant for production use.
/* 11g script to encode the image as hexadecimal from a BFILE */
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
cChunkSize CONSTANT INTEGER := 2000;
-- assumption here is that the file has already been placed in the file system directory mapped
-- through the Oracle directory object specified by cDirectory, that the file is named as specified by cFilename,
-- and that the necessary file system permissions and Oracle privileges have been granted
cDirectory CONSTANT VARCHAR2(15) := 'DATA_PUMP_DIR';
cFilename CONSTANT VARCHAR2(40) := 'rodin-thinker-silhouette_21022921.jpg';
lAmount BINARY_INTEGER;
lOffset INTEGER;
lBuffer RAW(2000); -- size should match cChunkSize
TYPE tRawArray IS TABLE OF RAW(2000); ---- size should match lBuffer
lRawArray tRawArray;
lBFile BFILE;
lFileSize INTEGER;
BEGIN
-- open the file and get the file size
lBFile := BFILENAME(directory => cDirectory,filename => cFilename);
DBMS_LOB.FILEOPEN(file_loc => lBFile);
lFileSize := NVL(DBMS_LOB.GETLENGTH(file_loc => lBFile),0);
IF (lFileSize > 0) THEN
-- initialize collection
lRawArray := tRawArray();
--set the offset to the first byte in the file
lOffset := 1;
-- read and feed chunks of the file into the raw array
LOOP
lAmount := cChunkSize;
DBMS_LOB.READ(file_loc => lBFile,
amount => lAmount,
offset => lOffset,
buffer => lBuffer);
-- add collected buffer data as a new element in the raw array
lRawArray.EXTEND;
lRawArray(lRawArray.COUNT) := lBuffer;
-- increment the offset by the number of bytes specified by cChunkSize
lOffset := lOffset + cChunkSize;
-- exit loop when offset has been set to or beyond the size of the file
EXIT WHEN lOffset >= lFileSize;
END LOOP;
-- each line of output will be a maximum of cChunkSize*2
-- since hex values are 2 characters per byte and as such
-- be aware that cChunkSize should not exceed a size of 16383 since
-- the maximum line size in the output is 32767 bytes
FOR i IN 1..lRawArray.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(lRawArray(i));
END LOOP;
END IF;
-- close the file
DBMS_LOB.FILECLOSE(file_loc => lBFile);
END;
/
I could have used the DBMS_LOB.LOADBLOBFROMFILE procedure to upload the image in one fell swoop, but I still would have had to chunk the binary data into the RAW array from the target BLOB variable using the DBMS_LOB.SUBSTR function. I instead chose to "read and feed" each chunk directly from the file into the array. I also could have used a larger chunk size up to 16383 bytes (and would have if the file were much larger) but wanted to stay within the bounds of SQL size constraints in case I ever want to use these scripts to store chunks of RAW data in a table.
An output of 12 lines of hexadecimal will be generated, and as I found out, the RAWTOHEX function isn't required to explicitly convert the RAW data into printable hexadecimal strings. The first 11 lines contain 4000 characters each, and the 12th line will be 3870 characters long (truncated here for brevity):
FFD8F.....E0874
DD512.....EC77BB1639.....08076
FF00B.....6AD73
8ACAF.....7A501
D9410.....F00C1
5FAC9.....8F251
9D2CE.....6FC98
1FE95.....2C4A9
29A0F.....ACD22
C7BE4.....61821
02248.....FFFD9
For the decoding function that reconstructs the silhouette image from these chunks, the hexadecimal output will be copied and pasted into 12 elements of a VARCHAR2 array with one line per element in proper order:
/* 11g function to decode the hexadecimal chunks and return a BLOB of the silhouette image */
CREATE OR REPLACE FUNCTION fncSilhouetteImage
RETURN BLOB
IS
lRetVal BLOB;
-- size should match cChunkSize from hex encoding script
lRawChunk RAW(2000);
-- size should match max line size of hex output
TYPE tHexArray IS TABLE OF VARCHAR2(4000);
lHexArray tHexArray := tHexArray(); -- initialized
BEGIN
-- file is just under 24kb in size, so I only need 12 elements in the hex array
lHexArray.EXTEND(12);
-- the hex output from the encoded raw array
lHexArray(1) := 'FFD8F.....E0874';
lHexArray(2) := 'DD512.....EC77B';
lHexArray(3) := 'B1639.....08076';
lHexArray(4) := 'FF00B.....6AD73';
lHexArray(5) := '8ACAF.....7A501';
lHexArray(6) := 'D9410.....F00C1';
lHexArray(7) := '5FAC9.....8F251';
lHexArray(8) := '9D2CE.....6FC98';
lHexArray(9) := '1FE95.....2C4A9';
lHexArray(10) := '29A0F.....ACD22';
lHexArray(11) := 'C7BE4.....61821';
lHexArray(12) := '02248.....FFFD9';
-- initialize lob
DBMS_LOB.CREATETEMPORARY(lob_loc => lRetVal,
cache => FALSE,
dur => DBMS_LOB.CALL);
-- decode the byte array and reconstruct its original LOB form
FOR i IN 1..lHexArray.COUNT
LOOP
DBMS_LOB.WRITEAPPEND(lob_loc => lRetVal,
amount => UTL_RAW.LENGTH(HEXTORAW(lHexArray(i))),
buffer => lHexArray(i));
END LOOP;
-- OR try the UTL_RAW.CONCAT function for kicks
lRetVal := UTL_RAW.CONCAT(r1 => lHexArray(1),
r2 => lHexArray(2),
r3 => lHexArray(3),
r4 => lHexArray(4),
r5 => lHexArray(5),
r6 => lHexArray(6),
r7 => lHexArray(7),
r8 => lHexArray(8),
r9 => lHexArray(9),
r10 => lHexArray(10),
r11 => lHexArray(11),
r12 => lHexArray(12));
RETURN lRetVal;
END;
/
In the function, I demonstrate two methods for decoding the hexadecimal data into the original image. Both methods take advantage of the implicit conversion between the RAW data type and hexadecimal strings, so explicit conversions using the HEXTORAW function were removed; some purists out there may want to chastise me for that, so be it.
So, the first method loops over the array appending each element to a BLOB variable using the DBMS_LOB.WRITEAPPEND procedure. In the call to that procedure, it should be noted that the standard LENGTH function instead of the UTL_RAW.LENGTH function could have been used on the hex array element in the amount argument; however, a division of the standard LENGTH result by 2 would be required since the input parameter of the standard function is a VARCHAR2 whereas the input parameter of the UTL_RAW function is a RAW. Both data types are implicitly converted by the two functions, but only the decoded length of the RAW value will work for the DBMS_LOB.WRITEAPPEND procedure for BLOBs (pat yourselves on the back, purists).
In the second method, I use the UTL_RAW.CONCAT function returning a RAW value which is the concatenation of the RAW input arguments provided, the hex array elements in this case. Again for the purists out there, I originally had passed the RAW concatenation to the TO_BLOB function for explicit conversion, but the implicit conversion between RAW and BLOB data types was just too convenient for this example. Coincidentally, the UTL_RAW.CONCAT function has only twelve parameters, so its practical use may be limited by the size of your array.
No comments:
Post a Comment