ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How can i extract data from flat file to oracle tablw with plsql?

How can i extract data from flat file to oracle tablw with plsql?

原创 Linux操作系统 作者:cc59 时间:2008-01-21 23:24:29 0 删除 编辑
Pl/sql cao do it
 
 
 
SQL> create or replace function  dump_csv( p_query     in varchar2,
  2                                        p_separator in varchar2
  3                                                      default ',',
  4                                        p_dir       in varchar2 ,
  5                                        p_filename  in varchar2 )
  6  return number
  7  AUTHID CURRENT_USER
  8  is
  9      l_output        utl_file.file_type;
 10      l_theCursor     integer default dbms_sql.open_cursor;
 11      l_columnValue   varchar2(2000);
 12      l_status        integer;
 13      l_colCnt        number default 0;
 14      l_separator     varchar2(10) default '';
 15      l_cnt           number default 0;
 16  begin
 17      l_output := utl_file.fopen( p_dir, p_filename, 'w' );
 18 
 19      dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
 20 
 21      for i in 1 .. 255 loop
 22          begin
 23              dbms_sql.define_column( l_theCursor, i,
 24                                      l_columnValue, 2000 );
 25              l_colCnt := i;
 26          exception
 27              when others then
 28                  if ( sqlcode = -1007 ) then exit;
 29                  else
 30                      raise;
 31                  end if;
 32          end;
 33      end loop;
 34 
 35      dbms_sql.define_column( l_theCursor, 1, l_columnValue,
 36                              2000 );
 37 
 38      l_status := dbms_sql.execute(l_theCursor);
 39 
 40      loop
 41          exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
 42          l_separator := '';
 43          for i in 1 .. l_colCnt loop
 44              dbms_sql.column_value( l_theCursor, i,
 45                                     l_columnValue );
 46              utl_file.put( l_output, l_separator ||
 47                                      l_columnValue );
 48              l_separator := p_separator;
 49          end loop;
 50          utl_file.new_line( l_output );
 51          l_cnt := l_cnt+1;
 52      end loop;
 53      dbms_sql.close_cursor(l_theCursor);
 54 
 55      utl_file.fclose( l_output );
 56      return l_cnt;
 57  end dump_csv;
 58  /
 
Function created.
 
SQL> create or replace directory tmp as '/tmp';
 
Directory created.
 

SQL> declare
  2           l_rows  number;
  3        begin
  4           l_rows := dump_csv( 'select *
  5                                            from all_users
  6                                          where rownum < 5',
  7                                        ' , ' , 'TMP', 'test.dat' );
  8        end;
  9  /
 
PL/SQL procedure successfully completed.
 

[oracle@rac2 tmp]$ pwd
/tmp
[oracle@rac2 tmp]$ cat test.dat
SYS , 0 , 03-AUG-07
SYSTEM , 5 , 03-AUG-07
OUTLN , 9 , 03-AUG-07
DIP , 13 , 03-AUG-07
 
 
 
 
 
 
 

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

请登录后发表评论 登录
全部评论

注册时间:2007-12-21

  • 博文量
    132
  • 访问量
    286645