在Oracle DB的CLOB字段中存储文件 [英] Storing Files in Oracle DB's CLOB field

问题描述

我的oracle表中有一列具有CLOB数据类型. 如何在此列中存储.txt文件以及如何检索同一文件?

I have a column in my oracle table with CLOB datatype. How do I store .txt file in this column and how can I retrieve the same file?

下面是表定义

fileID  Number
logFile CLOB

预先感谢

推荐答案

在PL/SQL中将文件加载到CLOB非常简单-您只需要使用

Loading a file into a CLOB in PL/SQL is pretty easy-- you just need to use the DBMS_LOB.LoadCLOBFromFile procedure

CREATE DIRECTORY file_dir
    AS <<path on database server file system>>;

GRANT read, write
   ON file_dir
   TO your_user_name;

DECLARE
  l_src_file bfile := BFileName( 'FILE_DIR', <<name of file>> );
  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 some_table( fileID, logFile )
    VALUES( fileIDSeq.nextval, empty_clob() )
    RETURNING logFile 
         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;
/

再次将文件从CLOB写入文件系统.我会使用类似Tom Kyte的

Writing the file from the CLOB to the file system again is a bit more involved. I would use something like Tom Kyte's clob_to_file procedure

这篇关于在Oracle DB的CLOB字段中存储文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！