oracle定时导出查询结果,如何定时从oracle数据库中导出查询某个表的结果为文本文件...

建立java程序StrToCsv.java

import java.io.*;

import java.io.FileOutputStream;

import java.io.FileWriter;

public class StrToCsv

{

public static void  StrToCsv(String dir,String filename,String Message) throws IOException

{

File letter= new File(dir,filename);

letter.createNewFile();

String ss=dir + File.separatorChar + filename;

FileOutputStream out = new FileOutputStream(ss,true);

out.write(Message.getBytes());

out.flush();

out.close();

}

public static void main(String[] args)

{

try

{

StrToCsv("G:/www","t1.txt","daxiao&quot

;

}catch(IOException e)

{

e.printStackTrace(System.err);

System.exit(-1);

}

System.exit(0);

}

}

编译类

javac  StrToCsv.java

生成StrToCsv.class文件

向数据库加载类

在dos的界面下运行

loadjava -user scott/tiger@guanjian StrToCsv.class

建立存储过程映射java类

CREATE OR REPLACE PROCEDURE strtocsv (

s1 VARCHAR2,

s2 VARCHAR2,

s3 VARCHAR2)

AS LANGUAGE JAVA

NAME 'StrToCsv1.StrToCsv1(java.lang.String,java.lang.String,java.lang.String)';

如果java程序在某个包(如 mypackage1)

则

CREATE OR REPLACE PROCEDURE strtocsv (

s1 VARCHAR2,

s2 VARCHAR2,

s3 VARCHAR2)

AS LANGUAGE JAVA

NAME 'mypackage1.StrToCsv1.StrToCsv1(java.lang.String,java.lang.String,java.lang.String)';

建立一个文件让scott用户有读写能力的文件

在sys用户下赋予scott读写能力

sql>call dbms_java.grant_permission('SCOTT','java.io.FilePermission','G:/www/t1.txt','read,write');

注意在unix 与 window下路径的写法

修改原有的存储过程，采用java对文件操作方法，程序如下

create or replace procedure dump_table_to_csv(

p_tname in varchar2,  --表名称

p_dir   in varchar2,  --目录名称

p_filename in varchar2 ,  --输出文件名称

P_EXPROWS   in OUT INTEGER )

authid current_user

is

--l_output        utl_file.file_type;

l_theCursor     integer default dbms_sql.open_cursor;

l_theCursor1     integer default dbms_sql.open_cursor;

l_columnValue   varchar2(4000);

l_status        integer;

l_query         varchar2(1000)

default 'select * from ' || p_tname;

l_colCnt        number := 0;                     --字段个数

l_separator     varchar2(1);                   --分隔符

l_descTbl       dbms_sql.desc_tab;            --表名称

l_descTbl1       dbms_sql.desc_tab;           --表描述集合

clob_flag  integer :=0;

str1 varchar2(2000) :=' ';

clob_field_value  clob :=to_clob('');

clob_field_s_value  varchar2(20000);

j integer :=0;

m  integer;

begin

--l_output := utl_file.fopen( p_dir, p_filename, 'w',30000 ); --打开一个目标文件

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

dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

for i in 1 .. l_colCnt loop

--如果有clob字段，则在clob字段前后加上标示符

if l_descTbl(i).col_type=112 then

str1:=str1||chr(39)||''||chr(39)||'||'|| l_descTbl(i).col_name ||'||'||chr(39)||''||chr(39)||'  xxx'||i||',';

clob_flag:=1;

end if;

--如果有clob字段，则不用在clob字段前后加上标示符

if l_descTbl(i).col_type!=112 or l_descTbl(i).col_type=113 then

str1:=str1|| l_descTbl(i).col_name ||',';

end if;

end loop;

str1:=substr(str1,1,length(str1)-1);

str1:='select '||str1||'  from '||p_tname;

--printout(100,str1);

dbms_sql.close_cursor(l_theCursor);

l_query:=str1;

dbms_sql.parse(  l_theCursor1,  l_query, dbms_sql.native );

dbms_sql.describe_columns( l_theCursor1, l_colCnt, l_descTbl1);

for i in 1 .. l_colCnt loop

--for i in 1 .. l_colCnt loop

if l_descTbl(i).col_type=112 then

dbms_sql.define_column( l_theCursor1, i, clob_field_value );

l_separator := ',';

end if;

if l_descTbl(i).col_type !=112 then

dbms_sql.define_column( l_theCursor1, i, l_columnValue, 40000 );

l_separator := ',';

end if;

end loop;

l_status := dbms_sql.execute(l_theCursor1);

P_EXPROWS:=0;

while ( dbms_sql.fetch_rows(l_theCursor1) > 0 ) loop

P_EXPROWS:=P_EXPROWS+1;

--如果有clob字段，用‘|’作为行数据分隔符加在行数据的最前面

if clob_flag=1 then

--utl_file.put( l_output, '|' );

strtocsv(p_dir,p_filename,'|');

end if;

l_separator := ' ';

for i in 1 .. l_colCnt loop

if l_descTbl(i).col_type=112 then

dbms_sql.column_value( l_theCursor1, i, clob_field_value );

--utl_file.put( l_output,l_separator);

strtocsv(p_dir,p_filename,l_separator);

j:=floor(length(clob_field_value)/10000)+1;

for m in 1..j loop

clob_field_s_value := to_char(substr(clob_field_value,(m-1)*10000+1,10000));

--utl_file.put( l_output, clob_field_s_value );

strtocsv(p_dir,p_filename,clob_field_s_value);

end loop;

l_separator := ',';

end if;

if l_descTbl(i).col_type != 112 then

dbms_sql.column_value( l_theCursor1, i, l_columnValue );

--utl_file.put( l_output, l_separator || l_columnValue );

strtocsv(p_dir,p_filename,l_separator || l_columnValue);

l_separator := ',';

end if;

end loop;

--utl_file.new_line( l_output );

end loop;

dbms_sql.close_cursor(l_theCursor1);

--utl_file.fclose( l_output );

end  dump_table_to_csv;

测试：

在scott用户下建立表

CREATE TABLE CLOB_TEST

(

PRODUCT_ID     NUMBER(6),

AD_ID          NUMBER(6),

AD_SOURCETEXT  CLOB,

AD_FINALTEXT   CLOB

)

用toda将clob文件加到表中，(直接粘贴进去即可)

在scott运行测试程序

-- Created on 2004-8-19 by IBM

declare

-- Local variables here

i integer;

begin

dump_table_to_csv('scott.clob_test','G:/www','t1.txt',i);

dbms_output.put_line(i);

end;

用sql*loader装载数据

成功！！！！