oracle执行sql文件返回结果,将oracle SQL语句执行结果导入文本文件

将oracle SQL语句执行结果导入文本文件

1 创建directory

create or replace directory DIR

as 'd:\temp';

为用户赋予该directory的权限

grant all on directroy dir to public;

2 创建函数function

create or replace function tofile(p_query in varchar2,

p_separator in varchar2,

p_dir in varchar2,

p_filename in varchar2)

return number

is

l_cursor integer default dbms_sql.open_cursor;

l_output utl_file.file_type;

l_columnValue varchar2(2000);

l_status integer;

l_colCnt number default 0;

l_separator varchar2(10) default '';

l_cnt number default 0;

rec_tab dbms_sql.desc_tab;

col_num number;

begin

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

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

/*写入该SQL查询结果的列名,以空格隔开*/

dbms_sql.describe_columns(l_cursor,l_colCnt,rec_tab);

col_num := rec_tab.first;

if( col_num is not null) then

loop

utl_file.put( l_output , l_separator||rec_tab(col_num).col_name);

col_num := rec_tab.next(col_num);

l_separator :=' ';

EXIT WHEN (col_num IS NULL);

end loop;

end if;

utl_file.new_line( l_output );

/*写入SQL查询结果的列值*/

for i in 1.. l_colCnt loop

dbms_sql.define_column( l_cursor , i,

l_columnValue,2000);

end loop;

l_status :=dbms_sql.execute(l_cursor);

loop

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

l_separator :='';

for i in 1 .. l_colCnt loop

dbms_sql.column_value( l_cursor ,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_cursor );

utl_file.fclose( l_output );

return l_cnt;

end tofile;

3 创建执行该function的存储过程procedure

create or replace procedure test_tofile

as

l_rows number;

l_filename varchar2(100);

v_sql varchar2(100);

l_temp varchar2(100);

begin

v_sql := 'select to_char(sysdate,''yyyymmdd'') from dual';

EXECUTE IMMEDIATE v_sql

INTO l_temp;

l_filename := 'dbperform_'|| l_temp || '.txt';

l_rows := tofile( 'SELECT fs.tablespace_name TABLESPACE_NAME,

df.totalspace TABLESPACE_TOTAL_SIZE,

(df.totalspace - fs.freespace) MB_USED,

fs.freespace MB_FREE,

round(100 * (fs.freespace / df.totalspace),2) PCT_FREE

FROM

(SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) TotalSpace FROM dba_data_files GROUP BY tablespace_name ) df,

(SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) FreeSpace FROM dba_free_space GROUP BY tablespace_name ) fs

WHERE df.tablespace_name = fs.tablespace_name(+)

order by PCT_FREE ASC',

',', 'DIR', l_filename );

dbms_output.put_line(l_rows);

end;

/*这里的'DIR'指明了directory dir,不过,由于是在WINDOWS环境,需要大写该directory名*/

4 调用该存储过程

SQL>exec test_tofile();