ITPub博客

首页 > 数据库 > 数据库开发技术 > 把外部bfile文件数据导入到blob大对象中的测试:

把外部bfile文件数据导入到blob大对象中的测试:

原创 数据库开发技术 作者:cyr1974 时间:2007-01-16 15:52:24 0 删除 编辑

SQL*Plus: Release 10.2.0.1.0 - 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL>create or replace directory LOBDIR as 'D:/LOBTEST';

SQL> drop table blobtab;

表已删除。

SQL> create table blobtab(index_col integer, blob_col blob);

表已创建。

SQL> insert into blobtab(index_col, blob_col)
2 values(1, utl_raw.cast_to_raw('this is only a test'));

已创建 1 行。

SQL> declare
2 ablob blob;
3 abfile bfile := bfilename('LOBDIR', 'less301.rm'); -- Get a pointer to the file.
4 amount integer;
5 asize integer;
6 begin
7 --
8 -- Note: Above, the blobtab table was initialized with one entry. This
9 -- is needed because the PL/SQL BLOB locator (ablob) must point to a
10 -- specific EXISTING NON-NULL database BLOB. Also, the select statement
11 -- may only return one entry. If more than one row is returned
12 -- (more than one row with index_col set equal to 1), then the script
13 -- will fail.
14 --
15 select blob_col into ablob from blobtab where index_col = 1;
16 dbms_lob.fileopen(abfile);
17 asize := dbms_lob.getlength(abfile);
18 dbms_output.put_line('Size of input file: ' || asize);
19 dbms_lob.loadfromfile(ablob, abfile, asize);
20 dbms_output.put_line('After loadfromfile');
21 asize := dbms_lob.getlength(ablob);
22 dbms_output.put_line('Size of blob: ' || asize);
23 exception
24 when others then
25 dbms_output.put_line('An exception occurred');
26 dbms_output.put_line(sqlcode || sqlerrm);
27 end;
28 /
Size of input file: 1164206
After loadfromfile
Size of blob: 1164206

PL/SQL 过程已成功完成。

SQL> COMMIT;

SQL> SELECT BYTES
2 FROM DBA_SEGMENTS
3 WHERE OWNER LIKE 'SYSTEM'
4 AND SEGMENT_TYPE LIKE 'LOBSEGMENT'
5 AND SEGMENT_NAME IN (select SEGMENT_NAME
6 from dba_lobs
7 where owner like 'SYSTEM'
8 AND TABLE_NAME LIKE 'BLOBTAB');

BYTES
----------
2097152

提交完成。

到目录下验证文件大小:1164206 BYTES

D:>CD LOBTEST

D:lobtest>DIR
Volume in drive D has no label.
Volume Serial Number is 40C4-F675

Directory of D:lobtest

01/16/2007 02:50 PM

.
01/16/2007 02:50 PM ..
09/20/2006 02:14 PM 1,164,206 less301.rm
01/16/2007 02:50 PM 1,558 testbfile.sql
2 File(s) 1,165,764 bytes
2 Dir(s) 4,404,162,560 bytes free

D:lobtest>

[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/36779/viewspace-890801/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-03-16

  • 博文量
    37
  • 访问量
    256870