Re: pl/sql write file

On Sat, 13 Jan 2001 19:28:07 GMT, Al Taylor <pwc_al_at_yahoo.com> wrote:
>If you are running on UNIX, make sure you have the correct permission in the
>path. I have also found that you have to explicitly state the utl_file_dir
>path. Don't be insulted by this question, but did you restart the Oracle Db
>after your mod's to the init$SID.ora file???
>
>Help yourself out by implementing the utl_file defined exceptions, this will
>help you debug your problem.
>
>Al Taylor (Not a Oracle Guru but I have used the UTL_FILE Package recently)
>

Hello Al,

To your first remark: yes I am running under linux and the permission can't be the problem here because after your posting I logged in as "su" and made myself "system" when entering oracle. To your second remark: I did add the line utl_file_dir = * to my init.ora file. Believing that '*' means wildcard here so everywhere is allowed. Or is this not the case ? Should I explicitly enter the path name here like "utl_file_dir = /tmp" ? Insulted ?? Those are the one's with the long toes and thin skins :) After making the adition to the init.ora file I indeed restarted the database.

So to your advise I added the exception part to the script. Still no clear answer. Only the "unhandled user-defined exception" comes to light. Is it possible that this part is not installed or is at a regular item like dbms_output ?

Rob

declare

        v_out_line varchar2(2000);
  fileID utl_file.file_type;

begin

	fileID := utl_file.fopen ('/tmp','tst.csv','W');
	v_out_line := 'This'
								||','||'is'
								||','||'a'
								||','||'piece'
								||','||'of'
								||','||'text.';
	utl_file.put_line(fileID,v_out_line);
	utl_file.fclose(fileID);
	
	exception
		when utl_file.invalid_path then
			dbms_output.put_line('inavalid path');
			raise;
		when utl_file.invalid_mode then
			dbms_output.put_line('inavalid mode');
			raise;
		when utl_file.invalid_filehandle then
			dbms_output.put_line('invalid filehandle');
			raise;
		when utl_file.invalid_operation then
			dbms_output.put_line('invalid operation');
			raise;
		when utl_file.read_error then
			dbms_output.put_line('read error');
			raise;
		when utl_file.write_error then
			dbms_output.put_line('write error');
			raise;
		when utl_file.internal_error then
			dbms_output.put_line('internal error');
			raise;