oracle导出text文件分隔符,使用UTL_FILE转储数据为逗号分隔符文件

使用UTL_FILE转储数据为逗号分隔符文件

Using Create directory & UTL_FILE in Oracle

create or replace function dump_csv( p_query in varchar2,

p_separator in varchar2

default ',',

p_dir in varchar2 ,

p_filename in varchar2 )

return number

AUTHID CURRENT_USER

is

l_output utl_file.file_type;

l_theCursor integer default dbms_sql.open_cursor;

l_columnValue varchar2(2000);

l_status integer;

l_colCnt number default 0;

l_separator varchar2(10) default '';

l_cnt number default 0;

begin

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

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

for i in 1 .. 255 loop

begin

dbms_sql.define_column( l_theCursor, i,

l_columnValue, 2000 );

l_colCnt := i;

exception

when others then

if ( sqlcode = -1007 ) then exit;

else

raise;

end if;

end;

end loop;

dbms_sql.define_column( l_theCursor, 1, l_columnValue,

2000 );

l_status := dbms_sql.execute(l_theCursor);

loop

exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );

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

l_separator := p_separator;

end loop;

utl_file.new_line( l_output );

l_cnt := l_cnt+1;

end loop;

dbms_sql.close_cursor(l_theCursor);

utl_file.fclose( l_output );

return l_cnt;

end dump_csv;

/

以下是使用样例:

[oracle@jumper tmp]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Fri May 13 15:04:41 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning option

JServer Release 9.2.0.4.0 - Production

SQL> select dump_csv('select * from scott.emp',',','/tmp','emp.csv') from dual;

DUMP_CSV('SELECT*FROMSCOTT.EMP',',','/TMP','EMP.CSV')

-----------------------------------------------------

14

SQL> ! more /tmp/emp.csv

7369,SMITH,CLERK,7902,17-DEC-80,800,,20

7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30

7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30

7566,JONES,MANAGER,7839,02-APR-81,2975,,20

7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30

7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30

7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10

7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20

7839,KING,PRESIDENT,,17-NOV-81,5000,,10

7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30

7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20

7900,JAMES,CLERK,7698,03-DEC-81,950,,30

7902,FORD,ANALYST,7566,03-DEC-81,3000,,20

7934,MILLER,CLERK,7782,23-JAN-82,1300,,10

历史上的今天...

>>

2008-05-13文章:

2007-05-13文章:

By eygle on 2005-05-13 15:24 |

Comments (14) |

SQL.PLSQL | 283 |

14 Comments

有没有办法直接导出成以TAB键分隔的文本文件

已经OK了,在命令提示符状态下可以直接键入一个TAB,就可以正常导出来.我之前在pl/sql developer中键入TAB会变成多个空格.

我执行这个命令出错

SQL> select dump_csv('select * from scott.emp',',','/tmp','emp.csv') from dual;

select dump_csv('select * from scott.emp',',','/tmp','emp.csv') from dual

*

第 1 行出现错误:

ORA-29280: 目录路径无效

ORA-06512: 在 "SYS.UTL_FILE", line 33

ORA-06512: 在 "SYS.UTL_FILE", line 436

ORA-06512: 在 "BEE.DUMP_CSV", line 17

ORA-06512: 在 line 1

这样才对，什么原因？

SQL> select dump_csv('select * from scott.emp',',','UTL_FILE_DIR','emp2.csv') from dual;

DUMP_CSV('SELECT*FROMSCOTT.EMP',',','UTL_FILE_DIR','EMP2.CSV')

--------------------------------------------------------------

14

SQL>

to l1t;

我在开头给出了链接:

http://www.eygle.com/archives/2005/04/using_create_di.html

你从程序也可以看到，传入的路径是给utl_file作为传入参数的，而utl_file.fopen需要一个directory作为参数。

对中文支持有些问题，出来都是乱码，而且不能在其中再使用字符集转换函数

中文OK了,不能将转换函数放在dump_csv的sql语句中,可以放在dbms_sql.column_value( l_theCursor, i, l_columnValue );后

--贴一个我最近写的,导出的数据以"",""分隔,这样用sqlload的时候遇上非法字符就不会出错了.

--设置数据库参数文件(initORCL.ora)

--加入一行：utl_file_dir = *

--重启数据库使参数生效。

create or replace procedure output_data

(

i_tablename in varchar2,

o_result out number,

o_errormessage out varchar2

)

as

cursor c1(m_tablename varchar2) is select column_name from user_tab_columns

where table_name=m_tablename order by column_id;

C2 C1%rowtype;

m_result number;

m_errormessage varchar2(512);

m_columns varchar2(10000);

m_sql varchar2(10000);

m_otext varchar2(10000);

beginflag number;

the_c1 integer;

fdbk INTEGER;

f_handle utl_file.file_type;

begin

beginflag :=0;

open c1(i_tablename);

loop

fetch c1 into c2;

exit when c1%notfound or c1%notfound is null;

--dbms_output.put_line('m_columns='||m_columns);

if beginflag = 0 then

m_columns := '''"'''||'||replace("'||c2.column_name||'"'||','||'''"'''

||','||''''''||')'||'||'||'''"''';

else

m_columns := m_columns||'||'||''','''||'||'||'''"'''||'||replace("'||

c2.column_name||'"'||','||'''"'''||','||''''''||')'||'||'||'''"''';

end if;

beginflag := 1;

end loop;

close c1;

m_sql :='select '||m_columns||' from '||i_tablename;

the_c1 :=dbms_sql.open_cursor;

dbms_sql.parse(the_c1,m_sql,dbms_sql.v7);

dbms_sql.define_column(the_c1,1,m_otext,3800);

fdbk := dbms_sql.execute(the_c1);

dbms_output.put_line('fdbk='||fdbk);

f_handle:=utl_file.fopen('/opt/oracle/data/',i_tablename||'.txt','w',32767);

loop

--fetch next row. exit when done.

exit when dbms_sql.fetch_rows (the_c1) = 0;

dbms_sql.column_value (the_c1, 1,m_otext);

utl_file.put_line(f_handle,replace(replace(m_otext,chr(10),'') ,chr(13),''));

end loop;

dbms_sql.close_cursor(the_c1);

utl_file.fclose(f_handle);

o_result:=0;

exception

when others then

dbms_sql.close_cursor(the_c1);

utl_file.fclose(f_handle);

m_result := sqlcode;

m_errormessage := m_result||substr(sqlerrm,1,200);

o_result:=-1;

o_errormessage := m_errormessage;

end;

/

var m_result number;

var m_errormessage varchar2(512);

--set NLA_LANG

exec output_data('TABLE_NAME',:m_result,:m_errormessage);

今天试了一下

utl_file.fopen(dir,'c:\test.txt','A');

中的dir必须加单引号而且必须大写才可以

SQL> select * from dba_directories where DIRECTORY_NAME='DIR';

小写就要报找不到路径

我出现了中文乱码问题，请问该如何转换？