UTL_FILE Output

[tokerago13]

Hi Guys,
I need some advice on how to output some text unto UNIX.
What I intend doing in a loop is output data similar to what will be output when you run the query below:
select 'Staff ID'||','||
'First Name'||','||
'Last Name'||','||
'Email address'||','||
'Chrysalis Logon'||','||
'Directorate'||','||
'Division'||','||
'Cost Code'||','||
'Start Date'||','||
'Leaving Date'
from dual;

I am using the UTL file utility thus:

file_handle := utl_file.fopen (c_path, c_filename, 'W',20000);
--
v_input := 'Staff ID'||','||
'First Name'||','||
'Last Name'||','||
'Email address'||','||
'Chrysalis Logon'||','||
'Directorate'||','||
'Division'||','||
'Cost Code'||','||
'Start Date'||','||
'Leaving Date';

utl_file.put_line (file_handle, v_input);
utl_file.new_line (file_handle);

What happens is that I can't get the output to write in UNIX. BUt if I do truncate the characters to say, just the first two columns, it writes.
I would like to know how I can write the whole line successfully?
Any help will be greatly appreciated.
Thanks

T

 

[SantaMufasa]

T,

I don't see your "utl_file.fclose(file_handle);" anywhere. Without that statement, I'm very surprised that you ever get any output. Let us know if you actually executed the close and just didn't list it in your original post.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[tokerago13]

Okay.. The fclose is there. I'll add full section of the code

**********************
file_handle := utl_file.fopen (c_path, c_filename, 'W',v_buffer);
--
help_code := '702';

v_input := 'Staff ID'||','||
'First Name'||','||
'Last Name'||','||
'Email address'||','||
'Chrysalis Logon'||','||
'Directorate'||','||
'Division'||','||
'Cost Code'||','||
'Start Date'||','||
'Leaving Date';

utl_file.put_line (file_handle, v_input);
utl_file.new_line (file_handle);

for rec_details in c_details loop

v_input := rec_details.employee_number||','||
rec_details.first_name||','||
rec_details.last_name||','||
rec_details.email_address||','||
rec_details.emp_user_name||','||
rec_details.business_unit_code||','||
rec_details.division_code||','||
rec_details.cost_code||','||
rec_details.start_date||','||
rec_details.leaving_date;
utl_file.put_line (file_handle, v_input);
utl_file.new_line (file_handle);

end loop;
--
utl_file.fclose (file_handle);

help_code := '703';
**********************

 

[SantaMufasa]

T,

What is the value of "v_buffer" at the time you execute the utl_file.fopen? That argument sets the maximum line size for any one line. The fact that you get output if you "cut down" your number of columns leads me to believe that you have an artificially small value for "v_buffer". Why not just set it to "32767" (maximum value) and let utl_file take care of business. ("utl_file" will not output right-filling spaces to your max value, so you have nothing to worry about when setting the value high.)

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[tokerago13]

Thanks. I have tried your suggestion, but still came up with no results. I'm not sure if this has got to do with some of the setting on the UNIX box itself or the some UTL settings.
T

 

[SantaMufasa]

As an experiment/test, T, can you try this out on a PC-based Oracle database?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Beilstwh]

Rewrite is as the following. Like Dave said, hard code the length parameter in fopen to a large value.

file_handle := utl_file.fopen (c_path, c_filename, 'W',[COLOR=red]2000[/color]);
  --        
            help_code := '702';
            
            v_input := 'Staff ID'||','||
                   'First Name'||','||
                   'Last Name'||','||                   
                   'Email address'||','||
                   'Chrysalis Logon'||','||
                   'Directorate'||','||
                   'Division'||','||
                   'Cost Code'||','||
                   'Start Date'||','||
                   'Leaving Date';
        
        utl_file.put_line (file_handle, v_input);
        utl_file.new_line (file_handle);
            
      for rec_details in c_details loop                
        
        v_input := rec_details.employee_number||','||
                   rec_details.first_name||','||
                   rec_details.last_name||','||
                   rec_details.email_address||','||
                   rec_details.emp_user_name||','||
                   rec_details.business_unit_code||','||
                   rec_details.division_code||','||
                   rec_details.cost_code||','||
                   rec_details.start_date||','||
                   rec_details.leaving_date;
        utl_file.put_line (file_handle, v_input);
        utl_file.new_line (file_handle);              
       
       end loop;
        --
        utl_file.fflush(file_handle);
        utl_file.fclose (file_handle);

Bill
Oracle DBA/Developer
New York State, USA

 

[tokerago13]

Hi All,
I've solved the issue. It had to do with the variable size for v_input. It was 88. I've now changed this to 1000.
Thanks a lot everyone
T