《Oracle PL/SQL开发指南》学习笔记33——源码调试——大对象（第二部分，将文件读入内部存储的列）

这节内容的调试花费了很多时间，却还有个遗留问题——中文（多字节字符）文件的读取。

1. 以sys身份创建虚拟目录，将大对象源文件复制到该目录下

SQL> CREATE DIRECTORY generic AS 'D:\temp';

2. 为c##student用户授权

GRANT READ ON DIRECTORY generic TO c##student;

3. 创建存储过程

-- Create stored procedure to load a CLOB datatype.
CREATE OR REPLACE PROCEDURE load_clob_from_file
( src_file_name     IN VARCHAR2
, table_name        IN VARCHAR2
, column_name       IN VARCHAR2
, primary_key_name  IN VARCHAR2
, primary_key_value IN VARCHAR2) IS

  -- Define local variables for DBMS_LOB.LOADCLOBFROMFILE procedure.
  --src_csid number :=NLS_CHARSET_ID('UTF8'); added
  --ZHS16GBK, SIMPLIFIED CHINESE_CHINA.ZHS16GBK,AL16UTF16, AL32UTF8
  des_clob   CLOB;
  src_clob   BFILE := BFILENAME('GENERIC',src_file_name);
  des_offset NUMBER := 1;
  src_offset NUMBER := 1;
  src_csid number := dbms_lob.default_csid;
  ctx_lang   NUMBER := dbms_lob.default_lang_ctx;
  warning    NUMBER;
  
  -- Define a pre-reading size.
  src_clob_size NUMBER;
  
  -- Define local variable for Native Dynamic SQL.
  stmt VARCHAR2(2000);

BEGIN

  -- Opening source file is a mandatory operation.
  IF dbms_lob.fileexists(src_clob) = 1 AND dbms_lob.isopen(src_clob) = 0 THEN
    src_clob_size := dbms_lob.getlength(src_clob);
    dbms_lob.open(src_clob,DBMS_LOB.LOB_READONLY);
  END IF;
  
  -- Assign dynamic string to statement.
  stmt := 'UPDATE '||table_name||' '
       || 'SET    '||column_name||' = empty_clob() '
       || 'WHERE  '||primary_key_name||' = '||''''||primary_key_value||''' '
       || 'RETURNING '||column_name||' INTO :locator';

  -- Run dynamic statement.       
  EXECUTE IMMEDIATE stmt USING OUT des_clob;
 
  -- Read and write file to CLOB, close source file and commit.
  
  --bfile_csid   => dbms_lob.default_csid
  --CLOB CHARACTER SET ANY_CS
  dbms_lob.loadclobfromfile( dest_lob => des_clob
                           , src_bfile    => src_clob
                           , amount       => dbms_lob.getlength(src_clob)
                           , dest_offset  => des_offset
                           , src_offset   => src_offset
                           , bfile_csid   => src_csid
                           , lang_context => ctx_lang
                           , warning      => warning );

  -- Close open source file.
  dbms_lob.close(src_clob);

  -- Commit write and conditionally acknowledge it.
  IF src_clob_size = dbms_lob.getlength(des_clob) THEN
    $IF $$DEBUG = 1 $THEN
      dbms_output.put_line('Success!');
    $END
    COMMIT;
  ELSE
    $IF $$DEBUG = 1 $THEN
      dbms_output.put_line('Failure.');
    $END
    RAISE dbms_lob.operation_failed;
  END IF;
  
END load_clob_from_file;
/

4. 读入前测试

SQL> SELECT item_id
  2  ,      item_title
  3  ,      dbms_lob.getlength(item_desc) AS "SIZE"
  4  FROM   item
  5  WHERE  dbms_lob.getlength(item_desc) > 0;

ITEM_ID ITEM_TITLE                                               SIZE
------- -------------------------------------------------- ----------
   1037 The Lord of the Rings - Fellowship of the Ring              6
   1038 The Lord of the Rings - Fellowship of the Ring              6
   1039 The Lord of the Rings - Fellowship of the Ring              6

5. 读入大对象源文件到item_desc列（大对象）

SQL> -- Insert description in all matching rows.
SQL> BEGIN
  2    FOR i IN (SELECT item_id
  3              FROM   item
  4              WHERE  item_title = 'The Lord of the Rings - Fellowship of the Ring'
  5              AND    item_type IN (SELECT common_lookup_id
  6                                   FROM   common_lookup
  7                                   WHERE  common_lookup_table = 'ITEM'
  8                                   AND    common_lookup_column = 'ITEM_TYPE'
  9                                   AND    REGEXP_LIKE(common_lookup_type,'^(dvd|vhs)*','i'))) LOOP
 10      -- Call procedure for matching rows.
 11      load_clob_from_file( src_file_name     => 'LOTRFellowship.txt'
 12                         , table_name        => 'ITEM'
 13                         , column_name       => 'ITEM_DESC'
 14                         , primary_key_name  => 'ITEM_ID'
 15                         , primary_key_value => TO_CHAR(i.item_id)
 16     );
 17    END LOOP;
 18  END;
 19  /

PL/SQL 过程已成功完成。

6. 读入后测试

SQL> -- Check after load.
SQL> SELECT item_id
  2  ,      item_title
  3  ,      dbms_lob.getlength(item_desc) AS "SIZE"
  4  FROM   item
  5  WHERE  dbms_lob.getlength(item_desc) > 0;

ITEM_ID ITEM_TITLE                                               SIZE
------- -------------------------------------------------- ----------
   1037 The Lord of the Rings - Fellowship of the Ring           5056
   1038 The Lord of the Rings - Fellowship of the Ring           5056
   1039 The Lord of the Rings - Fellowship of the Ring           5056

7. Toad中验证