如何从Oracle动态导出带列头的CSV文件至网络存储位置

Got it, let's get that column header added to your exported CSV file. The core issue is that your current procedure jumps straight to writing data rows without first outputting the header line that matches your CSV's formatting. Here's how to adjust your code:

Key Changes Needed #

• Add a header line before looping through the cursor results, formatted to match your data rows (quoted, pipe-separated)
• Initialize/reset the tmp variable to avoid leading empty segments or concatenated rows (a small bug in the original code)

Modified Procedure Code #

create or replace PROCEDURE REPORT_FILE ( p_status OUT NUMBER, p_message OUT VARCHAR2) IS
file_loc CONSTANT VARCHAR2(64) := 'New_DIR';
fid_out UTL_FILE.FILE_TYPE;
file_out VARCHAR2(58);
file_out_ext VARCHAR2(4):='.DAT';
tmp VARCHAR2(999);
CURSOR c_results is Select First_name,Last_name,Phone_number from customer ;
BEGIN
DBMS_OUTPUT.enable (1000000);
DBMS_OUTPUT.put_line ( 'START TIME IS ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
insert into customer (select * from customer_data);
----- output filename
file_out := 'New_data' || TO_CHAR(SYSDATE,cs_datefmt) || TO_CHAR(SYSDATE,cs_timefmt) ||'.CSV' ;
user_utility.print_line('current quater filename is : ' || file_out );
fid_out:=UTL_FILE.FOPEN(file_loc,file_out,'W');

-- Write column header first, matching data formatting
tmp := '"First Name"|"Last Name"|"Phone Number"|';
UTL_FILE.PUT_LINE(fid_out,tmp);

-- Initialize tmp for data rows to avoid leftover values
tmp := '';

FOR cur_rec in c_results LOOP
tmp := '"' || cur_rec.first_name|| '"|';
tmp := tmp || '"' || cur_rec.Last_name || '"|';
tmp := tmp || '"' || cur_rec.Phone_number|| '"|';
UTL_FILE.PUT_LINE(fid_out,tmp);
-- Reset tmp for the next row to prevent concatenation
tmp := '';
END LOOP;

user_utility.print_line ('REPORT completed successfully .. ');
COMMIT;
p_status := 0;
user_utility.update_job (loc_prog_name, loc_job_id,'C', sysname);
EXCEPTION
WHEN OTHERS THEN
loc_errcode := SQLCODE;
loc_errmess := SQLERRM;
user_utility.update_job (loc_prog_name, loc_job_id, 'I', sysname);
user_utility.error_handler (loc_job_id, loc_prog_name, loc_prog_step, loc_operation, loc_table_name, loc_errcode, loc_errmess, loc_note);
p_status := loc_errcode;
p_message := 'REPORT has failed. '|| loc_errmess;
rollback;
DBMS_OUTPUT.put_line ('Error code:' || loc_errcode);
DBMS_OUTPUT.put_line ('Error message:' || loc_errmess);
DBMS_OUTPUT.put_line ( 'END TIME IS ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
END;

What We Changed #

1. Header Line: We added a line that constructs the header string with human-readable column names wrapped in double quotes and separated by pipes—matching exactly how your data is formatted. This gets written to the file right after opening it, before processing any data rows.
2. tmp Management: The original code didn't reset tmp between rows, which would cause each row to append to the previous one. We initialize tmp to an empty string before the loop, and reset it at the end of each iteration to ensure clean, separate rows.

This will give you a CSV file that starts with clear column headers, followed by all your customer data in a consistent, usable format.

内容的提问来源于stack exchange，提问作者ARH