Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE and invalid directory path (DB:Oracle 9i, 9.2.0.1.0, RHEL 2.1 AS)
UTL_FILE and invalid directory path [message #399634] Thu, 23 April 2009 04:09 Go to next message
jimit_shaili
Messages: 231
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
Re: UTL_FILE and invalid directory path [message #399637 is a reply to message #399634] Thu, 23 April 2009 04:22 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
3rd argument in your call should be 'CSV_FILES' not '/home/smbshare'
Previous Topic: ORA-01873: the leading precision of the interval is too small
Next Topic: Facing a problem while using DBMS_JOB
Goto Forum:
  


Current Time: Tue Dec 06 04:31:23 CST 2016

Total time taken to generate the page: 0.09265 seconds