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 the storage of character type and binary file type LOB data, and separately introduces the storage of binary type LOB data.

1. LOB data type classification in Oracle
1. Data Types:
① Character type:
CLOB: stores a large amount of single-byte character data.
NLOB: stores fixed-width, multi-byte character data.
② Binary type:
BLOB: stores large unstructured binary data.
③ Binary file type:
BFILE: stores binary files in operating system files outside the database. Path for storing files.

2. Storage:
① Stored in the internal tablespace:
CLOB, NLOB, and BLOB
② Point to an external operating system file:
BFILE

Ii. Big Object Data Input
1. Declare the LOB type column
/*
Conn scott/tiger;
Create TableSpace ts5_21
DataFile 'e: \ Oracle \ ts5_21.dbf'
Size 5 m;
*/
Create Table tLob (
No Number (4 ),
Name VarChar2 (10 ),
Resume CLob,
Photo BLob,
Record BFile
)
Lob (resume, photo) Store (
Tablespace ts5_21 -- specify the Tablespace to be stored
Chunk 6 k -- specify the data block size
Disable Storage In Row
);
2. Insert a large object Column
① Insert common column data first

② When a large object column is encountered, insert a blank constructor.
Role: empty_clob (), empty_nclob ()
Binary: empty_blob ()
Binary file type: The BFileName function points to an external file.
BFileName function:
BFileName ('logical directory name', 'filename ');
The logical directory name can only be in uppercase, because the data dictionary is stored in uppercase. Oracle is case sensitive.
During creation, you do not need to point the logical directory of the BFileName function to the physical path. When using this function, you can check whether the two are associated.
Example:
Insert Into tLob Values (1, 'gene ', empty_clob (), empty_blob (), bfilename ('mydir', 'img _ 0210. JPG '));

③ Associate the logical directory with the physical directory. (For binary files)
Grant create any directory permission
Grant create any directory to user name with admin option;
Associate logical directory and physical directory
Local
Create Directory logical Directory name As 'physical Directory of the file ';
Network:
Create Directory logical Directory name As '\ Host Name (IP) \ shared Directory ';
Example:
Create Directory MYDIR As 'e: \ Oracle ';

Insert example:
Insert into tlob values (1, 'gene ', 'clob large object columns', empty_blob (), bfilename ('mydir', 'img _ 0210. JPG '));

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;