How can i extract data from flat file to oracle tablw with plsql?
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 /
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博客 ” ，链接：https://blog.itpub.net/104152/viewspace-155637/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
