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
Messages: 43
Registered: April 2008
Location: India
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

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';
   in_file := utl_file.Fopen(l_location,filename,'w');
     utl_file.Put_Line(out_file,'testing 123 the file...');
      WHEN no_data_found THEN

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
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
Messages: 2819
Registered: May 2007
Location: Scotland
Senior Member
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: 8614
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: Fri Oct 28 06:04:02 CDT 2016

Total time taken to generate the page: 0.10698 seconds