Home » SQL & PL/SQL » SQL & PL/SQL » utl_file.put_line
utl_file.put_line [message #246575] Thu, 21 June 2007 06:21 Go to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Good Morning.

I am trying to write the output to a flat file. My procedure is successfully completed without the IF condition (v_rec.HR = '0' and v_rec.HR = '1').

I got: 'ORA-20001: utl_file.invalid_filehandle' When I add the IF condition to

IF (v_rec.HR = '0') THEN
    utl_file.put_line(file_handleQ,outline1||'3'||outline2);
ELSIF (v_rec.HR = '1') THEN
    utl_file.put_line(file_handleB,outline1||'3'||outline2);
END IF;


Please show me the way that I can have the output file based on these conditions. I would appreciate that.

Thanks much.
Re: utl_file.put_line [message #246587 is a reply to message #246575] Thu, 21 June 2007 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Like you did.
Error come from elsewhere:
Quote:
'ORA-20001: utl_file.invalid_filehandle'

Did you set the handles?

Regards
Michel
Re: utl_file.put_line [message #246598 is a reply to message #246575] Thu, 21 June 2007 07:42 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Thanks for your reply.

I did set the handle:
file_handleQ        utl_file.file_type;
file_handleB        utl_file.file_type;


My procedure ran successfully now, but the output files are not what I expect. Both HR = ‘1’ and HR = ‘0’ went to both files. They became duplicated files.

Do I need to add IF, ELSIF conditions for the utl_file.fopen ?

Thanks much.
Re: utl_file.put_line [message #246601 is a reply to message #246598] Thu, 21 June 2007 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You surely have to open the file.
Where depends on what you do in the rest of your program.

Regards
Michel
Re: utl_file.put_line [message #246602 is a reply to message #246598] Thu, 21 June 2007 07:54 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:

file_handleQ utl_file.file_type;
file_handleB utl_file.file_type;


Is not setting the file handle, it is simply creating a variable of the appropriate type to allow you to store the file handle info.

You now require

file_handleQ := utl_file.fopen(dir, filename, mode);

To actually populate the filehandle variable.

Ooops, sorry Michel, stepping on your toes there.

[Updated on: Thu, 21 June 2007 07:55]

Report message to a moderator

Re: utl_file.put_line [message #246609 is a reply to message #246575] Thu, 21 June 2007 08:05 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Michel and Pablolee.

Thank you very much for your replies. I appreciate that. I'm very new in Oracle and also in Toad. I have to outputs that I expect with your help.

Thanks again.
Re: utl_file.put_line [message #246610 is a reply to message #246602] Thu, 21 June 2007 08:07 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't worry, it happens to me every time.

Regards
Michel
Previous Topic: Null comparison
Next Topic: Using a procedure in a select clause
Goto Forum:
  


Current Time: Tue Dec 06 10:30:02 CST 2016

Total time taken to generate the page: 0.14241 seconds