使用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:oraclefile
d:oraclefileexample.txt
d:oraclefile tt.txt
一、写文件到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:oraclefile
d:oraclefileexample.txt
d:oraclefile tt.txt