环境准备
create table test_lob_securefile (t_id number,name varchar2(30),t_file clob) lob (t_file) store as securefile;
CREATE DIRECTORY file_dir AS '/home/oracle/file';
GRANT read,write ON directory file_dir TO mytest;
上传文本
DECLARE
l_src_file bfile := BFileName( 'FILE_DIR','rman_xttconvert_v3.zip');
l_dest_lob clob;
l_dest_offset integer := 1;
l_src_offset integer := 1;
l_lang_context number := dbms_lob.default_lang_ctx;
l_warning number;
BEGIN
dbms_lob.open( l_src_file, dbms_lob.lob_readonly );
INSERT INTO test_lob_securefile( T_ID,name,t_file ) VALUES(2,'文盲筱烨', empty_clob() ) RETURNING t_file INTO l_dest_lob;
dbms_lob.LoadCLOBFromFile(
dest_lob => l_dest_lob,
src_bfile => l_src_file,
amount => dbms_lob.getLength( l_src_file ),
dest_offset => l_dest_offset,
src_offset => l_src_offset,
bfile_csid => dbms_lob.default_csid,
lang_context => l_lang_context,
warning => l_warning );
dbms_lob.close( l_src_file );
END;
/
读取
--读取
set serveroutput on
declare
info_var clob;
amount integer;
offset integer;
info_output varchar2(2000);
begin
select t_file into info_var from test_lob_securefile where t_id = 1;
amount :=6000;
offset :=1;
dbms_lob.read(info_var,amount,offset,info_var);
dbms_output.put_line(info_var);
end;
/
参考
其他代码参考
ops$tkyte@DEV816> create or replace
2 procedure clob_to_file( p_dir in varchar2,
3 p_file in varchar2,
4 p_clob in clob )
5 as
6 l_output utl_file.file_type;
7 l_amt number default 32000;
8 l_offset number default 1;
9 l_length number default
nvl(dbms_lob.getlength(p_clob),0);
10 BEGIN
11 l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
12 while ( l_offset < l_length )
13 loop
14 utl_file.put(l_output,
15 dbms_lob.substr(p_clob,l_amt,l_offset) );
16 utl_file.fflush(l_output);
17 l_offset := l_offset + l_amt;
18 end loop;
19 utl_file.new_line(l_output);
20 utl_file.fclose(l_output);
21 end;
22 /
Procedure created.
ops$tkyte@DEV816> create table test_tab ( col_id number, col_text clob );
Table created.
ops$tkyte@DEV816> declare
2 l_col_text clob;
3 begin
4 for i in 1..5 loop
5 insert into test_tab values
6 ( i, empty_clob() )
7 returning col_text into l_col_text;
8 for i in 1 .. 10 loop
9 dbms_lob.writeappend( l_col_text, 30001,
10 rpad('*',30000,'*') || chr(10) );
11 end loop;
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
ops$tkyte@DEV816> create or replace
2 procedure dump_table_to_file
3 (p_dir in varchar2,
4 p_file_extn in varchar2 default '.txt',
5 p_col_id in number default null)
6 is
7 BEGIN
8 for x in ( select *
9 from test_tab
10 where col_id = nvl(p_col_id,col_id) )
11 loop
12 clob_to_file( p_dir,
13 x.col_id || p_file_extn,
14 x.col_text );
15 end loop;
16 END;
17 /
Procedure created.
ops$tkyte@DEV816> exec dump_table_to_file( '/tmp' );
PL/SQL procedure successfully completed.
ops$tkyte@DEV816> host ls -l /tmp/?.txt
-rw-r--r-- 1 ora816 300011 Sep 12 08:00 /tmp/1.txt
-rw-r--r-- 1 ora816 300011 Sep 12 08:00 /tmp/2.txt
-rw-r--r-- 1 ora816 300011 Sep 12 08:00 /tmp/3.txt
-rw-r--r-- 1 ora816 300011 Sep 12 08:00 /tmp/4.txt
-rw-r--r-- 1 ora816 300011 Sep 12 08:00 /tmp/5.txt
ops$tkyte@DEV816> select dbms_lob.getlength(col_text) from test_tab;
DBMS_LOB.GETLENGTH(COL_TEXT)
----------------------------
300010
300010
300010
300010
300010
ops$tkyte@DEV816>