Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE (Oracle)
UTL_FILE [message #396992] Wed, 08 April 2009 10:13 Go to next message
eden60
Messages: 5
Registered: April 2009
Junior Member
I am getting an error utl_file invalid file operation.

I have a function that returns a filename when a 'pv_id' number is passed in. I have assigned the variable below to the function.

v_pfilename := email_event_pkg.templatefiles(pv_id);

utl_file.fopen('EMAIL_TEMPLATES',v_pfilename,'R');

Does utl_file.fopen allow a variable to be passed in?

Thanks
Re: UTL_FILE [message #396994 is a reply to message #396992] Wed, 08 April 2009 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Does utl_file.fopen allow a variable to be passed in?

Yes.
Does the directory exist? Does Oracle has read/write/execute accesses to it? Does your user has privilege on it?

Regards
Michel
Re: UTL_FILE [message #396998 is a reply to message #396994] Wed, 08 April 2009 10:23 Go to previous messageGo to next message
eden60
Messages: 5
Registered: April 2009
Junior Member
Yes, It works when I put the actual file name in quotes in the utl_file.fopen.
Re: UTL_FILE [message #397026 is a reply to message #396998] Wed, 08 April 2009 12:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is v_pfilename of type VARCHAR2 (or similar)?

Regards
Michel
Re: UTL_FILE [message #397033 is a reply to message #397026] Wed, 08 April 2009 12:23 Go to previous messageGo to next message
eden60
Messages: 5
Registered: April 2009
Junior Member
Yes
Re: UTL_FILE [message #397037 is a reply to message #397033] Wed, 08 April 2009 12:30 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then something is wrong somewhere.

Post the entire SQL*Plus session where you create the procedure, run it with the hard coded file name successfully and with the variable file name unsuccessfully.
Re: UTL_FILE [message #397038 is a reply to message #397033] Wed, 08 April 2009 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm sorryy but it works for me:
SQL> set serveroutput on
SQL> declare
  2     file utl_file.file_type;
  3     l_file varchar2(20) := 'toto';
  4  begin
  5     dbms_output.enable;
  6     dbms_output.put('>>> Start');
  7     dbms_output.new_line;
  8     file := utl_file.fopen ('WORK_DIR',l_file,'w');
  9     if ( utl_file.is_open(file) ) then
 10        dbms_output.put_line('>>> File opened');
 11        utl_file.put_line (file, 'titi');
 12        dbms_output.put_line('>>> Line written');
 13        utl_file.fflush (file);
 14        dbms_output.put_line('>>> Write flushed');
 15        utl_file.fclose (file);
 16        dbms_output.put_line('>>> File closed');
 17     else 
 18        dbms_output.put_line('>>> File not opened');
 19     end if;
 20     dbms_output.put_line('>>> End');
 21  exception
 22     when utl_file.invalid_path then
 23        dbms_output.put_line('>>> Invalid path');
 24     when utl_file.invalid_filehandle then
 25        dbms_output.put_line('>>> Invalid file handle');
 26     when utl_file.invalid_mode then
 27        dbms_output.put_line('>>> Invalid mode');
 28     when utl_file.invalid_operation then
 29        dbms_output.put_line('>>> Invalid operation');
 30     when utl_file.write_error then
 31        dbms_output.put_line('>>> Write error');
 32     when utl_file.read_error then
 33        dbms_output.put_line('>>> Read error');
 34     when utl_file.internal_error then
 35        dbms_output.put_line('>>> Internal error');
 36  end;
 37  /
>>> Start
>>> File opened
>>> Line written
>>> Write flushed
>>> File closed
>>> End

PL/SQL procedure successfully completed.

Check this for you and post the result.

Regards
Michel
Re: UTL_FILE [message #397039 is a reply to message #397038] Wed, 08 April 2009 12:47 Go to previous messageGo to next message
eden60
Messages: 5
Registered: April 2009
Junior Member
Thanks,

I will give it a try. But I do see on difference in what I am trying to do. Here it is using your example:

your version: l_file varchar2(20) := 'toto';
my version: l_file varchar2(20) := mail_event_pkg.templatefiles(pv_id);

Re: UTL_FILE [message #397040 is a reply to message #397039] Wed, 08 April 2009 12:49 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
And what does that tell you?
Re: UTL_FILE [message #397041 is a reply to message #397040] Wed, 08 April 2009 12:58 Go to previous messageGo to next message
eden60
Messages: 5
Registered: April 2009
Junior Member
I need to:

declare

l_file_a varchar2(20)
l_file varchar2(20)

--------------

l_file_a := mail_event_pkg.templatefiles(pv_id);

l_file := 'l_file_a';

file := utl_file.fopen ('WORK_DIR',l_file,'R');


??
Re: UTL_FILE [message #397048 is a reply to message #397041] Wed, 08 April 2009 13:22 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.
First test the same thing I does (just changing the directory) and post.
Display your function return, maybe the returned value is invalid for a file name.
In the end, change l_file assignment with your function and reexecute the PL/SQL bloc I posted and show us.

Regards
Michel
Previous Topic: please correct this trigger
Next Topic: Ftping using oracle procedure
Goto Forum:
  


Current Time: Fri Dec 02 18:42:54 CST 2016

Total time taken to generate the page: 0.24889 seconds