Home » SQL & PL/SQL » SQL & PL/SQL » Utl_File error
Utl_File error [message #230333] Wed, 11 April 2007 09:57 Go to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
Hi,

I have created a procedure below , but when i execute i am getting the error:

create or replace procedure utl_file_check (
p_fil_dir in varchar2,
p_fil_name in varchar2) is
v_file_handle utl_file.file_type;
cursor c1 is
select emp_id,last_name,salary from employees;
begin
v_file_handle := utl_file.fopen(p_fil_dir,p_fil_name,'w');
utl_file.put(v_file_handle,'description of emp table');
utl_file.new_line(v_file_handle);
for i in c1 loop
utl_file.putf(v_file_handle,'emp_id:%s\n',i.emp_id);
utl_file.putf(v_file_handle,'last_name:%s\n',i.last_name);
utl_file.putf(v_file_handle,'salary:%s\n',i.salary);
end loop;

utl_file.fclose(v_file_handle);

exception
when utl_file.invalid_path then
raise_application_error(-20001,'invalid file');
end utl_file_check;

When I run the following , i am getting an error:

execute utl_file_check('c:\temp','emp_det.txt');

ERROR at line 1:
ORA-20001: invalid file
ORA-06512: at "SCOTT.UTL_FILE_CHECK", line 21
ORA-06512: at line 1

I am running in scott, why i am getting th error.

Thanks,
Srinivas
Re: Utl_File error [message #230334 is a reply to message #230333] Wed, 11 April 2007 10:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because you receive an utl_file.invalid_path exception as your code implement it.
does "c:\temp\emp_det.txt" exist?
One question: what is the purpose of the exception block?
An advice: remove it then you will which utl_file function returned the error.

Regards
Michel
Re: Utl_File error [message #230337 is a reply to message #230334] Wed, 11 April 2007 10:18 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
Hi,

Yes the path c:\temp and file emp_test.txt exists.

when i removed the exception i am getting the following error

SQL> execute utl_file_check('c:\temp','emp_test.txt');
BEGIN utl_file_check('c:\temp','emp_test.txt'); END;

*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at "SCOTT.UTL_FILE_CHECK", line 8
ORA-06512: at line 1

Thanks,
Srinivas
Re: Utl_File error [message #230339 is a reply to message #230333] Wed, 11 April 2007 10:35 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
I could be wrong & don't have the time or inclination to test.
IIRC, it might work (better) without "C:" in the directory field.

THT & YMMV
Re: Utl_File error [message #230349 is a reply to message #230337] Wed, 11 April 2007 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the value of your UTL_FILE_DIR parameter?
Does it contain 'c:\temp'?
Please post "show parameter utl_file_dir".

Regards
Michel

[Updated on: Wed, 11 April 2007 11:22]

Report message to a moderator

Re: Utl_File error [message #230365 is a reply to message #230333] Wed, 11 April 2007 12:25 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
Hi,

When i gave show parameter i got the below,
has this anything to do??

SQL> show parameter utl_file_dir

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /tmp

Thanks,
Srinivas
Re: Utl_File error [message #230366 is a reply to message #230333] Wed, 11 April 2007 12:27 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
Hi,

when i ran as below, it ran successfully.


SQL> execute utl_file_check('/tmp','emp_test.txt');

PL/SQL procedure successfully completed.

but where will i get the file emp_test.txt


Thanks,
Srinivas
Re: Utl_File error [message #230370 is a reply to message #230365] Wed, 11 April 2007 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2 things:
1/ remember utl_file can only read files on the server side.
2/ If this is correct, replace '/tmp' with 'c:\temp' in utl_file_dir and restart the database
3/ If this is not correct, ftp the file to the server in /tmp directory.

Regards
Michel
Re: Utl_File error [message #230372 is a reply to message #230366] Wed, 11 April 2007 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
where will i get the file emp_test.txt


This is your file, you'll get it where you'll put it.

Regards
Michel
Re: Utl_File error [message #230373 is a reply to message #230333] Wed, 11 April 2007 12:50 Go to previous message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
ok thanks for your reply.
Previous Topic: Help with Package concepts
Next Topic: SQL Query
Goto Forum:
  


Current Time: Sun Dec 04 16:50:30 CST 2016

Total time taken to generate the page: 0.09416 seconds