oracle目录名无效,执行在Oracle中生成csv文件的过程时无效的目录路径（在Windows中）...

create or replace procedure dump_table_to_csv( p_tname in varchar2,

2 p_dir in varchar2,

3 p_filename in varchar2 )

4 is

5 l_output utl_file.file_type;

6 l_theCursor integer default dbms_sql.open_cursor;

7 l_columnValue varchar2(4000);

8 l_status integer;

9 l_query varchar2(1000)

10 default 'select * from ' || p_tname;

11 l_colCnt number := 0;

12 l_separator varchar2(1);

13 l_descTbl dbms_sql.desc_tab;

14 begin

15 l_output := utl_file.fopen( p_dir, p_filename, 'w' );

16 execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''

';

17

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

19 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

20

21 for i in 1 .. l_colCnt loop

22 utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"'

);

23 dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );

24 l_separator := ',';

25 end loop;

26 utl_file.new_line( l_output );

27

28 l_status := dbms_sql.execute(l_theCursor);

29

30 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop

31 l_separator := '';

32 for i in 1 .. l_colCnt loop

33 dbms_sql.column_value( l_theCursor, i, l_columnValue );

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

35 l_separator := ',';

36 end loop;

37 utl_file.new_line( l_output );

38 end loop;

39 dbms_sql.close_cursor(l_theCursor);

40 utl_file.fclose( l_output );

41

42 execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';

43 exception

44 when others then

45 execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';

46 raise;

47 end;

48 /