dbms_lob.write 測試

HUNTER@test>create table t_lob(id number,name clob);

Table created.

HUNTER@test>desc t_lob;
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 ID                                                                                NUMBER
 NAME                                                                              CLOB

 

HUNTER@test>insert into t_lob(id) values(1);

1 row created.

HUNTER@test>commit;

Commit complete.

HUNTER@test>select * from t_lob;

        ID NAME
---------- --------------------------------------------------------------------------------
         1

HUNTER@test> Declare
  2          varC clob;
  3          vWStr varchar2(1000);
  4          vStrt number(4);
  5        ln number(4);
  6       Begin
  7          vWStr := 'CLOB';
  8          ln := Length(vWStr);
  9          vStrt := 5;
 10          select name into varC from t_lob where id = 1 FOR UPDATE;
 11          DBMS_LOB.Write(varC, ln, vStrt, vWStr);
 12          DBMS_output.put_line('改寫結果為: '||varC);
 13          Commit;
 14       End;
 15  /
   Declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 819
ORA-06512: at line 11

 

HUNTER@test>insert into t_lob(id,name) values(2,empty_clob()) ;

1 row created.

HUNTER@test>commit;

Commit complete.

HUNTER@test>Declare
  2          varC clob;
  3          vWStr varchar2(1000);
  4          vStrt number(4);
  5          ln number(4);
  6          Begin
  7          vWStr := 'CLOB';
  8          ln := Length(vWStr);
  9          vStrt := 5;
 10          select name into varC from t_lob where id = 2 FOR UPDATE;
 11          DBMS_LOB.Write(varC, ln, vStrt, vWStr);
 12          DBMS_output.put_line('改寫結果為: '||varC);
 13          Commit;
 14       End;
 15  /
改寫結果為:     CLOB

PL/SQL procedure successfully completed.

HUNTER@test>select * from t_lob;

        ID NAME
---------- --------------------------------------------------------------------------------
         1
         2     CLOB

HUNTER@test>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7364032/viewspace-425556/，如需轉載，請註明出處，否則將追究法律責任。