blobclob:在sqlplus中操作blob和clob思路方法

on 2009-2-12 in 游戏开发 | 0 Comment


在sqlplus中操作blob和clob思路方法,下面实现代码

create directory utllobdir as \'d:\'
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">create table blobtest(col1 BLOB); \"\"\" align=top twffan=\"http://www.blogchinese.com/done\">create table clobtest(col1 cLOB);
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">--insert BLOB
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">declare
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">a_blob BLOB;
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">bfile_name BFILE := BFILENAME(\'ULTLOBDIR\',\'teslob.doc\');
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">begin
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">insert o blobtest values (empty_blob)
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">ing col1 o a_blob;
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">dbms_lob.fileopen(bfile_name);
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name));
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">dbms_lob.fileclose(bfile_name);
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">commit;


\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">end;
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">--update BLOB
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">declare
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">a_blob BLOB;
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">bfile_name BFILE := BFILENAME(\'ULTLOBDIR\',\'log.txt\');
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">begin
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">update blobtest col1=empty_blob where rownum=1
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">ing col1 o a_blob;
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">dbms_lob.fileopen(bfile_name);
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name));
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">dbms_lob.fileclose(bfile_name);
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">commit;
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">end;
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">--insert CLOB
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">create

table clobtest(col1 CLOB);
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">declare
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">a_clob CLOB;
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">bfile_name BFILE := BFILENAME(\'ULTLOBDIR\',\'teslob.doc\');
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">begin
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">insert o clobtest values (empty_clob)
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">ing col1 o a_clob;
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">dbms_lob.fileopen(bfile_name);
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">dbms_lob.loadfromfile(a_clob, bfile_name, dbms_lob.getlength(bfile_name));
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">dbms_lob.fileclose(bfile_name);
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">commit;
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">end;
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">--update CLOB
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">declare
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">a_clob CLOB;
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">bfile_name BFILE := BFILENAME(\'ULTLOBDIR\'

,\'log.txt\');
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">begin
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">update clobtest col1=empty_clob where rownum=1
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">ing col1 o a_clob;
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">dbms_lob.fileopen(bfile_name);
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">dbms_lob.loadfromfile(a_clob, bfile_name, dbms_lob.getlength(bfile_name));
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">dbms_lob.fileclose(bfile_name);
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">commit;
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">end;


　　通过查看lob字段长度判断是否插入成功

select dbms_lob.getlength(col1) from blobtest;
\"\"\" align=top twffan=\"http://www.blogchinese.com/done\">select dbms_lob.getlength(col1) from clobtest;

Tags:  oracleblobclob sqlclobblob blobclob区别 blobclob

延伸阅读

最新评论

发表评论