[20180415]blob的插入.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180415]如何取出這幾行資料.txt
- [20180416]clob的插入.txt
- Firedac 在資料表中插入BLOB資料的方法
- [20190104]bbed手工插入資料.txt
- [20210301]第2行插入Y.txt
- MySQL插入圖片或pdf檔案到資料庫中（BLOB）--load_file函式MySql資料庫函式
- JavaScript Blob 物件JavaScript物件
- 關於Oracle的BLOB和CLOBOracle
- Mysql BLOB、BLOB與TEXT區別及效能影響、將BLOB型別轉換成VARCHAR型別MySql型別
- 當 sendBeacon 遇上 Blob
- Blob type 屬性
- Blob size 屬性
- oid轉blob問題
- 【譯】什麼是Blob？
- Azure Storage Blob ContentType 問題
- 全面分析插入排序的三種插入方式排序
- 7.82 EMPTY_BLOB, EMPTY_CLOB
- 如何使用 jq 接收 blob 資料
- Mybatis如何動態生成插入的列及批次插入值MyBatis
- Mysql在資料插入後立即獲取插入的IdMySql
- PHP 操作 mysql blob 資料型別的欄位PHPMySql資料型別
- base64 , blob,url圖片的處理方式
- 【Azure Blob】關閉Blob 匿名訪問，iOS Objective-C SDK連線Storage Account報錯iOSObject
- Git插入新的commitGitMIT
- MySQL中TEXT與BLOB欄位型別的區別MySql型別
- C# convert sql blob type to plain stringC#SQLAI
- Java mysql blob 資料讀寫操作JavaMySql
- JS操作二進位制方法 - blobJS
- mybatis插入資料、批量插入資料MyBatis
- 如何檢視ORACLE的LOB(BLOB和CLOB)物件佔用的大小Oracle物件
- Oracle BLOB型別的資料如何檢視和下載？Oracle型別
- Delphi在dbgrideh中新增Img顯示blobIDE
- Azure Blob （三）引數設定說明
- 如何更換Azure SQL DB blob storage credentialsSQL
- 如何使用FormData上傳壓縮裁剪後的圖片Blob物件ORM物件
- 【Azure Developer】VS Code執行Java 版Azure Storage SDK操作Blob (新建Container, 上傳Blob檔案，下載及清理)DeveloperJavaAI
- 三種插入排序 直接插入排序，折半插入排序，希爾排序排序
- 插入排序排序