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

上一篇 / 下一篇 2008-01-21 23:24:29 / 个人分类：Concepts&Basic

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

see http://asktom.oracle.com

导入论坛引用链接收藏分享给好友推荐到圈子管理举报

TAG:

查看全部评论