UTL_FILE and invalid directory path [message #399634] |
Thu, 23 April 2009 04:09  |
jimit_shaili
Messages: 237 Registered: June 2006 Location: India, Ahmedabad
|
Senior Member |
|
|
Dear Freinds
I want to create a .csv file on my server hard disk through my application.for that i have created a function using UTL_FILE [copied from asktom site].but function not working when i tried.here is sample code.
SQL> CREATE OR REPLACE DIRECTORY CSV_FILES AS '/home/smbshare';
Directory created.
SQL> GRANT READ, WRITE ON DIRECTORY CSV_FILES TO PUBLIC;
Grant succeeded.
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> select dump_csv('select * from all_users where rownum < 25',',', '/home/smbshare', 'temp.txt' ) from dual;
select dump_csv('select * from all_users where rownum < 25',',', '/home/smbshare', 'temp.txt' ) from dual
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 424
ORA-06512: at "RMS.DUMP_CSV", line 17
ORA-06512: at line 1
it is giving me the above error,please suggest me what am i doing wrong here.
Regards
Jimit
|
|
|
|