ITPub博客

首页 > IT职业 > IT生活 > export custom table data into any file on your operation system

export custom table data into any file on your operation system

原创 IT生活 作者:raddisonLEE 时间:2006-10-22 01:11:11 0 删除 编辑

/* check input datatpye*/
CREATE OR REPLACE FUNCTION ifnum(P_NUM IN VARCHAR2) RETURN VARCHAR2 AS
V_TMP NUMBER;
BEGIN
IF P_NUM IS NULL THEN
RETURN NULL;
END IF;
V_TMP := TO_NUMBER(P_NUM);
RETURN 'T';
EXCEPTION
WHEN OTHERS THEN
RETURN 'N';
END;
/**************************************************/

/* setting your utl_file_dir /

alter system set utl_file_dir='/your DIR' scope=spfile;

create or replace function output_data( p_query in varchar2,
p_separator in varchar2
default ',',
p_dir in varchar2 ,
p_filename in varchar2 ,
tname in varchar2,
cmit in integer default 3 )
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(400) default '';
l_cnt number default 0;
t_desc dbms_sql.desc_tab;
l_rowcnt integer;

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 := 'insert into '||tname|| ' values (' ;
l_rowcnt :=DBMS_SQL.LAST_ROW_COUNT ;
-- dbms_output.put_line( l_rowcnt);
l_rowcnt :=l_rowcnt -1;
if l_rowcnt<>0 and mod(l_rowcnt,cmit)=0 then
utl_file.PUT (l_output , 'commit ;' );
utl_file.new_line( l_output );
end if ;

dbms_sql.describe_columns( l_theCursor , l_colCnt, t_desc);


for i in 1 .. l_colCnt loop

-- dbms_output.put_line(t_desc(i).col_type);
dbms_sql.column_value( l_theCursor, i,
l_columnValue );

-- l_columnValue :=nvl(l_columnValue,null) ;
---------- dbms_output.put_line(ascii(l_columnValue));
-- if ascii(l_columnValue) <32
-- then
-- l_columnValue :='null';
-- end if ;

if
ifnum(l_columnValue) ='T'
then
null;
else
l_columnValue :=''''||l_columnValue||'''' ;
-- dbms_output.put_line(''''||l_columnValue||'''' );
end if ;

if t_desc(i).col_type=12 then
l_columnValue :='to_date('|| l_columnValue ||', dd-mm-yyyy)';

end if;
utl_file.put( l_output, l_separator ||
nvl (l_columnValue,null) );
l_separator := p_separator;
end loop;
utl_file.put( l_output, ');' );
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 output_data;

/*usage : for e.g */

declare
l_rows number;
begin
l_rows := dump_csv( ' select * from allen where rownum<5 ',
',', 'c:/', 'test.dat','tablename' );

-- dbms_output.put_line( l_rows);
end;

/* your likely read the results follow :*/

insert into tablename values (600043,207,2200,0,0,0,0,1,0,' ',14,4176,0,0,0,0,0,1500,'01-1?-49','','01-1?-49','28-9?-06','ARBOR ',1,130,0,0,1,0,0,120,'',0);
insert into tablename values (600044,208,2200,0,0,0,0,1,0,' ',14,4176,0,0,0,0,0,1500,'01-1?-49','','01-1?-49','28-9?-06','ARBOR ',1,130,0,0,1,0,0,120,'',0);
insert into tablename values (600045,209,2200,0,0,0,0,1,0,' ',14,4176,0,0,0,0,0,1500,'01-1?-49','','01-1?-49','28-9?-06','ARBOR ',1,130,0,0,1,0,0,120,'',0);
insert into tablename values (600046,210,2200,0,0,0,0,1,0,' ',14,4176,0,0,0,0,0,1500,'01-1?-49','','01-1?-49','28-9?-06','ARBOR ',1,130,0,0,1,0,0,120,'',0);

[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/66865/viewspace-873034/,如需转载,请注明出处,否则将追究法律责任。

下一篇: init parameter
请登录后发表评论 登录
全部评论

注册时间:2009-02-09

  • 博文量
    11
  • 访问量
    86389