#### 环境准备 ``` 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; / ``` #### 参考 - https://www.it1352.com/1717133.html - https://asktom.oracle.com/pls/apex/f?p=100:11:0%3a%3a%3a%3aP11_QUESTION_ID:744825627183 #### 其他代码参考 ``` 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> ```