Home » SQL & PL/SQL » SQL & PL/SQL » problem using utl_file package (oracle 8i, windows xp)
problem using utl_file package [message #310412] Tue, 01 April 2008 03:03 Go to next message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
hello ppl ,
this is my first post to the forum

I got a problem while using the utl_file package ,
i need to output the details of a to a text i used the following code
i created a directory path
using

create or replace directory testdir as 'D:\temp';

    create or replace PROCEDURE testfile_io IS
    out_file   utl_file.File_Type;
 l_location     VARCHAR2(100) := 'D:\temp';
    
    filename  VARCHAR2(30) :='xyz.txt';
  BEGIN
   in_file := utl_file.Fopen(l_location,filename,'w');
    LOOP
     
     utl_file.Put_Line(out_file,'testing 123 the file...');
    END LOOP;
    EXCEPTION
      WHEN no_data_found THEN
     
        utl_file.Fclose(out_file);
   END;



the procedure compiles successfully

but when i call the procedure

i get the following error


SQL> exec testfile_io ;
BEGIN testfile_io ; END;

*
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 "SYSTEM.TESTFILE_IO", line 7
ORA-06512: at line 1



could , someone please help me out

Re: problem using utl_file package [message #310414 is a reply to message #310412] Tue, 01 April 2008 03:12 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

What is the output of this query ?

SELECT VALUE FROM v$parameter WHERE name = 'utl_file_dir'


You can only access these directories.
Re: problem using utl_file package [message #310415 is a reply to message #310412] Tue, 01 April 2008 03:23 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
the procedure compiles successfully
Not that code that you posted above. It will compile with errors.
Re: problem using utl_file package [message #310545 is a reply to message #310412] Tue, 01 April 2008 10:13 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Since you are using 8i, you need to make sure that the directory path is in your utl_file_dir parameter in your init.ora file and bounce your database after changing the parameter for it to take effect. You also have an endless loop. Since 8i is no longer supported, you should upgrade. 11g is the most current version. As of 9i, you can create a directory object to point to the path and there is no need to set the path in the init.ora file or bounce the database.

Previous Topic: Sending table in mail from oracle (pl/sql)
Next Topic: positive or negative add a new column
Goto Forum:
  


Current Time: Sun Dec 04 21:05:20 CST 2016

Total time taken to generate the page: 0.05739 seconds