[Oracle Utility] Adrian Billington’s data_dump

Adiran在他的网站上www.oracle-developer.net上给出一个有意思的Utility – data_dump， 用来把一个表中的数据dump到一个文件中。表面上看好像没啥大的价值，毕竟可以用export data pump来导出数据， 但是data_dump支持对给定的一个SQL语句来导出数据，而且可以指定每行数据的field之间的分隔符。 这个utility用起来也是非常方便，就是一个简单的存储过程调用而已。

看看他的代码，

-- ---------------------------------------------------------------------------------------------------
--
-- Script:      data_dump.sql
--
-- Author:      Adrian Billington
--              www.oracle-developer.net
--
-- Description: A standalone procedure to dump the results of a query to delimited flat-file. This
--              utility supports Oracle 8i upwards.
--
--              Note that the dynamic code that is built to perform the data dump can optionally be
--              written to a separate file.
--
-- Usage:       Usage is quite simple. A dynamic query is passed in as a parameter. As this uses
--              DBMS_SQL to parse the SQL, all expressions must have an alias.
--
--              a) Dump the contents of a table
--                 ----------------------------
--
--                 BEGIN
--                    data_dump( query_in     => 'SELECT * FROM table_name',
--                               file_in      => 'table_name.csv',
--                               directory_in => 'LOG_DIR',
--                               delimiter_in => ',' );
--                 END;
--                 /
--
--              b) Use an expression in the query
--                 ------------------------------
--
--                 BEGIN
--                    data_dump( query_in     => 'SELECT ''LITERAL'' AS alias_name FROM table_name',
--                               file_in      => 'table_name.csv',
--                               directory_in => 'LOG_DIR',
--                               delimiter_in => ',' );
--                 END;
--                 /
--
--             See list of parameters for the various other options available.
--
-- ---------------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE data_dump (
                            query_in        IN VARCHAR2,
                            file_in         IN VARCHAR2,
                            directory_in    IN VARCHAR2,
                            nls_date_fmt_in IN VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS',
                            write_action_in IN VARCHAR2 DEFAULT 'W',
                            array_size_in   IN PLS_INTEGER DEFAULT 1000,
                            delimiter_in    IN VARCHAR2 DEFAULT NULL,
                            dump_code_in    IN BOOLEAN DEFAULT FALSE) AUTHID CURRENT_USER IS

   v_fh           UTL_FILE.FILE_TYPE;
   v_ch           BINARY_INTEGER      := DBMS_SQL.OPEN_CURSOR;
   v_sql          VARCHAR2(32767)     := query_in;
   v_dir          VARCHAR2(512)       := directory_in;
   v_outfile      VARCHAR2(128)       := file_in;
   v_sqlfile      VARCHAR2(128)       := file_in||'.sql';
   v_arr_size     PLS_INTEGER         := array_size_in;
   v_col_cnt      PLS_INTEGER         := 0;
   v_delimiter    VARCHAR2(1)         := NULL;
   v_write_action VARCHAR2(1)         := write_action_in;
   v_nls_date_fmt VARCHAR2(30)        := nls_date_fmt_in;
   v_dummy        NUMBER;
   v_type         VARCHAR2(8);
   t_describe     DBMS_SQL.DESC_TAB;
   t_plsql        DBMS_SQL.VARCHAR2A;

   /* Procedure to output code for debug and assign plsql variable... */
   PROCEDURE put (
             string_in IN VARCHAR2
             ) IS
   BEGIN
      IF dump_code_in THEN
         UTL_FILE.PUT_LINE(v_fh,string_in);
      END IF;
      t_plsql(t_plsql.COUNT + 1) := string_in;
   END put;

BEGIN

   /* Open the file that the dynamic PL/SQL will be written to for debug... */
   IF dump_code_in THEN
      v_fh := UTL_FILE.FOPEN(v_dir, v_sqlfile, 'W', 32767);
   END IF;

   /* Parse the query that will be used to fetch all the data to be written out... */
   DBMS_SQL.PARSE(v_ch, v_sql, DBMS_SQL.NATIVE);

   /* Now describe the dynamic SQL to analyze the number of columns in the query... */
   DBMS_SQL.DESCRIBE_COLUMNS(v_ch, v_col_cnt, t_describe);

   /* Now begin the dynamic PL/SQL... */
   put('DECLARE');
   put('   v_fh     UTL_FILE.FILE_TYPE;');
   put('   v_eol    VARCHAR2(2);');
   put('   v_eollen PLS_INTEGER;');
   put('   CURSOR cur_sql IS');
   put('      '||REPLACE(v_sql,'"','''''')||';');

   /* Now loop through the describe table to declare arrays in the dynamic PL/SQL... */
   FOR i IN t_describe.FIRST .. t_describe.LAST LOOP
      IF t_describe(i).col_type = 2 THEN
         v_type := 'NUMBER';
      ELSIF t_describe(i).col_type = 12 THEN
         v_type := 'DATE';
      ELSE
         v_type := 'VARCHAR2';
      END IF;
      put('   "'||t_describe(i).col_name||'" DBMS_SQL.'||v_type||'_TABLE;');
   END LOOP;

   /* Syntax to set the date format to preserve time in the output, open the out file and start to collect... */
   put('BEGIN');
   put('   EXECUTE IMMEDIATE ''ALTER SESSION SET NLS_DATE_FORMAT = '''''||v_nls_date_fmt||''''''';');
   put('   v_eol := CASE');
   put('               WHEN DBMS_UTILITY.PORT_STRING LIKE ''IBMPC%''');
   put('               THEN CHR(13)||CHR(10)');
   put('               ELSE CHR(10)');
   put('            END;');
   put('   v_eollen := LENGTH(v_eol);');
   put('   v_fh := UTL_FILE.FOPEN('''||v_dir||''','''||v_outfile||''','''||v_write_action||''');');
   put('   OPEN cur_sql;');
   put('   LOOP');
   put('      FETCH cur_sql');

   IF t_describe.COUNT > 1 THEN

      put('      BULK COLLECT INTO "'||t_describe(t_describe.FIRST).col_name||'",');

      /* Add all other arrays into the fetch list except the last... */
      FOR i IN t_describe.FIRST + 1 .. t_describe.LAST - 1 LOOP
         put('                        "'||t_describe(i).col_name||'",');
      END LOOP;

      /* Add in the last array and limit... */
      put('                        "'||t_describe(t_describe.LAST).col_name||'" LIMIT '||v_arr_size||';');

   ELSE
      /* Just output the one collection and LIMIT... */
      put('      BULK COLLECT INTO "'||t_describe(t_describe.FIRST).col_name||'" LIMIT '||v_arr_size||';');

   END IF;

   /* Now add syntax to loop though the fetched array and write out the values to file... */
   put('      IF "'||t_describe(t_describe.FIRST).col_name||'".COUNT > 0 THEN');
   put('         FOR i IN "'||t_describe(t_describe.FIRST).col_name||'".FIRST .. "'||
                                  t_describe(t_describe.FIRST).col_name||'".LAST LOOP');

   FOR i IN t_describe.FIRST .. t_describe.LAST LOOP
      put('            UTL_FILE.PUT(v_fh,'''||v_delimiter||''' ||"'||t_describe(i).col_name||'"(i));');
      v_delimiter := NVL(delimiter_in,',');
   END LOOP;

   /* Add a new line marker into the file and move on to next record... */
   put('            UTL_FILE.NEW_LINE(v_fh);');
   put('         END LOOP;');

   /* Complete the IF statement... */
   put('      END IF;');

   /* Add in an EXIT condition and complete the loop syntax... */
   put('      EXIT WHEN cur_sql%NOTFOUND;');
   put('   END LOOP;');
   put('   CLOSE cur_sql;');
   put('   UTL_FILE.FCLOSE(v_fh);');

   /* Add in some exception handling... */
   put('EXCEPTION');
   put('   WHEN UTL_FILE.INVALID_PATH THEN');
   put('      DBMS_OUTPUT.PUT_LINE(''Error - invalid path.'');');
   put('      RAISE;');
   put('   WHEN UTL_FILE.INVALID_MODE THEN');
   put('      DBMS_OUTPUT.PUT_LINE(''Error - invalid mode.'');');
   put('      RAISE;');
   put('   WHEN UTL_FILE.INVALID_OPERATION THEN');
   put('      DBMS_OUTPUT.PUT_LINE(''Error - invalid operation.'');');
   put('      RAISE;');
   put('   WHEN UTL_FILE.INVALID_FILEHANDLE THEN');
   put('      DBMS_OUTPUT.PUT_LINE(''Error - invalid filehandle.'');');
   put('      RAISE;');
   put('   WHEN UTL_FILE.WRITE_ERROR THEN');
   put('      DBMS_OUTPUT.PUT_LINE(''Error - write error.'');');
   put('      RAISE;');
   put('   WHEN UTL_FILE.READ_ERROR THEN');
   put('      DBMS_OUTPUT.PUT_LINE(''Error - read error.'');');
   put('      RAISE;');
   put('   WHEN UTL_FILE.INTERNAL_ERROR THEN');
   put('      DBMS_OUTPUT.PUT_LINE(''Error - internal error.'');');
   put('      RAISE;');
   put('END;');

   /* Now close the cursor and sql file... */
   DBMS_SQL.CLOSE_CURSOR(v_ch);
   IF dump_code_in THEN
      UTL_FILE.FCLOSE(v_fh);
   END IF;

   /*
    * Execute the t_plsql collection to dump the data. Use DBMS_SQL as we have a collection
    * of syntax...
    */
   v_ch := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(v_ch, t_plsql, t_plsql.FIRST, t_plsql.LAST, TRUE, DBMS_SQL.NATIVE);
   v_dummy := DBMS_SQL.EXECUTE(v_ch);
   DBMS_SQL.CLOSE_CURSOR(v_ch);

EXCEPTION
   WHEN UTL_FILE.INVALID_PATH THEN
      DBMS_OUTPUT.PUT_LINE('Error - invalid path.');
      RAISE;
   WHEN UTL_FILE.INVALID_MODE THEN
      DBMS_OUTPUT.PUT_LINE('Error - invalid mode.');
      RAISE;
   WHEN UTL_FILE.INVALID_OPERATION THEN
      DBMS_OUTPUT.PUT_LINE('Error - invalid operation.');
      RAISE;
   WHEN UTL_FILE.INVALID_FILEHANDLE THEN
      DBMS_OUTPUT.PUT_LINE('Error - invalid filehandle.');
      RAISE;
   WHEN UTL_FILE.WRITE_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('Error - write error.');
      RAISE;
   WHEN UTL_FILE.READ_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('Error - read error.');
      RAISE;
   WHEN UTL_FILE.INTERNAL_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('Error - internal error.');
      RAISE;
END;
/

CREATE OR REPLACE PUBLIC SYNONYM data_dump FOR data_dump;
GRANT EXECUTE ON data_dump TO PUBLIC;

调用这个存储过程需要传入几个必须的参数，

query_in       ====> 针对哪个SQL语句执行结果进行数据导出

file_in            ====> 最后生成的dump文件名

directory_in   ====> 生成的dump文件所在的目录

其余的都是可选的参数，但是有两个要注意下，

nls_date_fmt_in  ====> 设置目标数据中出现的date类型数据以何种方式显示

dump_code_in ====> 如果设置成true，则会是生成一个SQL文件，把该存储过程中动态产生的PL/SQL语句输出出来，方便debug

其实data_dump的基本流程就是分析给定的SQL语句，然后动态生成最后要执行的PL/SQL代码，最后再执行这些PL/SQL 代码生成最后所需要的dump文件。

这个存储过程用到了DBMS_SQL来执行动态SQL语句，这个比NDS(Native Dynamic SQL)要复杂一些，但是功能也强大许多，比如它可以用来分析出给定的SQL语句返回结果的列有多少，每个列的类型是啥。 如下这部分代码，

/* Now loop through the describe table to declare arrays in the dynamic PL/SQL... */
   FOR i IN t_describe.FIRST .. t_describe.LAST LOOP
      IF t_describe(i).col_type = 2 THEN
         v_type := 'NUMBER';
      ELSIF t_describe(i).col_type = 12 THEN
         v_type := 'DATE';
      ELSE
         v_type := 'VARCHAR2';
      END IF;
      put('   "'||t_describe(i).col_name||'" DBMS_SQL.'||v_type||'_TABLE;');
   END LOOP;

注意这部分代码是如何为确定每个列的类型， 并定义一个该类型的数组变量的。该变量的名字就是列名加上引号， 类型对应于DBMS_SQL中提供的集合类型，例如DBMS_SQL.NUMBER_TABLE, DBMS_SQL.VARCHAR2_TABLE。

DBMS_SQL的执行过程一般如下，

(1) Open Cursor

      v_ch           BINARY_INTEGER      := DBMS_SQL.OPEN_CURSOR;

(2) Parse

/* Parse the query that will be used to fetch all the data to be written out... */
DBMS_SQL.PARSE(v_ch, v_sql, DBMS_SQL.NATIVE);

(3) Describe Columns

/* Parse the query that will be used to fetch all the data to be written out... */
DBMS_SQL.PARSE(v_ch, v_sql, DBMS_SQL.NATIVE);

(4) Execute

/* Now close the cursor and sql file... */
DBMS_SQL.CLOSE_CURSOR(v_ch);

(5) Close Cursor

/* Now close the cursor and sql file... */
DBMS_SQL.CLOSE_CURSOR(v_ch);

举个例子看看怎么用这个存储过程，如下

(1)  首先，需要做一些准备工作

创建一个Oracle Directory用来存放最后生成的dump文件

SQL> conn frank/frank
Connected.
SQL> CREATE
DIRECTORY xt_dir AS 'e:\oracle\dir';

 

(2) 调用存储过程data_dump

begin
  data_dump(query_in => 'select * from t',
            file_in => 'emp.dat',
            directory_in => 'XT_DIR',
            nls_date_fmt_in => 'DD/MM/YYYY',
            dump_code_in => true);
end;

为了看看过程中生成的动态PL/SQL语句，把参数dump_code_in设成true.

(注意，参数directory_in的值XT_DIR需要大写，如果小写会报错，说找不到目标directory.)

(3) 查看生成的文件

SQL> host dir e:\oracle\dir
 Volume in drive E is New Volume
 Volume Serial Number is 186E-F112

 Directory of e:\oracle\dir

01/04/2010  10:39 AM    <DIR>          .
01/04/2010  10:39 AM    <DIR>          ..
01/04/2010  10:39 AM         1,110,069 emp.dat
01/04/2010  10:39 AM             1,763 emp.dat.sql
               2 File(s)      1,111,832 bytes
               2 Dir(s)  96,405,520,384 bytes free

SQL>

可以看到生成了两个文件，emp.dat就是最后生成的dump文件，另外一个是产生的Pl/SQL代码文件。

来看下PL/SQL代码(emp.dat.sql文件)

DECLARE
   v_fh     UTL_FILE.FILE_TYPE;
   v_eol    VARCHAR2(2);
   v_eollen PLS_INTEGER;
   CURSOR cur_sql IS
      select * from t;
   "ID" DBMS_SQL.NUMBER_TABLE;
   "VAL" DBMS_SQL.VARCHAR2_TABLE;
BEGIN
   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''DD/MM/YYYY''';
   v_eol := CASE
               WHEN DBMS_UTILITY.PORT_STRING LIKE 'IBMPC%'
               THEN CHR(13)||CHR(10)
               ELSE CHR(10)
            END;
   v_eollen := LENGTH(v_eol);
   v_fh := UTL_FILE.FOPEN('XT_DIR','emp.dat','W');
   OPEN cur_sql;
   LOOP
      FETCH cur_sql
      BULK COLLECT INTO "ID",
                        "VAL" LIMIT 1000;
      IF "ID".COUNT > 0 THEN
         FOR i IN "ID".FIRST .. "ID".LAST LOOP
            UTL_FILE.PUT(v_fh,'' ||"ID"(i));
            UTL_FILE.PUT(v_fh,',' ||"VAL"(i));
            UTL_FILE.NEW_LINE(v_fh);
         END LOOP;
      END IF;
      EXIT WHEN cur_sql%NOTFOUND;
   END LOOP;
   CLOSE cur_sql;
   UTL_FILE.FCLOSE(v_fh);
EXCEPTION
   WHEN UTL_FILE.INVALID_PATH THEN
      DBMS_OUTPUT.PUT_LINE('Error - invalid path.');
      RAISE;
   WHEN UTL_FILE.INVALID_MODE THEN
      DBMS_OUTPUT.PUT_LINE('Error - invalid mode.');
      RAISE;
   WHEN UTL_FILE.INVALID_OPERATION THEN
      DBMS_OUTPUT.PUT_LINE('Error - invalid operation.');
      RAISE;
   WHEN UTL_FILE.INVALID_FILEHANDLE THEN
      DBMS_OUTPUT.PUT_LINE('Error - invalid filehandle.');
      RAISE;
   WHEN UTL_FILE.WRITE_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('Error - write error.');
      RAISE;
   WHEN UTL_FILE.READ_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('Error - read error.');
      RAISE;
   WHEN UTL_FILE.INTERNAL_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('Error - internal error.');
      RAISE;
END;

注意其中这部分代码用来判断Oracle Server的操作系统平台对换行符的处理

v_eol := CASE
           WHEN DBMS_UTILITY.PORT_STRING LIKE 'IBMPC%'
           THEN CHR(13)||CHR(10)
           ELSE CHR(10)
         END;

用到的是DBMS_UTILITY中的PORT_STRING, 如果是windows平台返回如下结果，

SQL> select dbms_utility.port_string from dual;

PORT_STRING
-------------------------------------------------------
IBMPC/WIN_NT-8.1.0