One DBMS_LOB example
-- &1 for the Oracle directory where the files (gem_custom.zip, Help.xml) are put
-- Refer to http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_lob.htm#i998978 for reference
declare
-- Insert BFILE into BLOB column
-- Using DBMS_LOB.LoadFromFile OR DBMS_LOB.LoadBLOBFromFile
src_zip bfile := BFILENAME('&1', 'gem_custom.zip');
dest_zip blob;
-- Insert BFILE into CLOB column
-- Using DBMS_LOB.LoadCLOBFromFile
src_help bfile := BFILENAME('&1', 'Help.xml');
dest_help clob;
dst_offset number := 1;
src_offset number := 1;
warning number;
begin
-- zip file
-- first get the lob locator
update Customization_env
set custom_env_file = EMPTY_BLOB()
where product = 'G'
returning custom_env_file into dest_zip;
--1.open
DBMS_LOB.open(src_zip, DBMS_LOB.LOB_READONLY);
DBMS_LOB.open(dest_zip, DBMS_LOB.LOB_READWRITE);
--2.load
DBMS_LOB.LOADFROMFILE(dest_lob => dest_zip,
src_lob => src_zip,
amount => DBMS_LOB.getLength(src_zip));
--2.close
DBMS_LOB.close(dest_zip);
DBMS_LOB.close(src_zip);
commit;
-- help xml file
-- first get the lob locator
update Customization_env
set help_dll_function = EMPTY_CLOB()
where product = 'G'
returning help_dll_function into dest_help ;
--1.open
DBMS_LOB.open(src_help, DBMS_LOB.LOB_READONLY);
DBMS_LOB.open(dest_help, DBMS_LOB.LOB_READWRITE);
--2.load
DBMS_LOB.LoadCLOBFromFile(DEST_LOB => dest_help,
SRC_BFILE => src_help,
AMOUNT => DBMS_LOB.GETLENGTH(src_help),
DEST_OFFSET => dst_offset,
SRC_OFFSET => src_offset,
BFILE_CSID => DBMS_LOB.DEFAULT_CSID,
LANG_CONTEXT => DBMS_LOB.DEFAULT_LANG_CTX,
WARNING => warning);
--3.close
DBMS_LOB.close(dest_help);
DBMS_LOB.close(src_help);
commit;
exception
when others then
dbms_lob.filecloseall;
rollback;
raise;
end;
/
exit
上面的例子是把文件存到数据库中,下面这个例子是从数据库中读取LOB然后存入文件...
-- Refer to http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#i997577
-- for the use of UTL_FILE
declare
output_file utl_file.file_type;
chunk_size constant pls_integer := 4096;
buf raw (4096); -- Must be equal to chunk_size
written_sofar pls_integer := 0; --(avoid PLS-00491: numeric literal required)
bytes_to_write pls_integer;
LOB_LEN PLS_INTEGER;
V_LOB BLOB;
V_DIR VARCHAR2(20) := 'LO_DUMP';
v_file varchar2(20) := 'gem_custom.zip';
BEGIN
select custom_env_file into v_lob from CUSTOMIZATION_ENV where product='G';
lob_len := dbms_lob.getlength(v_lob);
output_file := utl_file.fopen(v_dir, v_file, 'WB');
while written_sofar + chunk_size < lob_len loop
bytes_to_write := chunk_size;
dbms_lob.read(v_lob,bytes_to_write,written_sofar+1,buf);
utl_file.put_raw(output_file,buf);
written_sofar := written_sofar + chunk_size;
end loop;
bytes_to_write := lob_len-written_sofar;
dbms_lob.read(v_lob,bytes_to_write,written_sofar+1,buf);
utl_file.put_raw(output_file,buf);
utl_file.fclose(output_file);
END;
/
exit
--------------------------------------
Regards,
FangwenYu
![](https://pic.cnblogs.com/avatar/93867/20191016181427.png)