LOB processing in Oracle

It is mainly used to store database fields with a large amount of data. It can store up to 4 GB of unstructured data. This section describes how to store LOB data of character type and binary file type separately.

It is mainly used to store database fields with a large amount of data. It can store up to 4 GB of unstructured data. This section describes how to store LOB data of character type and binary file type separately.

3. Reading and operating big object data: DBMS_LOB package
DBMS_LOB package: contains the process and function for processing large objects.
/*
Insert into tlob values (1, 'gene ', 'clob large object columns', empty_blob (), bfilename ('mydir', 'img _ 0210. JPG '));
Insert into tlob values (2, 'jack', 'clob large object columns ', empty_blob (), bfilename ('mydir', 'img _ 0210. JPG '));
Insert into tlob values (3, 'Mary ', 'Big object column clob', empty_blob (), bfilename ('mydir', 'img _ 0210. JPG '));
*/
1. Process and function for reading big object data
①: DBMS_LOB.Read (): Read data of the specified length from the LOB to the buffer zone.
DBMS_LOB.Read (LOB data, specifying the length, starting position, storage return LOB type value variable );
Example:
Declare
VarC clob;
VRStr varchar2 (1000 );
Ln number (4 );
Strt number (4 );
Begin
Select resume into varC from tlob where no = 1;
Ln: = DBMS_LOB.GetLength (varC );
Strt: = 1;
DBMS_LOB.Read (varC, ln, Strt, vRStr );
DBMS_output.put_line ('Return: '| vRStr );
End;

②: DBMS_LOB.SubStr (): The function used to extract the substring from the LOB data.
DBMS_LOB.SubStr (LOB data, specify the extraction length and start position ):
Example:
Declare
VarC clob;
VRStr varchar2 (1000 );
Ln number (4 );
Strt number (4 );
Begin
Select resume into varC from tlob where no = 1;
Ln: = 4;
Strt: = 1;
VRStr: = DBMS_LOB.SubStr (varC, ln, Strt );
DBMS_output.put_line ('result: '| vRStr );
End;

③: DBMS_LOB.InStr (): The function used to find the position of the substring from the LOB data.
DBMS_LOB.InStr (LOB data, sub-string );
Example:
Declare
VarC clob;
VSubStr varchar2 (1000 );
VRStr varchar2 (1000 );
Ln number (4 );
Begin
Select resume into varC from tlob where no = 1;
VSubStr: = 'large object ';
Ln: = DBMS_LOB.InStr (varC, vSubStr );
DBMS_output.put_line ('location: '| ln );

VRStr: = DBMS_LOB.SubStr (varC, Length (vSubStr), ln );
DBMS_output.put_line ('location: '| ln | 'length:' | Length (vSubStr) | 'substring:' | vRStr );
End;

④: DBMS_LOB.GetLength (): return the function of the length of the specified LOB data.
DBMS_LOB.GetLength (LOB data );

⑤: DBMS_LOB.Compare (): checks whether two large objects are equal. The return value 0 is equal, and-1 is not equal.
DBMS_LOB.Compare (LOB data, LOB data );
Example:
Declare
VarC1 clob;
VarC2 clob;
VarC3 clob;
Ln number (4 );
Begin
Select resume into varC1 from tlob where no = 1;
Select resume into varC2 from tlob where no = 2;
Select resume into varC3 from tlob where no = 3;
Ln: = DBMS_LOB.Compare (varC1, varC1 );
DBMS_output.put_line ('comparison result: '| ln );
Ln: = DBMS_LOB.Compare (varC2, varC3 );
DBMS_output.put_line ('comparison result: '| ln );
End;
2. Process of operating big object data
The Operation will change the original data in the database. You need to add the Updata lock to lock the specified data column and submit the transaction after the modification.

①: DBMS_LOB.Write (): Write a specified amount of data to LOB.
DBMS_LOB.Write (written to LOB, write length (written to LOB data), Write Start position (written to LOB), write to LOB data );
Example:
Declare
VarC clob;
VWStr varchar2 (1000 );
VStrt number (4 );
Ln number (4 );
Begin
VWStr: = 'clob ';
Ln: = Length (vWStr );
VStrt: = 5;
Select resume into varC from tlob where no = 1 for update;
DBMS_LOB.Write (varC, ln, vStrt, vWStr );
DBMS_output.put_line ('rewrite result: '| varC );
Commit;
End;

②: DBMS_LOB.Append (): The process of appending the specified LOB data to the specified LOB data.
DBMS_LOB.Append (LOB data, LOB data );
Example:
Declare
VarC clob;
VAStr varchar2 (1000 );
Begin
VAStr: = ', which is a large object column ';
Select resume into varC from tlob where no = 1 for update;
DBMS_LOB.Append (varC, vAStr );
Commit;
DBMS_output.put_line ('append result: '| varC );
End;

③: DBMS_LOB.Erase (): The process of deleting part of the data at the specified position in the LOB data;
DBMS_LOB.Erase (LOB data, specifying the deletion length and starting to delete the location );
Example:
Declare
VarC clob;
Ln number (4 );
Strt number (4 );
Begin
Ln: = 1;
Strt: = 5;
Select resume into varC from tlob where no = 1 for update;
DBMS_LOB.Erase (varC, ln, strt );
Commit;
DBMS_output.put_line ('erasure result: '| varC );
End;

④: DBMS_LOB.Trim (): The process of truncating part of the data from the first position to the specified length;
DBMS_LOB.Trim (LOB data, truncation length );
Example:
Declare
VarC clob;
Ln number (4 );
Begin
Ln: = 4;
Select resume into varC from tlob where no = 1 for update;
DBMS_LOB.Trim (varC, ln );
COMMIT;
DBMS_output.put_line ('truncation result: '| varC );
End;

⑤: DBMS_LOB.Copy (): copy the source LOB to the target LOB from the specified location;
DBMS_LOB.Copy (source LOB, target LOB, copy source LOB length, copy to target LOB start location, copy source LOB start location)
Example:
Declare
VDEST_LOB clob;
VSRC_LOB clob;
AMOUNT number;
DEST_OFFSET number;
SRC_OFFSET number;
Begin
Select resume into vDEST_LOB from tlob where no = 1 for update;
Select resume into vSRC_LOB from tlob where no = 2;

AMOUNT: = DBMS_LOB.GetLength (vSRC_LOB );
DEST_OFFSET: = DBMS_LOB.GetLength (vDEST_LOB) + 1;
SRC_OFFSET: = 1;

DBMS_LOB.Copy (vDEST_LOB, vSRC_LOB, AMOUNT, DEST_OFFSET, SRC_OFFSET );
DBMS_output.put_line ('Copy result: '| vDEST_LOB );
End;