将需要信息 导出成 csv,txt等格式
create or replace procedure sql_to_csv(p_query in varchar2, --sql query statement
p_dir in varchar2, --the directory of file
p_filename in varchar2, --the export filename
p_max_linesize in number default 32000 --max linesize,must less than 32787
) is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_colCnt number := 0;
l_separator varchar2(1);
l_descTbl dbms_sql.desc_tab;
begin
--open file
l_output :=
(p_dir, p_filename, ‘w‘, p_max_linesize);
--define date format
execute immediate ‘alter session set nls_date_format=‘‘yyyy-mm-dd hh24:mi:ss‘‘‘;
--open cursor
dbms_sql.parse(l_theCursor, p_query, dbms_sql.native);
dbms_sql.describe_columns(l_theCursor, l_colCnt, l_descTbl);
--dump table column name
for i in 1 .. l_colCnt loop
/*
utl_file.put(l_output,
l_separator || ‘"‘ || l_descTbl(i).col_name || ‘"‘);
*/ --注释1
dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
l_separator := ‘,‘;
end loop;
-- utl_file.new_line(l_output); --注释1和本行都注释表示输出到文件不输出表列名
--execute the query statement
l_status := dbms_sql.execute(l_theCursor);
-- generate file
--dump table column value
while (dbms_sql.fetch_rows(l_theCursor) > 0) loop
l_separator := ‘‘;--表示文件里面数据行首分隔符
for i in 1 .. l_colCnt loop
dbms_sql.column_value(l_theCursor, i, l_columnValue);
utl_file.put(l_output, l_separator || l_columnValue); --generate txt file
--utl_file.put( l_output, l_separator || ‘"‘ ||trim(both ‘ ‘ from replace(l_columnValue,‘"‘,‘""‘)) || ‘"‘); -- generate csv file
l_separator := ‘,‘; --separator表示文件里面数据分隔符
end loop;
utl_file.new_line(l_output);
end loop;
--close cursor
dbms_sql.close_cursor(l_theCursor);
--close file
utl_file.fclose(l_output);
exception
when others then
raise;
end sql_to_csv;
/
刚好收到个需求，将oracle的数据 导入到 mysql .因环境工具有限，又需导出数据比较简单，没有复杂字符类型。
思路，导出目标数据为txt 文件， 在load进去mysql
exec test.sql_to_csv(‘select * from test.yiya_0611‘,‘YIYA’_0611‘,‘exp0611.txt‘); ----需要数据查询语句，directory,导出文件名称
mysql：数据load入
load data local infile "/data/dir_dump/exp0611.txt" into table mubiao.yiya_0611
fields terminated by ‘,‘ lines terminated by ‘\n‘;