味味A

請看下面範例：

CREATE TABLE ADM.X3

( CLOB_TEXT  CLOB);



Insert into X3

   (CLOB_TEXT)

 Values

   ('測試一下);   

Insert into X3

   (CLOB_TEXT)

 Values

   ('');      

COMMIT;



select * from x3 
where DBMS_LOB.INSTR(clob_text,'測',1,1)>0;

結果：

那如何得知長度呢？ DBMS_LOB.GETLENGTH()，若是NULL則GETLENGTH也是得到NULL，若有需要轉換的人可以用NVL。

select clob_text,

NVL(DBMS_LOB.GETLENGTH(CLOB_TEXT),0) AS LENG 

from x3;

結果：

基本功：

LONG is a variable length PL/SQL datatype and database column type that is used to store character data. In PL/SQL the LONG is like a VARCHAR2 datatype except that is can store 32780 bytes (VARCHAR2 is limited to 2000 bytes). The LONG database column has a width of 2GB.

• 32767 bytes for PL/SQL variables
  
• 2GB bytes for database columns

Use the CLOB datatype to store large blocks of single-byte character data "out of line" inside the database. This means that when a table has a CLOB column, a row of data for that table contains a pointer or locator to the actual location of the CLOB data (so it is not "in line" with the other column values of the row).

A CLOB variable contains a locator, which then points to the large block of single-byte character data. CLOBs can be up to 4 gigabytes in size, and they participate fully in transactions. In other words, any changes you make to a CLOB (via the DBMS_LOB built-in package) can be rolled back or committed along with other outstanding changes in your transaction. CLOB locators cannot, however, span transactions or sessions.

Use the BLOB datatype to store large binary objects "out of line" inside the database. This means that when a table has a BLOB column, a row of data for that table contains a pointer or a locator to the actual location of the BLOB data (so it is not "in line" with the other column values of the row).

A BLOB variable contains a locator, which then points to the large binary object. BLOBs can be up to 4 gigabytes in size, and they participate fully in transactions. In other words, any changes you make to a BLOB (via the DBMS_LOB built-in package) can be rolled back or committed along with other outstanding changes in your transaction. BLOB locators cannot, however, span transactions or sessions.

其他參考：

• 讀寫Oracle 10g的CLOB欄位| 簡睿隨筆@新世界


• Oracle DBMS_LOB Reference