Oracle lob data type application instance

LOB (large object) is a data type used to store large objects. Each LOB can have 4 GB of data. Let's take a look at the oracle lob data type application instance, I hope this article will help you.

LOB has three types:

BLOB: Binary Large Object
CLOB: Character Large Object single-Character Large Object
Double-byte Character Large Object dual-byte Character Large Object
Large Data Types in oracle:
LONG: variable-length string data, which can be 2 GB at most. LONG has the VARCHAR2 column feature and can store LONG text. A table can contain at most one LONG column.
Long raw: Variable Length binary data, up to 2 GB

CLOB: The character big object Clob is used to store single-byte character data.
NCLOB: used to store multi-byte character data
BLOB: used to store binary data

BFILE: binary data stored in the file. The data in this file can only be read-only. However, this file is not included in the database.
The bfile field is actually stored in the file system. The field stores the file positioning pointer. bfile is read-only for oracle and does not participate in transaction control and data recovery.
　　
CLOB, NCLOB, and BLOB are all internal LOB (Large Object) types, with a maximum length of 4 GB and no restrictions on LONG columns.
BLOB is preferred for storing images, text files, and Word files. long raw is also good, but Long is the type to be discarded by oracle, and there are only one column of restrictions, therefore, BLOB is recommended.

Oracle stored procedure operation LOB field instance 1:
One problem encountered during the project is that there are multiple systems (0, A, B, C), where the central system (0) needs to extract other subsystems (A, B, c. The design principle is to create a stored procedure in the central system, connect to the subsystem through DBlink, and extract data (insert or update to the central system) according to the marked fields in the subsystem table ).

Stored Procedure syntax (pseudo code ):

The Code is as follows:

Copy code

CREATE OR REPLACE PROCEDURE "Pro_N_ROLES" (V_Filter in varchar2: = '') Is V_ROLE varchar2 (30 ); V_TREEAUTH varchar2 (4 ); V_ORI number (22 ); V_UPDATEFLAG varchar2 (2); // subsystem Id field V_ SQL varchar2 (4000 ); Type vv Is Ref cursor; CUR_DATA vv; Begin V_ SQL: = 'select ROLE, TREEAUTH, UPDATEFLAG From ROLES @ DB_N // The created Dblink Where UPLOADFLAG = ''y' | V_Filter; // subsystem Id field, Open CUR_DATA For v_ SQL; Loop Fetch CUR_DATA into V_ROLE, V_TREEAUTH, V_UPDATEFLAG; Exit When CUR_DATA % NOTFOUND; If V_UPDATEFLAG = 'n' then Begin INSERT INTO J_ROLES ( ROLE, TREEAUTH, SORI, // primary key value of the subsystem www. bKjia. c0m S_FLAG // subsystem ID ) VALUES ( V_ROLE, V_TREEAUTH, V_ORI, // primary key value of A system table 'A system' ) ; Commit; UPDATE ROLES @ DB_N Set uploadflag = 'n ', UPDATEFLAG = 'y' WHERE ORI = V_ORI; Commit; End; End if; If V_UPDATEFLAG = 'y' then Begin UPDATE J_ROLES Set role = V_ROLE, TREEAUTH = V_TREEAUTH, ORI = V_ORI, SORI = V_ORI, S_FLAG = 'a system' WHERE SORI = V_ORI And S_FLAG = 'a system '; Commit; UPDATE ROLES @ DB_N Set uploadflag = 'n ', UPDATEFLAG = 'y' WHERE ORI = V_ORI; Commit; End; End if; // 001 Add the updated CLOB field code here End loop; Close CUR_DATA; End Pro_N_ROLES;

There is no longvarchar type definition in oracle stored procedures. The defined varchar type variable can contain a maximum of 32767 characters. Therefore, CLOB type fields cannot be stored directly with Stored Procedure variables.
There are many methods on the Internet, some by creating temporary tables, and some by intercepting and merging. Based on the actual situation on our side, there are not many such fields, and the direct update method is adopted.

Therefore, the following code is added in the above comment "// 001 add update CLOB field code here:

 

The Code is as follows:	Copy code
UPDATE J_ROLES Set othersituation = ( SELECT OTHERSITUATION FROM ROLES @ DB_N WHERE ORI = V_ORI ), SUGGEST = ( SELECT SUGGEST FROM ROLES @ DB_N WHERE ORI = V_ORI ) WHERE SORI = V_ORI And S_FLAG = 'a system ';

Oracle9i database stores and reads clob files (PL/SQL scripts) instance 2 ()

The Code is as follows:

Copy code

// Create the lob_example1 table first Create table lob_example1 ( Id number (6) primary key, Name varchar2 (10 ), Resume clob ); // Insert data Insert into lob_example1 values (1, 'PIGS', empty_clob ()); Insert into lob_example1 values (2, 'Dog', empty_clob ()); Commit; // Create a directory and save it to the c root directory Create or replace directory docs as 'C :'; // Create a stored procedure for writing the file content to the database CLOB Create or replace procedure update_doc ( T_id number, Filename varchar2 ) As Lobloc clob; Fileloc bfile; Amount int; Src_offset int: = 1; Dest_offset int: = 1; Csid int: = 0; Lc int: = 0; Warning int; Begin Fileloc: = bfilename ('docs', filename ); Dbms_lob.fileopen (fileloc, 0 ); Amount: = dbms_lob.getlength (fileloc ); Select resume into lobloc from lob_example1 Where id = t_id for update; Dbms_lob.loadclobfromfile (lobloc, fileloc, amount, dest_offset, src_offset, csid, lc, warning ); Dbms_lob.fileclose (fileloc ); Commit; End; // Call the stored procedure and read the file into the database CLOB. These two files must exist under disk C. Call update_doc(1,'aa.csv '); Call update_doc(2,'bb.csv '); // View the file size in the row where id is 2 and 1 Select length (resume) from lob_example1 where id = 2; Select length (resume) from lob_example1 where id = 1; //////////////////////////////////////// //// // The file is already in the database // Read the file from the database clob Create or replace procedure get_doc ( T_id number, Filename varchar2 ) As Lobloc clob; Amount int; Offset int: = 1; Buffer varchar2 (2000 ); Handle utl_file.file_type; Begin Select resume into lobloc from lob_example1 where id = t_id; Amount: = dbms_lob.getlength (lobloc ); Dbms_lob.read (lobloc, amount, offset, buffer ); Handle: = utl_file.fopen ('docs', filename, 'w', 2000 ); Utl_file.put_line (handle, buffer ); Utl_file.fclose (handle ); End; /   // Call this process to read the file Call get_doc(1,'zz.csv ');

// Haha, the file is saved on drive C. Go and check it out.

// By the way, if the file is too large, the buffer zone will be too small. The buffer zone can be up to 36 KB. I don't know what to do if it is too large. I am working on it!

Author: Wolf 1