DECLARE
    some_clob CLOB;
BEGIN
    IF some_clob IS NULL THEN
        dbms_output.put_line('a'); --NULL 表示該 LOB 字段或變量中連 LOB 指針都沒有
    ELSIF dbms_lob.getlength(some_clob) = 0 THEN
        dbms_output.put_line('b'); --empty LOB 是指該 LOB 字段或變量中保存了一個 LOB 指針，但這個指針并沒有指向任何 LOB 數(shù)據(jù)
    ELSE
        dbms_output.put_line('c'); --指針有實際內(nèi)容
    END IF;
END;

declare
  myclob          clob;
  amount          binary_integer;
  offset          integer;
  first_direction varchar2(100);
  more_myclob     varchar2(500);
begin
  --刪除所有“munining Falls”的現(xiàn)有行,然后
  delete from waterfalls   where falls_name =      'Munising Falls';
  
  insert into waterfalls   (falls_name, falls_myclob)  values   ('Munising Falls', EMPTY_CLOB()); --使用EMPTY_CLOB()插入新行來創(chuàng)建LOB定位器
  select falls_myclob   into myclob   from waterfalls  where falls_name = 'Munising Falls'; --檢索由前面的INSERT語句創(chuàng)建的LOB定位器
  --或直接
  INSERT  into waterfalls(falls_name, falls_myclob) values('Munising Falls' EMPTY_CLOB());  returning falls_myclob   into myclob;

  DBMS_LOB.OPEN(myclob, DBMS_LOB.LOB_READWRITE); --打開LOB;不是嚴(yán)格必要的，但是最好打開/關(guān)閉lob。
  
  first_direction := 'Follow I-75 across the Mackinac Bridge.';
  amount          := LENGTH(first_direction); --要寫的字符數(shù)
  offset          := 1; --開始寫CLOB的第一個字符
  DBMS_LOB.WRITE(myclob, amount, offset, first_direction); --使用DBMS_LOB。開始寫
  
  more_myclob := ' Take US-2 west from St. Ignace to Blaney Park.' ||    ' From Seney, take M-28 west to Munising.'; --使用DBMS_LOB.WRITEAPPEND添加更多的myclob
  DBMS_LOB.WRITEAPPEND(myclob, LENGTH(more_myclob), more_myclob);
  
  more_myclob := ' In front of the paper mill, turn right on H-58.' ||    ' Sand Point Road.'; --添加更多的myclob
  DBMS_LOB.WRITEAPPEND(myclob, LENGTH(more_myclob), more_myclob);
  
  DBMS_LOB.CLOSE(myclob); --關(guān)閉LOB，就完成了。
end;

declare
  myclob   clob;
  myclob_1 varchar2(300);
  myclob_2 varchar2(300);
  chars_read_1 binary_integer;
  chars_read_2 binary_integer;
  offset       integer;
begin
  select falls_myclob into myclob from waterfalls where falls_name = 'Munising Falls'; --檢索之前插入的LOB定位器
  offset := 1;  --從第一個字符開始閱讀
  chars_read_1 := 229; --嘗試讀取myclob的229個字符時，chars_read_1將使用實際讀取的字符數(shù)進行更新
  DBMS_LOB.READ(myclob, chars_read_1, offset, myclob_1);

  if chars_read_1 = 229 then  --如果讀取229個字符，則更新偏移量并嘗試讀取255個字符。
    offset       := offset + chars_read_1;
    chars_read_2 := 255;
    DBMS_LOB.READ(myclob, chars_read_2, offset, myclob_2);
  else
    chars_read_2 := 0;
    myclob_2 := '';
  end if;
  
  DBMS_OUTPUT.PUT_LINE('Characters read = ' ||  TO_CHAR(chars_read_1 + chars_read_2));  --顯示讀取的字符總數(shù)
  DBMS_OUTPUT.PUT_LINE(myclob_1);  --顯示myclob
  DBMS_OUTPUT.PUT_LINE(myclob_2);
end;

create directory BFILE_DATA as 'D:/temp';
declare
 waterfall_picture bfile;
begin
 waterfall_picture := BFILENAME('BFILE_DATA','waterfall.gif'); --調(diào)用BFILENAME來創(chuàng)建BFILE定位器
 insert into waterfalls (falls_name, falls_web_page) values ('my waterfall',waterfall_picture); --保存我們的新定位在waterfalls 
en

declare
 waterfall bfile;
 piece raw(60);
 amount binary_integer := 60;
 offset integer := 1;
begin
 select falls_web_page into waterfall from waterfalls where falls_name='my waterfall'; --檢索LOB定位器
 DBMS_LOB.OPEN(waterfall); --打開定位器，讀取60個字節(jié)，然后關(guān)閉定位器
 DBMS_LOB.READ(waterfall, amount, 1, piece);
 DBMS_LOB.CLOSE(waterfall);

 DBMS_OUTPUT.PUT_LINE(RAWTOHEX(piece));--十六進制顯示結(jié)果
 --將原始結(jié)果轉(zhuǎn)換為我們可以讀取的字符串
 --DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(piece));
 end;

declare
  My_Falls_bfile bfile := BFILENAME('BFILE_DATA', 'waterfall.gif');
  photo               blob;
  destination_offset  integer := 1;
  source_offset       integer := 1;
begin
  delete from waterfalls where falls_name = 'my waterfall'; --刪除Tannery Falls的行，所以這個例子可以運行多次。
  insert into waterfalls (falls_name, FALLS_PHOTO) values ('my waterfall', EMPTY_BLOB());--使用EMPTY_BLOB()插入新行來創(chuàng)建LOB定位器
  select FALLS_PHOTO into photo from waterfalls where falls_name = 'my waterfall'; --檢索由前面的INSERT語句創(chuàng)建的LOB定位器
  DBMS_LOB.OPEN(photo, DBMS_LOB.LOB_READWRITE);--打開目標(biāo)BLOB和源BFILE
  DBMS_LOB.OPEN(My_Falls_bfile);
  DBMS_LOB.LOADBLOBFROMFILE(photo,  My_Falls_bfile, DBMS_LOB.LOBMAXSIZE, destination_offset, source_offset);  --Load the contents of the BFILE into the BLOB column
  DBMS_LOB.CLOSE(photo);  --關(guān)閉兩個lob
  DBMS_LOB.CLOSE(My_Falls_bfile);
end;

* 在調(diào)用此函數(shù)之前需要寫插入一個字符串到 BLOB 中比如：
*        "CREATE TABLE tablewithlobs (a int, b BLOB, c CLOB, d NCLOB)";
*        "INSERT INTO tablewithlobs values (1, 'AA', 'AAA', N'AAAA')";
* 否則程序會在 OracleLob tempLob    = reader.GetOracleLob(0) 處出錯。

conn.Open();
OracleCommand cmd = conn.CreateCommand();
OracleTransaction transaction = cmd.Connection.BeginTransaction();      // 利用事務(wù)處理（必須）
cmd.Transaction = transaction;
// 獲得 OracleLob 指針
cmd.CommandText = "select fulls_myblob from waterfalls where fulls_name = 'myabc' FOR UPDATE";
using (OracleDataReader reader = cmd.ExecuteReader())
{
    reader.Read(); //Obtain the first row of data.
    OracleBlob tempLob = reader.GetOracleBlobForUpdate(0);   //Obtain a LOB.        
    FileStream fs = new FileStream("c:\\1.txt", FileMode.Open); // 將文件寫入 BLOB 中
    tempLob.BeginChunkWrite();
    int length = 10485760;
    byte[] Buffer = new byte[length];
    int i;
    while ((i = fs.Read(Buffer, 0, length)) > 0)
    {
        tempLob.Write(Buffer, 0, i);
    }
    fs.Close();
    tempLob.EndChunkWrite();
    cmd.Parameters.Clear();
}
transaction.Commit(); // 提交事務(wù)
conn.Close();

conn.Open();
OracleCommand cmd = conn.CreateCommand();
OracleTransaction trans = cmd.Connection.BeginTransaction();// 利用事務(wù)處理（必須）
cmd.Transaction = trans;
// 獲得 OracleLob 指針
string sql = "select fulls_myblob from waterfalls where fulls_name = 'myabc'";
cmd.CommandText = sql;
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
OracleBlob tempLob = dr.GetOracleBlob(0);
dr.Close();

// 讀取 BLOB 中數(shù)據(jù)，寫入到文件中
FileStream fs = new FileStream("c:\\1.txt", FileMode.Create);
int length = 1048576;
byte[] Buffer = new byte[length];
int i;
while ((i = tempLob.Read(Buffer, 0, length)) > 0)
{
    fs.Write(Buffer, 0, i);
}
fs.Close();
tempLob.Clone();
cmd.Parameters.Clear();
trans.Commit();     // 提交事務(wù)
conn.Close();