使用pl
/
sql读取写入blob对象
一、写文件到BLOB
SQL
>
create
table
iihero_blob(fid
int
primary
key
, fname
varchar
(
32
), f blob);
表已创建。
SQL
>
conn
/
as
sysdba
已连接。
SQL
>
create
or
replace
directory BLOBDIR
as
'
d:oraclefile
'
;
目录已创建。
SQL
>
grant
read
on
directory BLOBDIR
to
test;
授权成功。
SQL
>
conn test
/
test
已连接。
SQL
>
create
sequence s_iihero_seq
2
start
with
1
3
increment
by
1
4
/
序列已创建。
CREATE
OR
REPLACE
PROCEDURE
iihero_load_blob (pfname
VARCHAR2
)
IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file :
=
bfilename(
'
BLOBDIR
'
, pfname);
INSERT
INTO
iihero_blob (fid,fname,f)
VALUES
(S_IIHERO_SEQ.Nextval,pfname,EMPTY_BLOB())
RETURNING f
INTO
dst_file;
SELECT
f
INTO
dst_file
FROM
iihero_blob
WHERE
fname
=
pfname
FOR
UPDATE
;
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file :
=
dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
UPDATE
iihero_blob
SET
f
=
dst_file
WHERE
fname
=
pfname;
dbms_lob.fileclose(src_file);
commit
;
END
iihero_load_blob;
/
SQL
>
col segment_name
for
a30
SQL
>
conn
/
as
sysdba
已连接。
SQL
>
select
segment_name,segment_type,bytes
/
1024
/
1024
from
dba_segments
where
ow
ner
=
'
TEST
'
;
SEGMENT_NAME SEGMENT_TYPE BYTES
/
1024
/
1024
--
---------------------------- ------------------ ---------------
ST_COURSE
TABLE
.
0625
TAB2
TABLE
.
0625
IIHERO_BLOB
TABLE
.
0625
SYS_LOB0000030667C00003$$ LOBSEGMENT .
0625
已选择34行。
SQL
>
conn test
/
test
已连接。
SQL
>
4
. 装数据(写数据到BLOB)
SQL
>
exec
iihero_load_blob(
'
ttt.txt
'
);
PL
/
SQL 过程已成功完成。
SQL
>
select
fid, fname, dbms_lob.getlength(f)
from
iihero_blob;
FID FNAME DBMS_LOB.GETLENGTH(F)
--
-------- -------------------------------- ---------------------
1
ttt.txt
28729
二、将BLOB中的数据读到文件
1
. 确认有数据
SQL
>
select
fid, fname, dbms_lob.getlength(f)
from
iihero_blob;
FID FNAME DBMS_LOB.GETLENGTH(F)
--
-------- -------------------------------- ---------------------
1
ttt.txt
28729
2
. 创建目录
SQL
>
conn
/
as
sysdba
已连接。
SQL
>
create
or
replace
directory BLOBDIR
as
'
d:oraclefile
'
;
目录已创建。
SQL
>
grant
read
,write
on
directory BLOBDIR
to
test;
授权成功。
3
.创建存储过程
conn test
/
test
CREATE
OR
REPLACE
PROCEDURE
iihero_dump_blob(piname
varchar2
,poname
varchar2
)
IS
l_file UTL_FILE.FILE_TYPE;
l_buffer
RAW
(
32767
);
l_amount BINARY_INTEGER :
=
32767
;
l_pos
INTEGER
:
=
1
;
l_blob BLOB;
l_blob_len
INTEGER
;
BEGIN
SELECT
f
INTO
l_blob
FROM
iihero_blob
WHERE
FNAME
=
piname;
l_blob_len :
=
DBMS_LOB.GETLENGTH(l_blob);
--
dbms_output.put_line(l_blob_len);
--
l_file := UTL_FILE.FOPEN('BLOBDIR',poname,'wb', 32767);
l_file :
=
UTL_FILE.FOPEN(
'
BLOBDIR
'
,poname,
'
w
'
,
32767
);
WHILE
l_pos
<
l_blob_len LOOP
DBMS_LOB.
READ
(l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);
l_pos :
=
l_pos
+
l_amount;
END
LOOP;
UTL_FILE.FCLOSE(l_file);
EXCEPTION
WHEN
OTHERS
THEN
IF
UTL_FILE.IS_OPEN(l_file)
THEN
UTL_FILE.FCLOSE(l_file);
END
IF
;
RAISE;
END
;
/
SQL
>
host dir
/
b
/
s d:oraclefiled:oraclefileexample.txtd:oraclefile tt.txt