[20180415]blob的插入.txt

lfree發表於2018-04-15

[20180415]blob的插入.txt

--//論壇上有人問的問題,如何限制BLOB欄位的大小,使用者只能上傳15K至30K的圖片,超過30K就不給上傳.
--//連結:http://www.itpub.net/thread-482195-1-1.html

--//我自己也測試許久,始終沒有找到好的解決方法,這個主要是插入blob時,實際上先插入empty_blob(),獲取定位符合,
--//然後插入相關資訊,這個過程中無法獲得blob欄位的大小,關於限制blob大小的問題先放棄,
--//先探究blob的插入:

1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> alter database add supplemental log data;
Database altered.
--//開啟附加日誌主要目的是logminer能觀察到相關日誌.

2.建立測試指令碼:
SCOTT@book> create table t (id number,image blob));
Table created.

--//從網上抄了一段程式碼,修改如下:
--//連結:zhidao.baidu.com/question/569359922.html
$ cd /u01/app/oracle/admin/book/dpdump
$ ls -l 1.txt
-rw-r--r-- 1 oracle oinstall 6578 2018-04-11 09:11:24 1.txt
--//檔案我寫的比較特殊每行2047個字元.這樣加上回車正好2047.

$ cat c1.txt
declare
b_file bfile;
b_lob blob;
begin
insert into t values(2,empty_blob()) return image into b_lob;
     b_file:=bfilename('DATA_PUMP_DIR','1.txt');
     dbms_lob.open(b_file,dbms_lob.file_readonly);
     dbms_lob.loadfromfile(b_lob,b_file,dbms_lob.getlength(b_file));
     dbms_lob.close(b_file);
commit;
end;
/

3.插入跟蹤看看:

SCOTT@book> @ &r/scn
GET_SYSTEM_CHANGE_NUMBER SYSDATE
------------------------ -------------------
             13277727401 2018-04-15 15:13:01

SCOTT@book> @ &r/10046on 12
Session altered.

SCOTT@book> insert into t values (1,'aabb');
1 row created.

SCOTT@book> @ c1.txt
PL/SQL procedure successfully completed.

SCOTT@book> @ &r/10046off
Session altered.

SCOTT@book> @ &r/scn
GET_SYSTEM_CHANGE_NUMBER SYSDATE
------------------------ -------------------
             13277727464 2018-04-15 15:14:04


--//從10046跟蹤基本看不出來:
=====================
PARSING IN CURSOR #139798488606408 len=31 dep=0 uid=83 oct=2 lid=83 tim=1523776424786732 hv=1834852568 ad='7d2e7e68' sqlid='5321f9xqpv86s'
insert into t values (1,'aabb')
END OF STMT
PARSE #139798488606408:c=999,e=1587,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1523776424786726
EXEC #139798488606408:c=1000,e=494,p=0,cr=1,cu=5,mis=0,r=1,dep=0,og=1,plh=0,tim=1523776424787335
STAT #139798488606408 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=438 us)'
WAIT #139798488606408: nam='SQL*Net message to client' ela= 6 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1523776424787527

*** 2018-04-15 15:13:49.537
WAIT #139798488606408: nam='SQL*Net message from client' ela= 4750330 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1523776429537892
CLOSE #139798488606408:c=1000,e=29,dep=0,type=0,tim=1523776429538020
=====================
PARSING IN CURSOR #139798488606408 len=312 dep=0 uid=83 oct=47 lid=83 tim=1523776429543248 hv=850764855 ad='7dbaf510' sqlid='guc865stbb91r'
declare
b_file bfile;
b_lob blob;
begin
insert into t values(2,empty_blob()) return image into b_lob;
     b_file:=bfilename('DATA_PUMP_DIR','1.txt');
     dbms_lob.open(b_file,dbms_lob.file_readonly);
     dbms_lob.loadfromfile(b_lob,b_file,dbms_lob.getlength(b_file));
     dbms_lob.close(b_file);
commit;
end;
END OF STMT
PARSE #139798488606408:c=4999,e=5138,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1523776429543246
=====================
PARSING IN CURSOR #139798488597632 len=59 dep=1 uid=83 oct=2 lid=83 tim=1523776429543820 hv=2186787885 ad='7e23fc00' sqlid='ftb5f8k15gg1d'
INSERT INTO T VALUES(2,EMPTY_BLOB()) RETURN IMAGE INTO :O0
END OF STMT
PARSE #139798488597632:c=0,e=280,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1523776429543818
BINDS #139798488597632:
Bind#0
  oacdty=113 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=206001 frm=00 csi=00 siz=4000 off=0
  kxsbbbfp=7f255f40ab68  bln=4000  avl=00  flg=05
EXEC #139798488597632:c=1000,e=873,p=0,cr=1,cu=3,mis=1,r=1,dep=1,og=1,plh=0,tim=1523776429544801
STAT #139798488597632 id=1 cnt=1 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=162 us)'
CLOSE #139798488597632:c=0,e=2,dep=1,type=3,tim=1523776429544938
WAIT #139798488606408: nam='BFILE open' ela= 89  =0  =0  =0 obj#=-1 tim=1523776429545286
WAIT #139798488606408: nam='BFILE get length' ela= 10  =0  =0  =0 obj#=-1 tim=1523776429545403
WAIT #139798488606408: nam='BFILE get length' ela= 8  =0  =0  =0 obj#=-1 tim=1523776429545478
WAIT #139798488572808: nam='BFILE internal seek' ela= 47  =0  =0  =0 obj#=-1 tim=1523776429545709
WAIT #139798488572808: nam='BFILE read' ela= 32  =0  =0  =0 obj#=-1 tim=1523776429546129
WAIT #139798488572808: nam='Disk file operations I/O' ela= 37 FileOperation=2 fileno=4 filetype=2 obj#=-1 tim=1523776429546371
WAIT #139798488572808: nam='Disk file operations I/O' ela= 23 FileOperation=2 fileno=4 filetype=2 obj#=-1 tim=1523776429546442
WAIT #139798488572808: nam='direct path write' ela= 37 file number=4 first dba=3581 block cnt=1 obj#=-1 tim=1523776429546553
WAIT #139798488572808: nam='direct path sync' ela= 17 File number=4 Flags=0 p3=0 obj#=-1 tim=1523776429546629
WAIT #139798488606408: nam='BFILE closure' ela= 23  =0  =0  =0 obj#=-1 tim=1523776429546824
=====================

4.透過logminer觀察:
BEGIN
   DBMS_LOGMNR.START_LOGMNR
   (
      STARTSCN   => 13277727401
     ,ENDSCN     => 13277727464
     ,OPTIONS    =>   DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
                    + DBMS_LOGMNR.CONTINUOUS_MINE
                    + DBMS_LOGMNR.COMMITTED_DATA_ONLY
   );
END;
/

set linesize 2000
set pagesize 4999
column sql_redo format a1024
select operation,sql_redo from V$LOGMNR_CONTENTS where seg_name='T' and seg_owner='SCOTT';

OPERATION       SQL_REDO
--------------- ----------------------------------------------------------------------------------------------------
INSERT          insert into "SCOTT"."T"("ID","IMAGE") values ('1',EMPTY_BLOB());
UPDATE          update "SCOTT"."T" set "IMAGE" = HEXTORAW('aabb') where "ID" = '1' and ROWID = 'AAAWIeAAEAAAA30AAB';
INSERT          insert into "SCOTT"."T"("ID","IMAGE") values ('2',EMPTY_BLOB());
SEL_LOB_LOCATOR DECLARE
                 loc_c CLOB;
                 buf_c VARCHAR2(6144);
                 loc_b BLOB;
                 buf_b RAW(6144);
                 loc_nc NCLOB;
                 buf_nc NVARCHAR2(6144);
                BEGIN
                 select "IMAGE" into loc_b from "SCOTT"."T" for update;


LOB_WRITE
                 buf_b := HEXTORAW('61313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131
                3233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233
                3435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435
                363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383

LOB_WRITE       96131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435360a');
                  dbms_lob.write(loc_b, 2048, 1, buf_b);
                END;

 

LOB_WRITE
                 buf_b := HEXTORAW('62313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231
                3233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233
                3435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435
                363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383

LOB_WRITE       96231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435360a');
                  dbms_lob.write(loc_b, 2048, 2049, buf_b);
                END;

 

LOB_WRITE
                 buf_b := HEXTORAW('633132333435360a');
                  dbms_lob.write(loc_b, 8, 4097, buf_b);
                END;
9 rows selected.

--//你可以看到當插入長度很小(具體應該是4000-36 = 3964)是(id=1)的情況,實際上插入也是執行先插入EMPTY_BLOB(),然後直接update.
insert into "SCOTT"."T"("ID","IMAGE") values ('1',EMPTY_BLOB());
update "SCOTT"."T" set "IMAGE" = HEXTORAW('aabb') where "ID" = '1' and ROWID = 'AAAWIeAAEAAAA30AAB';

--//而插入id=2(使用指令碼),呼叫是一個儲存過程:
select "IMAGE" into loc_b from "SCOTT"."T" for update;
--//先獲得blob控制程式碼loc_b(logminer的operation很有意思是SEL_LOB_LOCATOR).寫內容到buf_b快取,大小2048位元組,然後呼叫dbms_lob.write函式寫入,迴圈直到寫完.
--//即使我指令碼執行的是dbms_lob.loadfromfile(b_lob,b_file,dbms_lob.getlength(b_file));,寫入還是分段2k大小寫入的.

4.你可以從上面的測試看到,僅僅當插入內容的blob長度很小(<=3964位元組)時,oracle內部才使用update修改相關內容.

--//如果修改內容長度大於3964,儲存在塊外,透過呼叫dbms_lob.write實現blob的寫入,這樣看不到執行的update語句.

5.如果加約束呢:
SCOTT@book> alter table t add  constraint c_t check (length(image)<=4002)  enable ;
alter table t add  constraint c_t check (length(image)<=4002)  enable
                              *
ERROR at line 1:
ORA-02293: cannot validate (SCOTT.C_T) - check constraint violated

--//已經有記錄存在,不能執行.
SCOTT@book> alter table t add  constraint c_t check (length(image)<=4002)  enable novalidate;
Table altered.

SCOTT@book> insert into t select 3,image from t where id=2;
insert into t select 3,image from t where id=2
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.C_T) violated

--//而實際上如果執行指令碼c1.txt:
SCOTT@book> @ c1.txt
PL/SQL procedure successfully completed.
--//可以發現可以正常插入,約束根本無效,為什麼insert into t select 3,image from t where id=2;約束有效呢?

SCOTT@book> select length(image),t.id  from t ;
LENGTH(IMAGE)           ID
------------- ------------
         4104            2
            2            1
         4104            2


SCOTT@book> delete from t where id=2 and rownum=1;
1 row deleted.

SCOTT@book> commit ;
Commit complete.

6.繼續分析insert+select的情況:
SCOTT@book> @ &r/scn
GET_SYSTEM_CHANGE_NUMBER SYSDATE
------------------------ -------------------
             13277729967 2018-04-15 15:51:48

SCOTT@book> insert into t select 3,image from t where id=2;
insert into t select 3,image from t where id=2
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.C_T) violated


SCOTT@book> @ &r/scn
GET_SYSTEM_CHANGE_NUMBER SYSDATE
------------------------ -------------------
             13277729983 2018-04-15 15:51:58


BEGIN
   DBMS_LOGMNR.START_LOGMNR
   (
      STARTSCN   => 13277729967
     ,ENDSCN     => 13277729983
     ,OPTIONS    =>   DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
                    + DBMS_LOGMNR.CONTINUOUS_MINE
);
END;
/

--//注意取消DBMS_LOGMAR.COMMITTED_DATA_ONLY,因為沒成功插入記錄.

set linesize 2000
set pagesize 4999
column sql_redo format a1024
select operation,sql_redo from V$LOGMNR_CONTENTS where seg_name='T' and seg_owner='SCOTT';

OPERATION                        SQL_REDO
-------------------------------- -----------------------------------------------------------------
INSERT                           insert into "SCOTT"."T"("ID","IMAGE") values ('3',EMPTY_BLOB());
SEL_LOB_LOCATOR                  DECLARE
                                  loc_c CLOB;
                                  buf_c VARCHAR2(6156);
                                  loc_b BLOB;
                                  buf_b RAW(6156);
                                  loc_nc NCLOB;
                                  buf_nc NVARCHAR2(6156);
                                 BEGIN
                                  select "IMAGE" into loc_b from "SCOTT"."T" where "ID" = '3' for update;


INTERNAL
LOB_WRITE
                                  buf_b := HEXTORAW('61313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131
                                 3233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233
                                 3435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435
                                 363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435363738396131323334353637383

LOB_WRITE                        96131323334353637383961313233343536373839613132333435363738396131323334353637383961313233343536373839613132333435360a');
                                   dbms_lob.write(loc_b, 2048, 1, buf_b);
                                 END;

 

LOB_WRITE
                                  buf_b := HEXTORAW('62313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231
                                 3233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233
                                 3435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435
                                 363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435363738396231323334353637383

LOB_WRITE                        96231323334353637383962313233343536373839623132333435363738396231323334353637383962313233343536373839623132333435360a');
                                   dbms_lob.write(loc_b, 2048, 2049, buf_b);
                                 END;

 

LOB_WRITE
                                  buf_b := HEXTORAW('633132333435360a');
                                   dbms_lob.write(loc_b, 8, 4097, buf_b);
                                 END;

 

INTERNAL
INTERNAL
DELETE                           delete from "SCOTT"."T" where ROWID = 'AAAWIeAAEAAAA30AAD';

11 rows selected.

--//奇怪,這裡定義長度變成了6156.最後回滾了dml操作.很奇怪這裡約束其作用了,實在不理解.

SCOTT@book> alter table t drop  constraint c_t ;
Table altered.

SCOTT@book> delete from t;
2 rows deleted.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> alter table t add  constraint c_t check (length(image)<=4002)  enable ;
Table altered.

SCOTT@book> @c1.txt
PL/SQL procedure successfully completed.

SCOTT@book> select length(image),t.id  from t ;
LENGTH(IMAGE)           ID
------------- ------------
         4104            2

SCOTT@book> insert into t select 3,image from t where id=2;
insert into t select 3,image from t where id=2
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.C_T) violated

--//如果對比前面視乎insert into+select 內部多了一些個遞迴操作.操作多了INTERNAL.放棄,不知道問題在那裡....

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

相關文章