Methods in Oracle databases to search or operate lob data

Oracle Database can be used to search or operate lob data in many ways. We all know that there are four large objects in Oracle Database: lobs, the four types are blob, clob, and bfile and nclob. The following is a brief introduction to the lob data type.

Blob: Binary lob, Which is binary data. It can be up to 4 GB and stored in the database.

Clob: character lob, character data, up to 4 GB, stored in the database.

Bfile: binary file; read-only binary data stored outside the database. The maximum length is limited by the operating system.

Nclob: supports a clob column of the byte character set (nultibyte characterset.

Oracle Database developers often encounter lob data retrieval and operation. I will introduce some methods and skills for lob data processing in Oracle, and hope to help readers in future development.

Oracle can use multiple methods to retrieve or operate lob data. The common solution is to use the dbms_lob package.

Other methods include application programminginterfaces application interface and ociOracle call interface) Oracle call interface program.

1. In the Oracle development environment, we can use the dbms_lob package for processing! Dbms_lob package is powerful and simple to use. It can be used to read internal lob objects or process bfile objects. However, there is a difference between the two. When processing internal lob objects blob, clob), read and write operations can be performed, but when processing external lob object bfile, read operations can only be performed, write operations can be processed using pl/SQL. In addition, you can use SQL to process lob, but note that SQL can only process the entire lob and cannot operate on lob data slices.

The dbms_lob package has built-in functions such as read (), append, write (), erase (), copy (), getlength (), and substr, you can easily operate lob objects. We will not discuss it in depth here. Readers can refer to relevant books.

For pl/SQL, the following describes how to use dynamic pl/SQL statements to process clob objects and pass them as table names!

Example 1.

Dynamic PL/SQL. For CLOB field operations, you can pass the table name table_name, the unique table Identifier Field name field_id, And the clob field name field_name Record Number v_id to start processing the character location v_pos, passed string variable v_clob

Modify the PL/SQL PROCESS OF CLOB:

 
 

  
  
updateclob   
  
  
create or replace procedure updateclob(   
  
  
table_name in varchar2,   
  
  
field_id in varchar2,   
  
  
field_name in varchar2,v_id in number,   
  
  
v_pos in number,   
  
  
v_clob in varchar2)   
  
  
is   
  
  
lobloc clob;   
  
  
c_clob varchar2(32767);   
  
  
amt binary_integer;   
  
  
pos binary_integer;   
  
  
query_str varchar2(1000);   
  
  
begin   
  
  
pos:=v_pos*32766+1;   
  
  
amt := length(v_clob);   
  
  
c_clob:=v_clob;   
  
  
query_str :='select '||field_name||'from '||table_name||'   
  
  
where '||field_id||'= :id for update ';   
  
  
--initialize buffer with data to be inserted or updated   
  
  
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;   
  
  
--from pos position, write 32766 varchar2 into lobloc   
  
  
dbms_lob.write(lobloc, amt, pos, c_clob);   
  
  
commit;   
  
  
exception   
  
  
when others then   
  
  
rollback;   
  
  
end;  
 
 

L/usage instructions:

Before inserting or modifying, insert or modify other fields. Set the CLOB field to empty empty_clob (),

Then, call the above process to insert more than 2048 to 32766 characters.

If you want to insert more than 32767 characters, compile a loop to solve the problem. The above content is an introduction to Oracle databases that can use multiple methods to retrieve or operate lob data. I hope you will gain some benefits.