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 BEGINIF 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);
EXCEPTIONWHEN 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/frankConnected.
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\dir01/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 freeSQL>可以看到生成了两个文件，emp.dat就是最后生成的dump文件，另外一个是产生的Pl/SQL代码文件。
来看下PL/SQL代码(emp.dat.sql文件)
DECLAREv_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;
BEGINEXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''DD/MM/YYYY''';
v_eol := CASEWHEN 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_sqlBULK 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);
EXCEPTIONWHEN 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 := CASEWHEN 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
