oracle导入平面文件,oracle导出平面文件

一下是代码，新建了一个程序包：

create or replace package expfile

authid current_user

as

/*将查询获得的数据导出到平面文件，创建控制文件，用以重新加载数据到oracle表中

p_query = 查询sql语句，该语句的查询的结果会被卸载到平面文件

p_tname = 需要load数据的表

p_mode = 重新加载的模式replace|append|truncate

p_dir = ctl(控制文件)和dat(数据文件)写入的目录

p_filename = 写入的文件名，后缀名.ctl和.dat分别表示控制文件和数据文件

p_separator = 字段分隔符。默认为一个逗号

p_enclosure = 定义要被分装进字段的内容，比如换行符、制表符

p_terminator = 行结束标记，默认为"|\n"on unix或者"|\r\n".你需要将此重写。

*/

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;

end;

create or replace package body expfile

As

/*

g_theCursor 打开一个游标，并且返回一个整型。

g_descTbl  是一个PL/SQL表，将保存dbms_sql.desc_tab调用的输出

g_nl   换行符，在需要内嵌有换行符的串中会使用这个变量。

*/

g_theCursor integer default dbms_sql.open_cursor;

g_descTbl   dbms_sql.desc_tab;

g_nl             varchar2(2) default chr(10);

/*函数将字符转换为一个十六进制数*/

function to_hex(p_str in varchar2) return varchar2

is

begin

return to_char(ascii(p_str),'fm0x');

end;

/*对系统平台进行判断，如果是windows，行结束符就是一个两字符的串，而大多数其他

平台上的行结束符只是单字符。dbms_utility程序包get_parameter_value函数可以读取所有函数

读取了control_files参数，并查找其中是否存在\,如果有，则说明在windows平台

*/

function is_windows return boolean

is

l_cfiles varchar2(4000);

l_dummy  number;

begin

if(dbms_utility.get_parameter_value('control_files',l_dummy,l_cfiles)>0)

then

return instr(l_cfiles,'\')>0;

Else

Return False;

end if;

end;

/*

创建一个控制文件来重新加载卸载的数据。

*/

procedure dump_ctl(p_dir in varchar2,

p_filename in varchar2,

p_tname in varchar2,

p_mode in varchar2,

p_separator in varchar2,

p_enclosure in varchar2,

p_terminator in varchar2)

is

l_output  utl_file.file_type;

l_sep     varchar2(5);

l_str     varchar2(5) := chr(10);

begin

if (is_windows)

then

l_str := chr(13)||chr(10);

end if;

l_output := utl_file.fopen(p_dir,p_filename||'.ctl','w');

utl_file.put_line(l_output,'load data');

utl_file.put_line(l_output,'infile '''||

p_filename||'.dat'' "str x'''||

utl_raw.cast_to_raw(p_terminator||l_str)||'''"');

utl_file.put_line(l_output,'into table '|| p_tname);

Utl_File.Put_Line(L_Output,'fields terminated by X'''|| To_Hex(P_Separator)||''' enclosed by X '''||

to_hex(p_enclosure)||''' ');

utl_file.put_line(l_output,'(');

/*遍历表的desc*/

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

/*将NLS_DATE_FORMAT保存到一个变量中，从而在将数据转储到磁盘上时可以把它改为一种保留日期和时间的格式*/

select value

into l_datefmt

From Nls_Session_Parameters

where parameter = 'NLS_DATE_FORMAT';

execute immediate  'alter session  set nls_date_format = ''ddmmyyyyhh24miss''';

Begin

/*解析并描述这个查询。将G_DESCTBL 设置为L_DESTBL来重置全局表；否则，其中会包含前DESCRIBE生成的数据

而不只是当前查询生成的数据。一旦完成，再调用DUMP_CTL具体创建控制文件

*/

Dbms_Sql.Parse(G_Thecursor,P_Query,Dbms_Sql.Native);

g_descTbl := l_descTbl;

dbms_sql.describe_columns(g_theCursor,l_colCnt,g_descTbl );

/*

*/

dump_ctl(p_dir,p_filename,p_tname,p_mode,p_separator,p_enclosure,p_terminator);

/*

将每一个column绑定成一个varchar2(4000)。不用关心这个数是什么类型，每一个东西都可以变成string

现在可以具体数据转储到磁盘上。首先将每一个列定义为varchar2(4000)来获取数据。所有类型(number

date row)都要转换为 varchar2.

*/

For I In 1 .. L_Colcnt Loop

dbms_sql.define_column(g_theCursor,i,l_columnValue,4000);

end loop;

/*

现在打开数据文件准备些，从查询获取所有行，并将其打印到数据文件

*/

l_cnt := dbms_sql.execute(g_theCursor);

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);

/*将日期格式设置回原来的样子

*/

execute immediate'alter session set nls_date_format = ''' || l_datefmt|| '''';

return l_cnt;

exception

when others then

execute immediate

'alter session set nls_date_format = ''' || l_datefmt|| '''';

RAISE;

end;

end run;

end expfile;