|
FILE.put_line (l_output, '(');
FOR i IN 1 .. g_desctbl.COUNT
LOOP
IF (g_desctbl (i).col_type = 12)
THEN
UTL_FILE.put (l_output, l_sep || g_desctbl (i).col_name || ' date ''ddmmyyyyhh24miss'' ');
ELSE
UTL_FILE.put (l_output, l_sep || g_desctbl (i).col_name || ' char(' || TO_CHAR (g_desctbl (i).col_max_len * 2) || ' )');
END IF;
l_sep := ',' || g_nl;
END LOOP;
UTL_FILE.put_line (l_output, g_nl || ')');
UTL_FILE.fclose (l_output);
END;
FUNCTION quote (p_str IN VARCHAR2, p_enclosure IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN p_enclosure || REPLACE (p_str, p_enclosure, p_enclosure || p_enclosure) || p_enclosure;
END;
FUNCTION run (p_query IN VARCHAR2,
p_tname IN VARCHAR2,
p_mode IN VARCHAR2 DEFAULT 'REPLACE',
p_dir IN VARCHAR2,
p_filename IN VARCHAR2,
p_separator IN VARCHAR2 DEFAULT ',',
p_enclosure IN VARCHAR2 DEFAULT '"',
p_terminator IN VARCHAR2 DEFAULT '|')
RETURN NUMBER
IS
l_output UTL_FILE.file_type;
l_columnvalue VARCHAR2 (4000);
l_colcnt NUMBER DEFAULT 0;
l_separator VARCHAR2 (10) DEFAULT '';
l_cnt NUMBER DEFAULT 0;
l_line LONG;
l_datefmt VARCHAR2 (255);
l_desctbl DBMS_SQL.desc_tab;
BEGIN
SELECT VALUE
INTO l_datefmt
FROM nls_session_parameters
WHERE parameter = 'NLS_DATE_FORMAT';
/*
Set the date format to a big numeric string. Avoids
all NLS issues and saves both the time and date.
*/
EXECUTE IMMEDIATE 'alter session set nls_date_format=''ddmmyyyyhh24miss'' ';
/*
Set up an exception block so that in the event of any
error, we can at least reset the date format.
*/
BEGIN
/*
Parse and describe the query. We reset the
descTbl to an empty table so .count on it
will be reliable.
*/
DBMS_SQL.parse (g_thecursor, p_query, DBMS_SQL.native);
g_desctbl := l_desctbl;
DBMS_SQL.describe_columns (g_thecursor, l_colcnt, g_desctbl);
/*
Create a control file to reload this data
into the desired table.
*/
dump_ctl (p_dir,
p_filename,
p_tname,
p_mode,
p_separator,
p_enclosure,
p_terminator);
/*
Bind every single column to a varchar2(4000). We don't care
if we are fetching a number or a date or whatever.
Everything can be a string.
*/
FOR i IN 1 .. l_colcnt
LOOP
DBMS_SQL.define_column (g_thecursor,
i,
l_columnvalue,
4000);
END LOOP;
/*
Run the query - ignore the output of execute. It is only
valid when the DML is an insert/update or delete.
*/
l_cnt := DBMS_SQL.execute (g_thecursor);
/*
Open the file to write output to and then write the
delimited data to it.
*/
l_output :=
UTL_FILE.fopen (p_dir,
p_filename || '.dat',
'w',
32760);
LOOP
EXIT WHEN (DBMS_SQL.fetch_rows (g_thecursor) <= 0);
l_separator := '';
l_line := NULL;
FOR i IN 1 .. l_colcnt
LOOP
DBMS_SQL.COLUMN_VALUE (g_thecursor, i, l_columnvalue);
l_line := l_line || l_separator || quote (l_columnvalue, p_enclosure);
l_separator := p_separator;
END LOOP;
l_line := l_line || p_terminator;
UTL_FILE.put_line (l_output, l_line);
l_cnt := l_cnt + 1;
END LOOP;
UTL_FILE.fclose (l_output);
/*
Now reset the date format and return the number of rows
written to the output file.
*/
EXECUTE IMMEDIATE 'alter session set nls_date_format=''' || l_datefmt || '''';
RETURN l_cnt;
EXCEPTION
/*
In the event of ANY error, reset the data format and
re-raise the error.
*/
WHEN OTHERS
THEN
EXECUTE IMMEDIATE 'alter session set nls_date_format=''' || l_datefmt || '''';
RAISE;
END;
END run;
END unloader;
/
更多参考
使用 DBMS_PROFILER 定位 PL/SQL 瓶颈代码
使用PL/SQL Developer剖析PL/SQL代码
对比 PL/SQL profiler 剖析结果
PL/SQL Profiler 剖析报告生成html
DML Error Logging 特性
PL/SQL --> 游标
PL/SQL --> 隐式游标(SQL%FOUND)
批量SQL之 FORALL 语句
批量SQL之 BULK COLLECT 子句
PL/SQL 集合的初始化与赋值
PL/SQL 联合数组与嵌套表
SQL tuning 步骤
高效SQL语句必杀技
父游标、子游标及共享游标
绑定变量及其优缺点
dbms_xplan之display_cursor函数的使用
dbms_xplan之display函数的使用
执行计划中各字段各模块描述
使用 EXPLAIN PLAN 获取SQL语句执行计划
|