1. Reason
BLOB refers to a binary large object, which is the abbreviation of the English Binary Large Object.
In many cases, we access BLOB's byte data through other programming languages (such as Java) and perform byte-level operations.
But sometimes the workload is very small, and it feels more troublesome to develop a program specifically for BLOB byte-level operations. So I studied how to operate BLOB's byte data directly in Oracle stored procedures.
2. Method
2.1 Basic Operation
Using the length function, you can get the byte length of the blob. likev_len := length(i_blob); 。
Like strings (such as varchar2, etc.), blob isnullWhen length returns the valuenull. Therefore, it is recommended to add nvl to make a conversion, such asv_len := nvl(length(i_blob), 0); 。
To avoidnullFor the problem, you can use the empty_blob function, which is used to return a blob of length 0. likev_blob := empty_blob(); 。
The blog returned by empty_blob is just an initialization, and it cannot modify the byte data. Therefore, we need to use dbms_lob.createtemporary to create a temporary blob that can perform byte data operations. likedbms_lob.createtemporary(v_blob, TRUE); 。
2.2 DBMS_LOB package
In order to facilitate the use of BLOB types, Oracle officially provides the DBMS_LOB package, which provides many tool functions. For example, we used the createtemporary function before.
The process provided by DBMS_LOB is-
- APPEND: Add content from the source LOB to the destination LOB.
- CLOSE: Close the already opened LOB.
- CREATETEMPORARY: Create a temporary LOB in the user's temporary tablespace.
- FILECLOSE: Closes the OS file pointed to by the open BFILE locator.
- FILECLOSEALL: Close all BFILE files that have been opened in the current session.
- FILEEXISTS: Determine whether the OS file corresponding to file_loc exists, 1: exists. 0: Does not exist.
- FILEGETNAME: Get the directory alias and file name corresponding to the BFILE locator.
- FILEISOPEN: Determine whether the OS file corresponding to BFILE is open.
- FREETEMPORARY: Release temporary LOBs in the default temporary tablespace.
- FILEOPEN: Open the file.
- GETCHUNKSIZE: When creating a table containing CLOB/BLOB columns, by specifying the CHUNK parameter, you can specify that the number of bytes to be allocated to manipulate the LOB (an integer multiple of the database size) by default is the size of the data block.
- COPY: Copy data from the source LOB to the destination LOB.
- ERASE: Delete all or part of the content in the LOB.
- TRIM: Reduces the LOB value to the specified length.
- WRITE: Write data to the LOB.
- INSTR: Returns the specific position where a specific style data appears N times from a certain offset position of the LOB.
- IDOPEN: Determine whether the LOB is on, open: 1, not open: 0.
- ISTEMPORARY: Determines whether the locator is a temporary LOB.
- LOADFROMFILE: Copy some or all of the contents of BFILE to the target LOB variable.
- LOADBLOBFROMFILE: Load the BFILE data into the BLOB and obtain the latest offset position after loading.
- OPEN: Open LOB, open_mode (read-only: dbms_lob.lob_readonly, write: dbms_lob.lob_readwrite).
- COMPARE: Compare whether some or all values of LOBs of the same data type are the same.
- GETLENGTH: Get the length of the LOB.
- READ: Read data from LOB.
- SUBSTR: The same method as the character processing function SUBSTR is used.
- WRITEAPPEND: Write buffer data to the tail of the LOB.
With the DBMS_LOB package, it is more convenient for (variable-level) BLOB operations. For example, if we want to splice the contents of two blobs into 1 blob in succession, we can do this -
function test_blob_join(i_blob1 in blob, i_blob2 in blob) return blob is
v_rt blob := empty_blob();
begin
dbms_lob.createtemporary(v_rt, TRUE); -- Assign temporary blobs.
dbms_lob.append(v_rt, i_blob1);-- Splicing i_blob1.
dbms_lob.append(v_rt, i_blob2);-- Splicing i_blob2.return v_rt;
end;
Can test this way—
select PKG_FINGER.test_blob_join(hextoraw('0102'), hextoraw('A1A2')) from dual;
It returns the byte data of the blob01 02 A1 A2. Verification passed.
2.3 Byte-level operations and RAW data types
There is no problem with variable-level operations that implement BLOB. So, how to implement byte-level operation of BLOB?
For example - How to intercept a string of bytes starting from a position in the blob? Replace bytes at each position in blob? Append byte data at the end of the blob?
In fact, the substr, write, and writeappend of dbms_lob can solve these three problems respectively.
Then, after a closer look, you will find that these processes use the RAW type.
For RAW types, many data say this -
RAW type is a data type used in Oracle to save bit strings. It is similar to CHAR and is declared using RAW (L) method, up to a maximum length32767 bytes.The relationship between RAW and BLOB-
- A piece of byte data in a BLOB is of type RAW. For example, the data obtained through dbms_lob.substr.
- Secondly, a certain byte data in the BLOB can be replaced based on the RAW data. That is to use dbms_lob.write.
- RAW data can be added at the end of the BLOB. That is, use dbms_lob.writeappend.
- Oracle supports implicit transformation of RAW to BLOB type.
Observe the help document of dbms_lob and you will find that each function has both a BLOB version and a CLOB version. Moreover, when the CLOB version uses the VARCHAR2 type, its BLOB version is RAW type. That is, RAW is similar to VARCHAR2, one is a byte string and the other is a string.
Many commonly used string functions are also valid for RAW. For example length and sustr .
RAW can be represented by hexadecimal strings. Therefore, the hextoraw function is generally used to convert the hexadecimal string to RAW. For examplehextoraw('A1A2') 。
RAW can be regarded as a hexadecimal string. So when using the length function for raw variables, its return value is 2 times the byte length (because for hexadecimal strings, one byte is represented by 2 hexadecimal characters). The same situation exists for functions such as substr.
You can also use rawtohex to convert RAW type data into a hexadecimal string (VARCHAR2).
2.4 UTL_RAW package
The length result of RAW mentioned above is 2 times the byte length, which is inconvenient. At this time, the UTL_RAW package can be used. For example, the result of utl_raw.length is the byte length.
Common UTL_RAW processes include—
- length: length calculation function to get the length of a raw type variable, unit in bytes
- concat: a splicing function, used to splice two raw type variables
- substr: Get substring function
- bit_and: bit and function
- bit_or: bit or function
- bit_xor: bit xor function
- overlay: Assign a value to the specified byte
- cast_to_raw: string to RAW
- cast_to_varchar2: RAW to varchar2
- cast_to_nvarchar2: RAW to nvarchar2
- cast_to_number: RAW to number
- cast_from_number: number to RAW
- cast_to_binary_integer: RAW to binary_integer
- cast_from_binary_integer:binary_integer to RAW
3. Usage experience
3.1 32-bit integer conversion function
At first I didn't know that binary_integer was a 32-bit integer. So I wrote the conversion function of 32-bit integers and RAW. Although they are not available now, I think they are still suitable for application demonstration.
-- Convert a number to a big-endian 32-bit integer of type raw(4).function TO_INT32BE(i_src in number) return raw is
v_src number;
v_hexstr varchar2(20);
v_rt raw(4);
begin
v_src := i_src;
if (v_src<0) then
v_src:=v_src + 4294967296; -- In order to support negative numbers.end if;
v_hexstr := '0000000' || trim(to_char(v_src,'XXXXXXXX'));
v_hexstr := substr(v_hexstr, length(v_hexstr)-7, length(v_hexstr));
v_rt := hextoraw(v_hexstr);
return v_rt;
end;
-- Convert a number to a little-endian 32-bit integer of type raw(4).function TO_INT32LE(i_src in number) return raw is
v_src number;
v_hexstr varchar2(20);
v_rt raw(4);
begin
v_src := i_src;
if (v_src<0) then
v_src:=v_src + 4294967296; -- In order to support negative numbers.end if;
v_hexstr := '0000000' || trim(to_char(v_src,'XXXXXXXX'));
v_hexstr := substr(v_hexstr, length(v_hexstr)-7, length(v_hexstr));
v_hexstr := substr(v_hexstr, 7, 2)
|| substr(v_hexstr, 5, 2)
|| substr(v_hexstr, 3, 2)
|| substr(v_hexstr, 1, 2)
;
v_rt := hextoraw(v_hexstr);
return v_rt;
end;
-- Convert a big-endian 32-bit integer stored in raw(4) to a number. The value range is 0~4294967295.function FROM_INT32BE(i_src in raw) return number is
v_src raw(8);
v_hexstr varchar2(20):='';
v_rt number:=0;
begin
if ( (nvl(length(i_src), 0)<=0) ) then
return v_rt;
end if;
if (length(i_src) >= 8) then -- length and substr both regard 1 byte of raw as 2 (hexadecimal) characters.
v_src:= substr(i_src, 1, 8);
v_hexstr := rawtohex(v_src);
else
v_hexstr := '000000' || rawtohex(i_src);
v_hexstr := substr(v_hexstr, length(v_hexstr)-7, length(v_hexstr));
end if;
v_rt := to_number(v_hexstr,'XXXXXXXX');
return v_rt;
end;
-- Convert a small-endian 32-bit integer stored in raw(4) to a number. The value range is 0~4294967295.function FROM_INT32LE(i_src in raw) return number is
v_src raw(8);
v_hexstr varchar2(20):='';
v_rt number:=0;
begin
if ( (nvl(length(i_src), 0)<=0) ) then
return v_rt;
end if;
if (length(i_src) >= 8) then -- length and substr both regard 1 byte of raw as 2 (hexadecimal) characters.
v_src:= substr(i_src, 1, 8);
v_hexstr := rawtohex(v_src);
else
v_hexstr := rawtohex(i_src) || '000000';
v_hexstr := substr(v_hexstr, 1, 8);
end if;
v_hexstr := substr(v_hexstr, 7, 2)
|| substr(v_hexstr, 5, 2)
|| substr(v_hexstr, 3, 2)
|| substr(v_hexstr, 1, 2)
;
v_rt := to_number(v_hexstr,'XXXXXXXX');
return v_rt;
end;
3.2 Append 32-bit integer to blob
Many times, you need to append a 32-bit integer to the blob. Now using the above function, you can do this-
v_tempraw := TO_INT32LE(nvl(i_int32, 0));
dbms_lob.writeappend(v_blob, 4, v_tempraw);
(over)
References
- Oracle Database Online Documentation / DBMS_LOB . /cd/E11882_01/timesten.112/e21645/d_lob.htm#TTPLP600
- Oracle Database Online Documentation / UTL_RAW . /cd/E11882_01/timesten.112/e21645/u_raw.htm#TTPLP072
- Wangzai and Xiao Mantou "DBMS_LOB"./wang-chen/p/
- Hornsey "Basic Operation Functions and Usage Examples of Oracle RAW Type"./nalw2012/article/details/72466256
- jimeper "Conversion between ORACLE decimal and hexadecimal"./jimeper/archive/2013/01/24/