#### 環境準備
```
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> 
```

【SQL】Oracle建立CLOB型別上傳下載讀取檔案

相關文章