The commonly-in-character function of LOB fields in Oracle

Previously, the LOB fields were processed using the Dbms_lob package, and a recent document found that many of the commonly used character functions support lob fields.

Create a test table:

Sql> CREATE TABLE T_lob (ID number, CONTENTS CLOB);

Table created.

Sql> DECLARE

2 V_lob CLOB;

3 BEGIN

4 INSERT into T_lob

5 VALUES (1, Empty_clob ())

6 return CONTENTS into V_lob;

7 for I in 1..100 LOOP

8 Dbms_lob. Writeappend (V_lob, 32767, Lpad (' A ', 32767, ' a '));

9 End LOOP;

Ten end;

11/

Pl/sql procedure successfully completed.

Sql> SELECT ID, Dbms_lob. GetLength (CONTENTS) from T_lob;

ID Dbms_lob. GetLength (CONTENTS)

---------- ----------------------------

1 3276700

Sql> SELECT ID, LENGTH (CONTENTS) from T_lob;

ID LENGTH (CONTENTS)

---------- ----------------

1 3276700

The length function here is obviously not an implicit to_char conversion of the CLOB, because in that case, if the CLOB length is too large to make an error, otherwise CLOB automatically intercepts the first 4,000 characters and length returns 4000. The length function here obviously receives the parameters of the CLOB type.

In addition to length, commonly used InStr, substr, and so on are supported for LOB fields:

Sql> DECLARE

2 V_lob CLOB;

3 BEGIN

4 SELECT CONTENTS

5 into V_lob

6 from T_lob

7 WHERE ID = 1

8 for UPDATE;

9 Dbms_lob. Writeappend (V_lob, 1000, rpad (' BCDEFG ', 1000, ' A '));

Ten commits;

one end;

12/

This column more highlights: http://www.bianceng.cn/database/Oracle/

Pl/sql procedure successfully completed.

Sql> SELECT Dbms_lob. INSTR (CONTENTS, ' B ')

2 from T_lob

3 WHERE ID = 1;

Dbms_lob. INSTR (CONTENTS, ' B ')

----------------------------

3276701

Sql> SELECT INSTR (CONTENTS, ' B ')

2 from T_lob

3 WHERE ID = 1;

INSTR (CONTENTS, ' B ')

-------------------

3276701

Sql> SELECT Dbms_lob. SUBSTR (CONTENTS, 10, 3276701)

2 from T_lob

3 WHERE ID = 1;

Dbms_lob. SUBSTR (contents,10,3276701)

-------------------------------------------------------------------------------------------

Bcdefgaaaa

Sql> SELECT SUBSTR (CONTENTS, 3276701, 10)

2 from T_lob

3 WHERE ID = 1;

SUBSTR (contents,3276701,10)

--------------------------------------------------------------------------------

Bcdefgaaaa

In addition, Lpad, Rpad, and LTrim and RTrim also support the CLOB type:

Sql> SELECT LTRIM (RTRIM (CONTENTS, ' a '), ' a ')

2 from T_lob

3 WHERE ID = 1;

LTRIM (RTRIM (CONTENTS, ' a '), ' a ')

--------------------------------------------------------------------------------

Bcdefg

Sql> SELECT LENGTH (Lpad (CONTENTS, 10000000, ' B '))

2 from T_lob

3 WHERE ID = 1;

LENGTH (Lpad (contents,10000000, ' B '))

-----------------------------------

10000000

Character Connection Operation | | LOB types are also supported:

Sql> SELECT LENGTH (CONTENTS | | CONTENTS)

2 from T_lob

3 WHERE ID = 1;

LENGTH (contents| | CONTENTS)

--------------------------

6555400

If you want to find more detailed function descriptions that support lob, you can refer to the LOB-related documentation.