Oracle PL / SQL: CLOB Data Type Performance in PL / SQL

Question

If I use many CLOB variables in a PL / SQL stored procedure to store a lot of long string, are there any performance issues? Is CLOB length also a variable? Are there any known limitations / disadvantages for CLOBs instead of using varchar2 and long?

Answer 1

CLOB variables in length, yes. The upper limit varies depending on which version of Oracle is included and the database block size. For 11G, the limit is the “4G * DB_BLOCK_SIZE parameter value” (from 11G PL / SQL Language Reference ). VARCHAR2 values ​​are limited to 32767 bytes in PL / SQL.

CLOB VARCHAR2 PL/SQL, Google . , VARCHAR2 , CLOB ( , ), . , , VARCHAR2 CLOB, 1000 . Tom Kyte Runstats - Oracle.

: 32K, CLOB; , CLOB, VARCHAR2.

NB: CLOB , VARCHAR2 , Oracle, , desrecate VARCHAR2, , .

Answer 2

CLOB () , VARCHAR2. CLOB VARCHAR2, , .

, , .

1), 4000 , VARCHAR2, CLOB.

2), 32k PLSQL, VARCHAR2, CLOB.

, . VARCHAR2, CLOB, VARCHAR2.

, , LOB LOB ( ), PERMANENT ( ). CLOB PLSQL CLOB JAVA , CLOB Oracle. , , CLOB, CLOB, . , . , , ( ). , . , . , (, java) .

.

Answer 3

CLOB , . CLOB. :

• Relying on implicit CLOB transforms, CLOB ↔ VARCHAR2 tends to be slow.
• Work with the package is dbms_lobquite effective. Just do it in a loop:
  • Get a piece of data from your CLOB to the buffer
  • Use data from your buffer. Maybe turn it into another piece of data.
  • Positively write the converted fragment of data to the target destination CLOB.